Oracle: select insert

Does anyone knows what this "select 'insert..." clause means? It looks like that it generates a lot of insert statement. Are there some introduction material telling about that?

==================create_portlets.sql============================
set pagesize 999;
set linesize 320;
set feedback off;
set verify off;
set heading off;
SET TERM ON;
SET ECHO ON;

SPOOL tmp_create_test_data.sql;

-- Login (user, pwd)
select 'INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH (PORTLET_DEFINITION_ID, PLACEHOLDER_DEFINITION_ID, ACTUAL_SIZE) VALUES ('
 || pf_portlet_definition.portlet_definition_id || ', '
 || pf_placeholder_definition.placeholder_definition_id || ', '
 || '1' || ');' from pf_placeholder_definition, pf_markup_definition, pf_portlet_definition  
 where pf_placeholder_definition.markup_definition_id = pf_markup_definition.markup_definition_id
 and pf_portlet_definition.portlet_label = 'portlet_userpwdlogin' and pf_portlet_definition.webapp_name = 'portal'
 and pf_markup_definition.markup_type = 'Placeholder';

-- Registration
select 'INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH (PORTLET_DEFINITION_ID, PLACEHOLDER_DEFINITION_ID, ACTUAL_SIZE) VALUES ('
 || pf_portlet_definition.portlet_definition_id || ', '
 || pf_placeholder_definition.placeholder_definition_id || ', '
 || '1' || ');' from pf_placeholder_definition, pf_markup_definition, pf_portlet_definition  
 where pf_placeholder_definition.markup_definition_id = pf_markup_definition.markup_definition_id
 and pf_portlet_definition.portlet_label = 'portlet_registration' and pf_portlet_definition.webapp_name = 'portal'
 and pf_markup_definition.markup_type = 'Placeholder';

SPOOL OFF;

set pagesize 55;
set feedback on;
set heading on;

@tmp_create_test_data;

commit;

=============================

====================
SQL> -- Login (user, pwd)
SQL> select 'INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH (PORTLET_DEFINITION_ID, PLACEHOLDER_DEFINITION_ID, ACTUAL_SIZE) VALUES ('
  2   || pf_portlet_definition.portlet_definition_id || ', '
  3   || pf_placeholder_definition.placeholder_definition_id || ', '
  4   || '1' || ');' from pf_placeholder_definition, pf_markup_definition, pf_portlet_definition
  5   where pf_placeholder_definition.markup_definition_id = pf_markup_definition.markup_definition_id
  6   and pf_portlet_definition.portlet_label = 'portlet_userpwdlogin' and pf_portlet_definition.webapp_name = 'portal'
  7   and pf_markup_definition.markup_type = 'Placeholder';

INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH (PORTLET_DEFINITION_ID, PLACEHOLDER_DEFINITION_ID, ACTUAL_SIZE) VALUES (77009, 2001, 1);                                                                                                                                                                                              
INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH (PORTLET_DEFINITION_ID, PLACEHOLDER_DEFINITION_ID, ACTUAL_SIZE) VALUES (77009, 2002, 1);                                                                                                                                                                                              
INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH (PORTLET_DEFINITION_ID, PLACEHOLDER_DEFINITION_ID, ACTUAL_SIZE) VALUES (77009, 2003, 1);                                                                                                                                                                                              
...
JianJunShenAsked:
Who is Participating?
 
sventhanConnect With a Mentor Commented:
Its generating insert statment dynamically to insert the data into the table "NPP_PLACEHOLDER_PORTLET_MATCH" from the following tables
pf_placeholder_definition, pf_markup_definition, pf_portlet_definition.
0
 
konektorConnect With a Mentor Commented:
spool typicaly creates log of actions you perform in sqlplus
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12043.htm#sthref3036

you can use select statement only to select values to as text format, but if you surround them with "constant text" - eg. insert statement, updata ... you can create script with many inserts, updates, ... anything ...  from existing data in database
0
 
sventhanConnect With a Mentor Commented:
continue ..

these insert statements will be spooled into this file "tmp_create_test_data" and inserted into the table "NPP_PLACEHOLDER_PORTLET_MATCH" when the script get executed (@tmp_create_test_data)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
JianJunShenAuthor Commented:
what does this select mean here? "select 'INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH... "
Are there some documents mentioning about that?
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
what does this select mean here? "select 'INSERT...?

That is just a way to use the power of SQL to create the insert statements quickly, dynamically and accurately, so you don't have to find all of the data, then type them all manually.

Take a look at the "from..." part of that statement.  That indicates where the "select" statement is looking, and it determines how many "INSERT..." statements will be created.
0
 
rbrookerConnect With a Mentor Commented:
try this :

select 'this is some static text' my_comment from dual;
select 'counting ... ' || level my_count
from dual
connect by level < 11;

the first query returns some static text, the second one returns some static text and some data generated by the select statement.

this is what the select is doing, it is mixing static text "INSERT INTO..." and mixing it with values from the tables to create insert statements, my guess is to transfer some data somewhere...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.