The query shown below creates the insert -  
INSERT INTO WIP_ENTITIES(.....) 
base query (code template) that can be 
used in SQL or PL/SQL development, ETL
analysis or in Oracle ERP WIP 
data/workflow analysis or for code 
verification/validation.

The "Insert Into table(....)" base query
is created using the Oracle analytic 
function LEAD().
All_Tab_Columns Insert Query Base

SELECT
 q_insert_sql.string_sql  sql_insert_into 
FROM
 (SELECT 1 column_id,
    'INSERT INTO '||'&i_tab'||'(' string_sql 
  FROM DUAL
UNION
  SELECT
   q_atc.column_id,
   q_atc.table_column||
   DECODE(q_atc.lead_val,0,')',',') string_sql
  FROM
   (SELECT  column_id,
     LOWER(column_name) table_column,
     LEAD(column_id,1,0) OVER
      (PARTITION BY table_name 
           ORDER BY column_id) lead_val
    FROM  all_tab_columns
    WHERE table_name = UPPER('&i_tab')
    ORDER BY column_id
   ) q_atc
 ORDER BY 1
 ) q_insert_sql;
   All_Tab_Columns Insert Query template code

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 01, 2012

  7612