[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3136
  • Last Modified:

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);                                                                                                                                                                                              
...
0
JianJunShen
Asked:
JianJunShen
5 Solutions
 
sventhanCommented:
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
 
konektorCommented:
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
 
sventhanCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JianJunShenAuthor Commented:
what does this select mean here? "select 'INSERT INTO NPP_PLACEHOLDER_PORTLET_MATCH... "
Are there some documents mentioning about that?
0
 
Mark GeerlingsDatabase 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
 
rbrookerCommented:
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now