Thanks Tom, But I am not planning to move data using that script.
Like I said, TOAD Creates this script [ofcourse it's not dynamically generated]. I have to select a table to get insert statements. So I was wondering is there a way to create scripts like that If I pass in a TABLE NAME as parameter. This script has to pick up all the column names and create an insert statement..
Just to see whether it can be done using pl/sql or not?

Thanks, for any ideas [Did not mean for you to create whole script for me]. Just some ideas as to how can I fetch data into variables dynamically..
sample pseudo code would work :-)

Thanks,

Hi Maverick,

You may query ALL_TAB_COLUMNS dictionary view in order to get the column name of a table given its name.

In pl/sql you may then write a cursor to fetch all the column names and build the insert statement in each iteration...

hope this helps..

problem comes when you fetch those values into variables. Eg: I am trying to do this for a table that has 5 columns in it. I get all those from all_tab_columns and can buid

Insert into table (col1,col2,col3,col4,col5)
  values(

Now putting those values in the string, is where the complexity is ..
I need to select those columns from table , execute them dynamically and bulk collect into variables. If it was a known table during run time, we could do that. But if I were to build something dynamically [generic] for any table..that's not possible [let's say I'm not able to]
..Any ideas?

Sorry, but I'm not understanding your problem,

We agreed that you can build a statement like:

INSERT INTO any_table ( col1, col2, col3, col4, col5) VALUES (:1, :2, :3, :4, :5); 

In which col1..5 are the column names of any_table retrieved dinamically from the ALL_TAB_COLUMNS table.

Now you need five variables for the USING clase. ¿Is there any problem picking these values into five variables? If you want to insert values regardless the table, use dynamic sql again.

Are we talking the same scenario? Can you provide a use-case of this script?

Thanks,

I think you missed a small point in this scenario. I will not be having only 5 columns in all tables. It could vary. a table can have 2 columns or three columns or n columns.
So, I need to vary the fetch into variables dynamically. Hope I'm not confusing you more.

Steps would be like this:

1)Select columns from all_tab_columns for a given table.
eg: Need to create Insert statements for EMP ->
select column_name bulk collect into empColumnarray
from user_tab_columns
where table_name='EMP';

2)using this column list, select the data from that table:
eg: select [empColumnarray]
Bulk collect into empRecordType
from EMP;

3) now build Inserts:
Insert into EMP(empColumnArray) values (empRecordType.empno,empRecrodType.Ename...);

problem is in last statment I cannot say empno,ename,etc. It has to be dynamic. it changes for DEPT table,CUSTOMER Table etc..

Hope this helps.
Thanks,

Following sample code can be used to generate insert statement. I have used very limited data-types in the solution (number, date and varchar2 only). Also note that dbms_output is restricted to 255 characters. So, if the length of 'insert into ' exceeds 255, the query will fail. There are number of workarounds which can be implemented to avoid this error.

set serveroutput on size 100000
set feedback off

declare
  v_table_name      varchar2(30) := 'EMP';  -- Your Tablename
  v_column_list     varchar2(2000);
  v_insert_list     varchar2(2000);
  v_ref_cur_columns varchar2(4000);
  v_ref_cur_query   varchar2(2000);
  v_ref_cur_output  varchar2(2000);
  v_column_name     varchar2(2000);
  cursor c1 is select column_name, data_type from user_tab_columns where table_name = v_table_name order by column_id;
  refcur            sys_refcursor; 
begin
  for i in c1 loop
     v_column_list := v_column_list||','||i.column_name;
     if i.data_type = 'NUMBER' then
        v_column_name := i.column_name;
     elsif i.data_type = 'DATE' then
        v_column_name := chr(39)||'to_date('||chr(39)||'||chr(39)'||'||to_char('||i.column_name||','||chr(39)||'dd/mm/yyyy hh:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||chr(39)||'dd/mm/rrrr hh:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
     elsif i.data_type = 'VARCHAR2' then
        v_column_name := 'chr(39)||'||i.column_name||'||chr(39)';
     end if;
     v_ref_cur_columns := v_ref_cur_columns||'||'||chr(39)||','||chr(39)||'||'||v_column_name;
  end loop; 
  v_column_list     := ltrim(v_column_list,',');
  v_ref_cur_columns := substr(v_ref_cur_columns,8);

  v_insert_list     := 'INSERT INTO '||v_table_name||' ('||v_column_list||') VALUES ';
  v_ref_cur_query   := 'SELECT '||v_ref_cur_columns||' FROM '||v_table_name;
  
  open refcur for v_ref_cur_query;
  loop
  fetch refcur into v_ref_cur_output; 
  exit when refcur%notfound;
    v_ref_cur_output := '('||v_ref_cur_output||');'; 
    v_ref_cur_output := replace(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := replace(v_ref_cur_output,'(,','(null,');
    v_ref_cur_output := replace(v_ref_cur_output,',,)',',null)');
    v_ref_cur_output := replace(v_ref_cur_output,'null,)','null,null)');
    v_ref_cur_output := v_insert_list||v_ref_cur_output; 
    dbms_output.put_line (v_ref_cur_output); 
  end loop; 
end;
/

HTH

Thanks "Reader",
you are basically concatenating all the columns and outputting them into a single VARCHAR2 variable, and breaking them by a delimiter. I didn't think of that at all. Thanks a lot.
I could use this and proceed further.

you can create insert statment,through spooling.


set echo off
set feedback off
set term off
set pagesize 0
set linesize 8000
set heading off
SPOOL C:\upgrade\scripts\data\3.0\event_schema\data\db_settings.sql
prompt set define off
prompt set echo off
prompt set feedback off
prompt set term off
prompt set pagesize 0
prompt set linesize 8000
prompt set heading off
prompt delete from db_settings
prompt /
select 'insert into db_settings values('''||setting_name||''','''||data_type||''','''||replace(value,'''','''''')||''','''|| replace(description,'''','''''')||''''||')'||';' from db_settings;
prompt commit;
prompt /
prompt set define on
prompt set echo on
prompt set feedback on
prompt set term on
set heading on
SPOOL OFF
set echo on
set feedback on
set term on
set heading on

Hi, we have a requirement that install scripts create a spool file of all the activities. I've recently being working on a script to be called from the main install script to create insert statements from data within a table before it is dropped. This is mainly incase a tester re-runs a script without backing up their data. As I'm already spooling to a log file and am only on Oracle 9i the script spools its generated sql statmenet to the unix box to an area which is accessible via a url. I then run the file by referencing the url + filename. This is a first draft of the script. It does not fully work if the number or xmltype columns are null but an addition of a decode around these should do the trick. I pass in 2 parameters when calling the script, first the table name and second a name for the temp file on the unix box.


SET VERIFY OFF

DECLARE


CURSOR column_names_cur
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER('&&1')
ORDER BY column_id ;


CURSOR col_select_cur
IS
SELECT DECODE(data_type ,
'DATE' , ''''||'TO_DATE('||''''||'||'||''''||''''||''''||''''||'||'||'TO_CHAR(' ,
'NUMBER', NULL,
'VARCHAR2', ''''''''''||'||' ,
'XMLTYPE', ''''||'xmltype('||''''||'||'||''''||''''||''''||''''||'||')

||column_name
||DECODE (data_type ,
'DATE' , ','||''''||'dd-mon-yyyy hh24:mi:ss'||''''||')'||'||'||''''||''''||''''||''''||'||'||''''||','||''''||'||'||''''||''''||''''||''''||'||'||''''||'dd-mon-yyyy hh24:mi:ss'||''''||'||'||''''||''''||''''||''''||'||'||''''||')'||'''' ,
'NUMBER', NULL ,
'VARCHAR2' , '||'||''''||''''||''''||'''' ,
'XMLTYPE', '||'||''''||''''||''''||''''||'||'||''''||')'||'''' ) sel
FROM user_tab_columns
WHERE table_name = UPPER('&&1')
ORDER BY column_id ;


w_sql_start VARCHAR2(4000) ;
w_sql VARCHAR2(4000) ;
w_ok BOOLEAN ;
w_file_handle utl_file.file_type ;
w_err_text VARCHAR2(500) ;
w_utl_dir system_default_parameters.sdp_value%TYPE ;
w_col_sep VARCHAR2(400) := '||'',''||' ;


BEGIN

-- obtain the start of the sql statement
FOR column_names_rec IN column_names_cur
LOOP

w_sql_start := w_sql_start ||column_names_rec.column_name || ',' ;

END LOOP ;


w_sql_start := 'SELECT ''INSERT INTO &&1 ('|| RTRIM (w_sql_start,',') ||') VALUES (' ||''''||'||';


-- obtain individual columns

FOR col_select_rec IN col_select_cur
LOOP

w_sql := w_sql || col_select_rec.sel ||w_col_sep ;

END LOOP ;

IF w_sql IS NOT NULL
THEN
w_sql := SUBSTR(w_sql, 1, LENGTH(w_sql)-7) ;
w_sql := w_sql_start || w_sql ||'||'||''''||');'||''''||' AS "insert.sql" FROM &&1 ;' ;
ELSE
w_sql := 'SELECT ''Table &&1 not found.'' AS "insert.sql" FROM DUAL; ' ;
END IF ;


-- write the select statement which will genearte the insert statement to a file on the unix box

w_file_handle := utl_file.fopen ('/app/webrep/webreports', -- directory ,
'&&2' , -- filename ,
'W') ;


utl_file.putf(w_file_handle ,
'%s' ,
w_sql) ;



utl_file.fclose(w_file_handle) ;

END ;
/


COLUMN url_and_file new_value url_and_file

SELECT ' http://my_url_to_file_location' ||'&&2' "url_and_file"
FROM DUAL
/

-- Exec the file from unix by calling it via its web address

@'&url_and_file'



-- Tidy up

SET VERIFY ON

UNDEFINE 1
UNDEFINE 2
UNDEFINE url_and_file

I also faced the same situation i.e i has to generate "Insert statements dynamically".So wrote a query for that The query is :

Select 'insert into t values('||col1||','||col2||','||col3||','||col4||')' from xyz.

This would generate the insert statements.

Code by HTH is useful, but need some improvements, e.g. it does not handle single quote in the text field, and serveroutput for huge table.

You may find situations where you need to create insert statement dynamically. Here is the code you can use. I have modified code by HTH, and it works:

----------------------------------------------
-- Script to generate insert statement dynamically
-- Written by HTH
-- Improved by Zahirul Haque
-- Aug. 29, 2012
----------------------------------------------
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SET PAGESIZE 0
SET FEEDBACK OFF
SET DOC OFF
SET TAB OFF
SET LINESIZE 4096 
SET TRIM ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT OFF

SPOOL insert_me.sql


DECLARE
TYPE num_t IS TABLE OF number index by binary_integer;
     l_count_t num_t;
TYPE tab_t IS TABLE OF VARCHAR2(35);
tableList tab_t := tab_t(
    'EMP'
   ,'DEPT'
   ,'REGION'
   ,'COUNTRY'
   -- add more tables here
);

table_nam   VARCHAR2(34);
l_count     NUMBER;

PROCEDURE gen_insert (p_tab_name VARCHAR2, p_exist BOOLEAN default false)
IS
  v_column_list     VARCHAR2(4096) := null;
  v_insert_list     VARCHAR2(16096);
  v_ref_cur_columns VARCHAR2(16096) := null;
  v_ref_cur_query   VARCHAR2(16000);
  v_ref_cur_output  VARCHAR2(16000) := null;
  v_column_name     VARCHAR2(256);
  CURSOR c1 IS
       SELECT column_name, data_type FROM user_tab_columns
       WHERE table_name = p_tab_name ORDER BY column_id;
  refcur            sys_refcursor; 
BEGIN
  DBMS_OUTPUT.ENABLE(NULL); -- this will hangle dbms_output for huge table.
  FOR i IN c1 LOOP
     v_column_list := v_column_list||','||i.column_name;
     IF i.data_type = 'NUMBER' THEN
        v_column_name := i.column_name;
     ELSIF i.data_type = 'DATE' THEN
        v_column_name := chr(39)||'to_date('||chr(39)||'||chr(39)'||'||to_char('||i.column_name||','||
        chr(39)||'dd/mm/yyyy hh24:mi:ss'||chr(39)||')||chr(39)||'||chr(39)||', '||chr(39)||'||chr(39)||'||
        chr(39)||'dd/mm/rrrr hh24:mi:ss'||chr(39)||'||chr(39)||'||chr(39)||')'||chr(39);
     ELSIF i.data_type = 'VARCHAR2' then
        -- Following line will hangle single quote in text data.
        v_column_name := 'chr(39)||replace('|| i.column_name ||','''''''','''''''''''')||chr(39)';
        --dbms_output.put_line(v_column_name);
     ELSIF i.data_type = 'CHAR' then
        v_column_name := 'chr(39)||'|| i.column_name ||'||chr(39)';
     END IF;
     v_ref_cur_columns := v_ref_cur_columns||'||'||chr(39)||','||chr(39)||'||'||v_column_name;
  END LOOP;
     --dbms_output.put_line('A='||v_ref_cur_columns);

IF v_column_list is null then
  dbms_output.put_line('--Table '|| p_tab_name || ' does not exist');
ELSE
  --dbms_output.put_line(p_tab_name || ' does exist');
  v_column_list     := LTRIM(v_column_list,',');
  v_ref_cur_columns := SUBSTR(v_ref_cur_columns,8);

  v_insert_list     := 'INSERT INTO '||p_tab_name||' ('||v_column_list||') VALUES ';
  v_ref_cur_query   := 'SELECT '||v_ref_cur_columns||' FROM '||p_tab_name;
  --dbms_output.put_line(v_ref_cur_columns);
  --dbms_output.put_line(v_ref_cur_query);
 
  OPEN refcur FOR v_ref_cur_query;
  LOOP
  FETCH refcur INTO v_ref_cur_output; 
  EXIT WHEN refcur%NOTFOUND;
    v_ref_cur_output := '('||v_ref_cur_output||');'; 
    v_ref_cur_output := REPLACE(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := REPLACE(v_ref_cur_output,',,',',null,');
    v_ref_cur_output := REPLACE(v_ref_cur_output,'(,','(null,');
    v_ref_cur_output := REPLACE(v_ref_cur_output,',,)',',null)');
    v_ref_cur_output := REPLACE(v_ref_cur_output,'null,)','null,null)');

    v_ref_cur_output := v_insert_list||v_ref_cur_output; 
    DBMS_OUTPUT.PUT_LINE (v_ref_cur_output); 
  END LOOP; 
IF v_ref_cur_output is null then
  dbms_output.put_line('--No data in '||p_tab_name);
END IF;
END IF;

  Exception
    When others then
         dbms_output.put_line('Error='||sqlerrm);

END gen_insert;
-----------------------------------------------------------------------
BEGIN
   DBMS_OUTPUT.ENABLE(NULL);
   DBMS_OUTPUT.PUT_LINE('SET FEEDBACK OFF');
   DBMS_OUTPUT.PUT_LINE('SET HEADING OFF');
   DBMS_OUTPUT.PUT_LINE('SPOOL 6_static_data.log');
   DBMS_OUTPUT.PUT_LINE('');

   FOR i IN tableList.FIRST..tableList.LAST LOOP
       DBMS_OUTPUT.PUT_LINE('------------------------------------------------- ' );
       DBMS_OUTPUT.PUT_LINE('PROMPT '||to_char(i)||'. Deleting/Inserting into '|| tableList(i)||';' );
       DBMS_OUTPUT.PUT_LINE('DELETE FROM '|| tableList(i)||';' );
       gen_insert(tableList(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Commit;');
    DBMS_OUTPUT.PUT_LINE('SPOOL OFF');

END;
/
SPOOL OFF
PROMPT "Finito...";

Hi,

This works very nicely. Except for a case, i have to add the below line to correct an issue.

v_ref_cur_output := REPLACE(v_ref_cur_output,',);',',null);');

Thank you very much.

Thiruppathi

Hi,

I have a requirement where insert is to be done from a dynamic query.

Solution is like


execute immediate ' insert into Amit(APP_ID)
select 1 from '||TABLE_NAME||'';

the above query is working fine.but its doing commit.
But i dnt want a commit.as amit table is temp table,as its doing commit the temp table data is available in other session as well.

Can we have solution without committing.
I tried this way but its not workimg


insert into Amit(APP_ID)
execute immediate 'select 1 from '||TABLE_NAME||'';

Errors: check compiler log
103/8 PL/SQL: ORA-00926: missing VALUES keyword
102/11 PL/SQL: SQL Statement ignored

ops$tkyte%ORA11GR2> create table t (x int);

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          execute immediate 'insert into t(x) select 1 from ' || 'dual';
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from t;

no rows selected

----------------------------------------------
-- Script to generate insert statement dynamically
-- Written by HTH
-- Improved by Zahirul Haque
-- Aug. 29, 2012
----------------------------------------------
This script can be modified to use the insert statement only once for a table and use Select Union all. It will reduce the size of the file.

For example the out put looks like

Insert into tbl_name
Select c1,c2,c3,c4 union all
Select c1,c2,c3,c4 union all
Select c1,c2,c3,c4 union all
Select c1,c2,c3,c4;

instead of
Insert into tbl_name
Select c1,c2,c3,c4;
Insert into tbl_name
Select c1,c2,c3,c4;
Insert into tbl_name
Select c1,c2,c3,c4;
Insert into tbl_name
Select c1,c2,c3,c4;

Step 1:-
Get the output of the below query in a variable:-

select DISTINCT
LISTAGG(DECODE( DATA_TYPE , 'DATE','''TO_DATE(''''''||'||DECODE(DATA_TYPE,'DATE','TO_CHAR('||COLUMN_NAME||',''YYYYMMDD HH24:MI:SS'')',''||COLUMN_NAME||'')||'||'''''', ''''YYYYMMDD HH24:MI:SS'''')''','''''''''||'||COLUMN_NAME||'||'''''''''),'||'',''||') WITHIN GROUP (ORDER BY COLUMN_ID ASC) OVER (PARTITION BY TABLE_NAME)
from all_tab_columns
where table_name = :table
and owner = :owner;

Step 2:-

Replace the value of <variable> with the output of the above sql and run.

SELECT
(
select DISTINCT
'INSERT INTO '||OWNER||'.'||TABLE_NAME||' ('||
LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_ID ASC) OVER (PARTITION BY TABLE_NAME)
|| ')'||CHR(10)
from all_tab_columns
where table_name = :table
and owner = :owner
)
||'VALUES ('
|| <variable>
|| ');'
FROM :table;

You can even avoid PL-SQL and can do it using a simple SQL... Well - in two steps.

I have created a sample table (script below) to demonstrate.

CREATE TABLE TEST_TBL1
(
COL1 NUMBER,
COL2 VARCHAR2(10),
COL3 DATE
);

INSERT INTO CONNECTOR.TEST_TBL1 VALUES (1, 'A', TO_DATE('05/21/2013 16:33:08', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (2, 'B', TO_DATE('05/21/2013 16:33:08', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (3, 'C', TO_DATE('05/21/2013 16:33:09', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (4, 'D', TO_DATE('05/21/2013 16:33:09', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (NULL, 'E', TO_DATE('05/21/2013 16:33:25', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (6, NULL, TO_DATE('05/21/2013 16:33:35', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO CONNECTOR.TEST_TBL1 VALUES (7, 'G', NULL);
COMMIT;

Now the query is:
----------------
SELECT 'SELECT '''||INS.INSERT_STMT||'('||RECS.SELECT_ROWS||')'' STMT FROM '||RECS.OWNER||'.'||RECS.TABLE_NAME RUN_ME_TO_GET_INSERT_SCRIPT
FROM
(
SELECT ATC.OWNER, ATC.TABLE_NAME, ''||LISTAGG(
CASE
WHEN ATC.DATA_TYPE = 'VARCHAR2' THEN '''||DECODE('||ATC.COLUMN_NAME||', NULL, ''NULL'', ''''''''||'||ATC.COLUMN_NAME||'||'''''''')||'''
WHEN ATC.DATA_TYPE = 'NUMBER' THEN '''||DECODE('||ATC.COLUMN_NAME||', NULL, ''NULL'', '||ATC.COLUMN_NAME||')||'''
WHEN ATC.DATA_TYPE = 'DATE' THEN 'TO_DATE(''''''||TO_CHAR('||ATC.COLUMN_NAME||', ''DD-MON-YYYY HH24:MI:SS'')||'''''', ''''DD-MON-YYYY HH24:MI:SS'''')'
END,
', ') WITHIN GROUP (ORDER BY ATC.COLUMN_ID) SELECT_ROWS
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.TABLE_NAME IN ('TEST_TBL1')
GROUP BY ATC.OWNER, ATC.TABLE_NAME
) RECS,
(
SELECT ATC.OWNER, ATC.TABLE_NAME, 'INSERT INTO '||ATC.OWNER||'.'||ATC.TABLE_NAME||' ('||LISTAGG(ATC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ATC.COLUMN_ID)||') VALUES ' INSERT_STMT
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.TABLE_NAME IN ('TEST_TBL1')
GROUP BY ATC.OWNER, ATC.TABLE_NAME
) INS
WHERE RECS.OWNER = INS.OWNER
AND RECS.TABLE_NAME = INS.TABLE_NAME


This will give you following result:

RUN_ME_TO_GET_INSERT_SCRIPT
---------------------------
SELECT 'INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES ('||DECODE(COL1, NULL, 'NULL', COL1)||', '||DECODE(COL2, NULL, 'NULL', ''''||COL2||'''')||', TO_DATE('''||TO_CHAR(COL3, 'DD-MON-YYYY HH24:MI:SS')||''', ''DD-MON-YYYY HH24:MI:SS''))' STMT FROM CONNECTOR.TEST_TBL1

Just run it to see the magic..

STMT
----
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (1, 'A', TO_DATE('21-MAY-2013 16:33:08', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (2, 'B', TO_DATE('21-MAY-2013 16:33:08', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (3, 'C', TO_DATE('21-MAY-2013 16:33:09', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (4, 'D', TO_DATE('21-MAY-2013 16:33:09', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (NULL, 'E', TO_DATE('21-MAY-2013 16:33:25', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (6, NULL, TO_DATE('21-MAY-2013 16:33:35', 'DD-MON-YYYY HH24:MI:SS'))
INSERT INTO CONNECTOR.TEST_TBL1 (COL1, COL2, COL3) VALUES (7, 'G', TO_DATE('', 'DD-MON-YYYY HH24:MI:SS'))


Here is your insert statements.. And yes. It uses LISTAGG - So doesn't work in earlier versions (may be before 10g?)

Hope that helps...

Hi Tom,

We are trying to solve a unique situation.
We get some third party data which we load into temp tables. We are supposed to consolidate all these tables into one final table.

Following are the rules.

1) final table should have all the columns from all these tables. If there are common column(s) then add only one column with that name.
2) the join would be based on all the common columns
3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)

In this example, i know the tables, columns etc ,which makes it easy for me to frame the SQL.
In real life, the only information i get is the table names. I have to derive the rest of the information from USER_TAB_COLUMNS.

Question:
How do i frame the following query on the fly based on metadata only?

Example:

DROP TABLE TABLE1;
DROP TABLE TABLE2;
DROP TABLE FINAL_TABLE;

CREATE TABLE TABLE1(HOST_ID VARCHAR(100),DOMAIN VARCHAR(100));
INSERT INTO TABLE1 VALUES(2,'ACME.COM');

CREATE TABLE TABLE2(HOST_ID VARCHAR(100),DOMAIN VARCHAR(100),HOSTNAME VARCHAR(100));
INSERT INTO TABLE2 VALUES(2,'ACME.COM','WS159');
INSERT INTO TABLE2 VALUES(2,'BDNA.COM','FL001');

create table FINAL_TABLE as
select 
   case when table2.host_id is not null then table2.host_id else table1.host_id end as host_id
 , case when table2.domain is not null then table2.domain else table1.domain end as domain
 , case when table2.hostname is not null then table2.hostname else null end as hostname
from table2 
full outer join table1 on table2.host_id=table1.host_id
                       and table2.domain=table1.domain;
                       
select * from FINAL_TABLE;

ops$tkyte%ORA11GR2> CREATE TABLE T1(HOST_ID VARCHAR(10),DOMAIN VARCHAR(10), interesting_column number);

Table created.

ops$tkyte%ORA11GR2> INSERT INTO T1 VALUES(2,'ACME.COM',100);

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO T1 VALUES(2,'ORACLE.COM',42);

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE T2(HOST_ID VARCHAR(10),DOMAIN VARCHAR(10),HOSTNAME VARCHAR(10));

Table created.

ops$tkyte%ORA11GR2> INSERT INTO T2 VALUES(2,'ACME.COM','WS159');

1 row created.

ops$tkyte%ORA11GR2> INSERT INTO T2 VALUES(2,'BDNA.COM','FL001');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2      l_sql varchar2(32765) := 'create table ft as select ';
  3      l_on  varchar2(32765);
  4  
  5      cursor c is
  6      select
  7         case when ut1.column_name is not null and ut2.column_name is not null
  8              then 'coalesce( "T1"."' || ut1.column_name || '", "T2"."' || ut2.column_name || '" ) as "' || ut1.column_name || '"'
  9              when ut1.column_name is not null
 10              then '"T1"."' || ut1.column_name || '" as "' || ut1.column_name || '"'
 11              when ut2.column_name is not null
 12              then '"T2"."' || ut2.column_name || '" as "' || ut2.column_name || '"'
 13          end select_list,
 14         case when ut1.column_name is not null and ut2.column_name is not null
 15              then ut1.column_name
 16          end on_list
 17  
 18        from (select * from user_tab_columns where table_name = 'T1') ut1 full outer join
 19             (select * from user_tab_columns where table_name = 'T2') ut2
 20             on (ut1.column_name = ut2.column_name );
 21  begin
 22      for x in C
 23      loop
 24          l_sql := l_sql || x.select_list || ',';
 25          if (x.on_list is not null)
 26          then
 27              l_on := l_on || '"T1"."' || x.on_list || '" = "T2"."' || x.on_list || '" and ';
 28          end if;
 29      end loop;
 30      l_sql := rtrim( l_sql, ',' ) || ' from "T1" full outer join "T2" on (' ||
 31               substr( l_on, 1, length(l_on)-4 ) || ')';
 32  
 33      dbms_output.put_line( l_sql );
 34      execute immediate l_sql;
 35  end;
 36  /
create table ft as select coalesce( "T1"."HOST_ID", "T2"."HOST_ID" )
as "HOST_ID",coalesce( "T1"."DOMAIN", "T2"."DOMAIN" ) as
"DOMAIN","T2"."HOSTNAME" as "HOSTNAME","T1"."INTERESTING_COLUMN" as
"INTERESTING_COLUMN" from "T1" full outer join "T2" on ("T1"."HOST_ID"
= "T2"."HOST_ID" and "T1"."DOMAIN" = "T2"."DOMAIN" )

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> desc ft
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 HOST_ID                                      VARCHAR2(10)
 DOMAIN                                       VARCHAR2(10)
 HOSTNAME                                     VARCHAR2(10)
 INTERESTING_COLUMN                           NUMBER

ops$tkyte%ORA11GR2> select * from ft;

HOST_ID    DOMAIN     HOSTNAME   INTERESTING_COLUMN
---------- ---------- ---------- ------------------
2          ACME.COM   WS159                     100
2          BDNA.COM   FL001
2          ORACLE.COM                            42

Simply brilliant!!

But what if the number of tables are dynamic too? Meaning, in this example we know there are 2 tables involved. In case of 3 or more tables, forming OUTER JOIN would be tricky isn't it?

If it is okay to assume,

1. Joins should be made on columns which are available on all tables
2. If there is a column which is present in more than one table - but not in all tables, it would be just selected as tab1_colname, tab2_colname etc..

Here you go,

Script to setup test data (added another table)
-------------------------
CREATE TABLE T1(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), INTERESTING_COLUMN NUMBER);
CREATE TABLE T2(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE));
CREATE TABLE T3(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE), ANOTHER_COLUMN NUMBER);

INSERT INTO T1 (HOST_ID, DOMAIN, INTERESTING_COLUMN) VALUES ('2', 'ACME.COM', 100);
INSERT INTO T1 (HOST_ID, DOMAIN, INTERESTING_COLUMN) VALUES ('2', 'ORACLE.COM', 42);

INSERT INTO T2 (HOST_ID, DOMAIN, HOSTNAME) VALUES ('2', 'ACME.COM', 'WS159');
INSERT INTO T2 (HOST_ID, DOMAIN, HOSTNAME) VALUES ('2', 'BDNA.COM', 'FL001');

INSERT INTO T3 (HOST_ID, DOMAIN, HOSTNAME, ANOTHER_COLUMN) VALUES ('2', 'ORACLE.COM', 'ABCD', 43);
INSERT INTO T3 (HOST_ID, DOMAIN, HOSTNAME, ANOTHER_COLUMN) VALUES ('2', 'ACME.COM', 'WS159', 10);
INSERT INTO T3 (HOST_ID, DOMAIN, HOSTNAME, ANOTHER_COLUMN) VALUES ('2', 'GOOGLE.COM', 'FL001', 11);
COMMIT;


Now just run below query, it will generate the "CREATE TABLE AS SELECT" script for you.


SELECT 'CREATE TABLE T4 AS SELECT '||MAX(SELECT_COLS)||' FROM '||LISTAGG(JOIN_CND, ' ') WITHIN GROUP (ORDER BY TABLE_NAME) NEW_COND
FROM
(
SELECT TABLE_NAME, DECODE(MAX(RHS), NULL, TABLE_NAME, 'FULL OUTER JOIN '||TABLE_NAME||' ON ('||LISTAGG(DECODE(RHS, NULL, NULL, LHS||RHS), ' AND ') WITHIN GROUP (ORDER BY COLUMN_NAME)||')') JOIN_CND,
MAX(SELECT_COLS) SELECT_COLS
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, KEY_COL, LHS, RHS, RWNM,
LISTAGG(DECODE(RWNM, 1, COL_LIST, NULL), ', ') WITHIN GROUP (ORDER BY COL_LIST) OVER (PARTITION BY 1) SELECT_COLS
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, KEY_COL,
MAX(DECODE(KEY_COL, 'K', DECODE(RWNM, 1, TABLE_NAME||'.'||COLUMN_NAME||'=', NULL),NULL)) OVER (PARTITION BY COLUMN_NAME) LHS,
DECODE(KEY_COL, 'K', DECODE(RWNM, 1, NULL, TABLE_NAME||'.'||COLUMN_NAME),NULL) RHS, RWNM,
DECODE(KEY_COL, 'K', 'COALESCE(', NULL) ||
LISTAGG(DECODE(KEY_COL, 'M', TABLE_NAME||'.'||COLUMN_NAME||' '||TABLE_NAME||'_'||COLUMN_NAME, TABLE_NAME||'.'||COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY COLUMN_NAME) OVER (PARTITION BY COLUMN_NAME) ||
DECODE(KEY_COL, 'K', ') '||COLUMN_NAME, NULL) COL_LIST
FROM
(
SELECT TABLE_NAME, COLUMN_NAME, DECODE(COUNT(1) OVER (PARTITION BY COLUMN_NAME), 3, 'K', 1, NULL, 'M') KEY_COL,
ROW_NUMBER() OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME) RWNM, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME IN ('T1', 'T2', 'T3')
)
)
)
GROUP BY TABLE_NAME
)

Output:

NEW_COND
--------
CREATE TABLE T4 AS SELECT COALESCE(T1.DOMAIN, T2.DOMAIN, T3.DOMAIN) DOMAIN, COALESCE(T1.HOST_ID, T2.HOST_ID, T3.HOST_ID) HOST_ID, T1.INTERESTING_COLUMN, T2.HOSTNAME T2_HOSTNAME, T3.HOSTNAME T3_HOSTNAME, T3.ANOTHER_COLUMN FROM T1 FULL OUTER JOIN T2 ON (T1.DOMAIN=T2.DOMAIN AND T1.HOST_ID=T2.HOST_ID) FULL OUTER JOIN T3 ON (T1.DOMAIN=T3.DOMAIN AND T1.HOST_ID=T3.HOST_ID)


You need to provide two inputs.
1. List of tables (as 'T1', 'T2', 'T3')
2. Number of tables (you can see "3" in the inner most select)

It uses 5 levels of sub-selects. But yet no joins between ALL_TAB_COLUMNS (no need to join ALL_TAB_COLUMNS n times for n tables). So should be fast.

Hope it helps :)

Gireesh,

Thank you very much for the SQL. Very elegant and educative! and I am still analyzing how it works for various scenarios.

But to answer Tom's question "However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?"

The final output from your query (T4) should be something like:

SELECT domain, host_id, INTERESTING_COLUMN, 
       t2_hostname AS HOSTNAME,another_column 
  FROM T4 
 WHERE t2_hostname IS NOT NULL
UNION
SELECT domain, host_id, INTERESTING_COLUMN,
       t3_hostname AS HOSTNAME, 
       another_column 
  FROM T4 
 WHERE t3_hostname IS NOT NULL;

Is it possible?

"However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?"

-In my answer, single row will be selected with two columns T2_D & T3_D (please look at the first two assumptions)

If you want to use them too in the joins, two different rows would be selected (one with D=1 & second with D=2) instead of single row.

Agree 100% to Tom. Union makes no sense at all.

We are still in the process of developing the system. We are still getting the actual data from our customer as we are doing the development.
The UNION query is just to depict how the final data should look like for the result of the query you provided.

Following is the most realistic data.

1) T1/T2/T3 has the sample data which cover most of our test cases
2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.
3) we might have more than 3 tables in our production environment, so the query should work for N tables.
4) I have given the explanation of how each row should be derived to be inserted in T4

DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;

CREATE TABLE T1(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), INTERESTING_COLUMN NUMBER);
CREATE TABLE T2(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE));
CREATE TABLE T3(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), HOSTNAME VARCHAR2(100 BYTE), ANOTHER_COLUMN NUMBER);

INSERT INTO T1 VALUES(1,'ACME.COM',100);
INSERT INTO T1 VALUES(2,'ORACLE.COM',42);

INSERT INTO T2 VALUES(1,'ACME.COM','WS159');
INSERT INTO T2 VALUES(3,'BDNA.COM','FL001');

INSERT INTO T3 VALUES(2,'ORACLE.COM','ABCD',43);
INSERT INTO T3 VALUES(1,'ACME.COM','WS158',10);
INSERT INTO T3 VALUES(3,'BDNA.COM','FL001',11);
INSERT INTO T3 VALUES(4,'GOOGLE.COM','G123',NULL);



CREATE TABLE T4(HOST_ID VARCHAR2(100 BYTE), DOMAIN VARCHAR2(100 BYTE), INTERESTING_COLUMN NUMBER,HOSTNAME VARCHAR2(100 BYTE),ANOTHER_COLUMN NUMBER);

INSERT INTO T4 VALUES(1,'ACME.COM',100,'WS159',NULL);    -- row1
INSERT INTO T4 VALUES(1,'ACME.COM',100,'WS158',10);      -- row2
INSERT INTO T4 VALUES(2,'ORACLE.COM',42,'ABCD',43);      -- row3
INSERT INTO T4 VALUES(3,'BDNA.COM',NULL,'FL001',11);     -- row4
INSERT INTO T4 VALUES(4,'GOOGLE.COM',NULL,'G1231',NULL); -- row5

Explanation for each row:

row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match)
row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match)
row3) This row comes from T1 and T3
row4) This row comes from T2 and T3
row5) This row comes from T3

The answer to the follOWing question you asked earlier, is the same explanation of how rows 1 and 2 above get populated in T4;

Excerpt from your question:

t1( a,b,c )
t2( a,b,D )
t3( a,b,D )

final table has a,b,c,d from #1.

I know the join is only on A,B since those are the common columns.

However - what about D, what if t2 has D=1 and t3 has D=2 for the same a,b values?

Thanks!

Hi Tom, Gireesh

Any suggestions for me? I am kind of stuck at this point. Please let know what else could be the option? I tried few things by modifying the queries but it is not working out.

Thanks!

Tom,

After much deliberation with our business i could get the following from them. This is exact description i got.

"we take the number of columns that are common across all tables at the same time (looking at all tables at the same time) and we use that as the join criteria. Whenever there is a perfect match we merge rows if the other columns had null value. We do not merge whenever there was value on the same non-common-across column"

If my -Guess- about the requirement is right, that is what exactly the query I gave above does. It uses all common-across-all-tables columns in join and merges the rows which shares common values. Also it does not merge on the not-common-across-tables columns.

Try that and share what different you are looking for.

Hi Tom,

Yes. It is different from what the requirement was before. It is much more simpler now. Earlier, we were complicating the rules too much.

perfect match is "if all the values in common columns match/same".

Hi Gireesh,

Your solution is much closer, but instead of creating two different columns, it should be created as new row if there are different values for the column. That part is the tricky part.

One more headache i have is to convert to SQLSERVER which unfortunately doesn't has equivalent to LISTAGG. It would be a herculean task. My deliverable is by friday :(

Hi,
I recently used this solution to dinamicaly generate INSERT statements from different tables and it perfectly worked until I had to apply it on a table where I had a numeric column with decimals.
Let's say we have the following:
TAB_TEST(NUMBER,VARCHAR,NUMBER(10,2))
When I execute the select it returns me an entry like this:
1,'Test1',10,3
The problem is that decimal separator is considered value separator so when I run the INSERT statement I get ORA-00913: too many values. One solution whould be to alter the session and set nls_numeric_characters='.,';

Do you have other suggestions?

Thanks a lot for the two different solutions.
I would like to use select /*insert*/* from table in plsql procedure dinamically for each table of schema.

I need an export of all data tables of the schema without sysdba privileges

select 'select /*insert*/* from ' || owner || '.' || table_name
from   all_tables
where  owner = 'CHRIS'

Hi Zahirul Haque/Team,

This insert statements is working fine for datatypes (Number/varchar/DATE/CHAR etc ) but missing for TIMESTAMP datatype.
Could you please include i.datatype= 'TIMESTAMP' with formats like as DATE column. i tried but insert statement is not generating column for Timestamp columns.
Highly appreciated for such solutions ..Hope you could add some part for TIMESTAMP as well.. It would be great help for me if script can handle for TIMESTAMP data types.

Thanks,
Poshan Pandey