This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Aug 18, 2009 1:46 AM by BillyVerreynne RSS

generating INSERT statements

668822 Explorer
Currently Being Moderated
hello all,

i am not very efficient in development activities, and i am asked to create a procedure to generating insert statement for each record, in each table for each column for migration purpose...

i know it is very difficult task, and i need to make a seperate .txt or .sql file for those insert statements,

how can i achieve this???? i have set UTL_FILE_DIR to guest_dir and grant permissions to specific user, now there are 415 tables in schema..

what i have done so far is.........
create or replace procedure insert_data is
l_srno          NUMBER:=0;
    l_chardate      VARCHAR2(1000);
    l_filename      VARCHAR2(1000);
    l_filehandle utl_file.file_type;
    b_file utl_file.file_type;
    l_ip            VARCHAR2(100); 
    l_flag          CHAR(1):='N';
    RecordGroup varchar2(5000);
    l_Str          VARCHAR2(32767):= NULL;
    l_Str1          VARCHAR2(32767):= 'SELECT ' ;
    l_Cm_Tbl_Name  VARCHAR2(32767):= NULL;    
    l_Rg_id        RecordGroup%TYPE; 
     l_Rg_name      VARCHAR2(40) := 'schema_name';
    l_Rec_Cursor   NUMBER;  
    l_Cur_Rec      NUMBER := 0;
    GroupColumn varchar2(5000);
    l_Gc_Id        GroupColumn%TYPE; 
    l_Data_Type    VARCHAR2(50);
    l_Length       NUMBER(10);
     l_Number_Type  NUMBER;
     l_Varchar_Type VARCHAR2(2000);
    l_Date_Type    DATE;    
    l_Rec_Status  NUMBER; 
          L_COUNT                    NUMBER :=0;
CURSOR tablename IS
               SELECT     table_name 
               FROM          user_tables;
      
CURSOR TableCol (a_tablename user_tables.table_name%TYPE) IS
SELECT     column_name,
                                   data_type  
               FROM          user_tab_columns w 
               WHERE          w.table_name=a_tablename
      and column_name='CREATEDON';
      
begin

l_filehandle :=utl_file.fopen('guest_dir','insert.sql','W');
      
      FOR k IN tablename 
      LOOP
    
        FOR t IN TableCol(k.table_name) 
        LOOP
                 
          IF TableCol%ROWCOUNT=1 THEN
            l_Str  := t.column_name ;
            l_Str1  := l_Str1||t.column_name ;
          ELSE
            l_Str  := l_Str ||','||t.column_name ;
            l_Str1  := l_Str1 ||','||t.column_name ;
          END IF;
        END LOOP;

        l_Str1 := l_Str1||' '||' FROM '||k.table_name;
        

         
         *IF Not Id_Null(l_Rg_id) THEN* 

             Delete_Group(l_Rg_id);    
         END IF; 
         
        MESSAGE(l_Str1);
        MESSAGE(l_Str1);
        l_Rg_id := Create_Group_From_Query(l_Rg_name,l_Str1);
        l_Rec_Cursor := Populate_Group(l_Rg_id); 

         IF l_Rec_Cursor = 0 THEN  
          l_Cur_Rec := Get_Group_Row_Count( l_Rg_id ); 
          SYNCHRONIZE;        
           FOR J IN 1..L_CUR_REC
           LOOP
                              utl_file.new_line(l_filehandle);
                              utl_file.put(l_filehandle,'INSERT INTO '||K.table_name||' ('||l_Str ||' ) values ');      
                              utl_file.new_line(l_filehandle);
                              utl_file.put(l_filehandle,'(');
                               FOR i IN TableCol(k.table_name)
                                    LOOP
                                l_Gc_Id := FIND_COLUMN( l_Rg_Name||'.'||I.COLUMN_NAME); 
              
              IF NOT Id_Null(l_Gc_Id) THEN 
                                         SELECT  DATA_TYPE,
                                                             DATA_LENGTH 
                              INTO  L_DATA_TYPE,
                                             L_LENGTH 
                                           FROM  USER_TAB_COLUMNS 
                                      WHERE  TABLE_NAME  = k.table_name
                                        AND  COLUMN_NAME = i.column_name  ;                                         

                                             IF L_DATA_TYPE = 'VARCHAR2' OR L_DATA_TYPE = 'CHAR' THEN
                                                  L_VARCHAR_TYPE := GET_GROUP_CHAR_CELL( l_Gc_Id, j ); 
                                                       utl_file.PUT(l_filehandle,''''||(NVL(L_VARCHAR_TYPE,' '))||''''||',');
                                             ELSIF L_DATA_TYPE = 'NUMBER' THEN
                                                  L_NUMBER_TYPE := GET_GROUP_NUMBER_CELL( l_Gc_Id, j ); 
                                                       utl_file.PUT(l_filehandle,(NVL(L_NUMBER_TYPE,' '))||',');
                                             ELSIF L_DATA_TYPE = 'DATE' THEN
                                                  L_DATE_TYPE := GET_GROUP_DATE_CELL( l_Gc_Id, j ); 
                                                            utl_file.PUT(l_filehandle,'TO_DATE('''||NVL(To_Char(L_DATE_TYPE,'dd/mm/yyyy hh24:mi:ss'),' ')||','''||'dd-mon-rrrr hh24:mi'''||')'||',' );                                                            
                                             END IF;
                                    END IF;
                                   l_Str1:='SELECT ';
                                                                   
                                    END LOOP;     
                                    utl_file.put(l_filehandle,')');
                                   utl_file.new_line(l_filehandle);                                    
                                   utl_file.put(l_filehandle,'COMMIT;');                                    
                       utl_file.new_line(l_filehandle,1);
       
                       END LOOP;          
                  END IF;
             END LOOP;           
                utl_file.new_line(l_filehandle);
            utl_file.put(l_filehandle,('EXIT'));
            utl_file.fclose(l_filehandle);
         Delete_Group(l_Rg_id);          
EXCEPTION
      WHEN FORM_TRIGGER_FAILURE THEN
      utl_file.FCLOSE(l_filehandle);
          RAISE;

      WHEN OTHERS THEN
           utl_file.FCLOSE(l_filehandle);
          RAISE FORM_TRIGGER_FAILURE;
end insert_data;
and i am getting error "Error: PLS-00201: identifier 'ID_NULL' must be declared"


any suggestion will be valuable..

thanks and regards
VD
  • 1. Re: generating INSERT statements
    539769 Pro
    Currently Being Moderated
    IF Not Id_Null(l_Rg_id) THEN
    Instead of above use IF l_Rg_id<>NULL THEN

    Regards.
  • 2. Re: generating INSERT statements
    668822 Explorer
    Currently Being Moderated
    thanks virendra


    but now i am getting
    Error: PLS-00201: identifier 'L_RG_IDNULL' must be declared
    thanks and regards
    VD
  • 3. Re: generating INSERT statements
    481319 Guru
    Currently Being Moderated
    Why don't you use sqlldr to load data, Use UTL_FILE to generate the CSV file.
    Insert statements for each record will be very inefficient.
    Here is a link to get csv file
    Re: External table ORA-30657: operation not supported on external organized tab

    HTH

    SS
    http://db-oracl.blogspot.com
  • 4. Re: generating INSERT statements
    539769 Pro
    Currently Being Moderated
    Sorry boss I have posted
    IF L_RG_ID != NULL in other syntax as using < and > which did not get posted

    Regards.
  • 5. Re: generating INSERT statements
    MichaelS Guru
    Currently Being Moderated
    This seems to be (or migrated from) a FORMS trigger code. Do you want to generate the Insert's from within FORMS?

    If so I'd suggest to post in the Forms forum.

    Otherwise come back and also mention your db version.
  • 6. Re: generating INSERT statements
    668822 Explorer
    Currently Being Moderated
    hello sir,

    ya it is from forms trigger, but i do not want to do that in forms, and need a file that contains insert statement for all columns, and all transcations for all tables in that schema..

    my DB version is : 10.2.0.1
    OS: RHEL 5 x86

    i would be great if you suggest a procedure to generate all insert statement in a file as sanjay mentioned

    @sanjay

    thanks for your link, i will read it and get back to u..


    thanks and regards
    VD
  • 7. Re: generating INSERT statements
    668822 Explorer
    Currently Being Moderated
    hello sanjsy,

    but what this procedure will do?????

    i tried it, and having utl_file_dir creatd and grant permission on that to user, when i compile it it get compile and when i call it it gives me
    ORA-06576: not a valid function or procedure name
    i think it will generate a .csv file in mu utl_file??? am i right....
    i did search it on google, but didn't got any satisfactory answer

    thanks and regards
    VD
  • 8. Re: generating INSERT statements
    MichaelS Guru
    Currently Being Moderated
    Another way might be
    SQL>  select 'insert into ' || table_name || ' (' ||
           rtrim( xmlquery ('for $i in ROW/* return concat(name($i),",")' passing t.column_value.extract('ROW') returning content),',') || ') values (' ||
           utl_i18n.unescape_reference(rtrim( xmlquery ('for $i in ROW/* return concat("''", $i, "''",",")' passing t.column_value.extract('ROW') returning content),',')) || ');' ins_stmt
     from user_tables, xmltable('ROW' passing dbms_xmlgen.getxmltype('select * from ' || table_name).extract('ROWSET/ROW')) t
    where table_name = 'EMP'
    
    INS_STMT                                                                        
    --------------------------------------------------------------------------------
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values (
    '7369', 'SMITH', 'CLERK', '7902', '17.12.1980 00:00:00', '800', '20');                  
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (
    '7499', 'ALLEN', 'SALESMAN', '7698', '20.02.1981 00:00:00', '1600', '300', '30'); 
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (
    '7521', 'WARD', 'SALESMAN', '7698', '22.02.1981 00:00:00', '1250', '500', '30');  
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7566', 
    'JONES', 'MANAGER', '7839', '02.04.1981 00:00:00', '2975', '20');               
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (
    '7654', 'MARTIN', 'SALESMAN', '7698', '28.09.1981 00:00:00', '1250', '1400', '30')
    ;                                                                               
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7698', 
    'BLAKE', 'MANAGER', '7839', '01.05.1981 00:00:00', '2850', '30');               
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7782', 
    'CLARK', 'MANAGER', '7839', '09.06.1981 00:00:00', '2450', '10');               
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7788', 
    'SCOTT', 'ANALYST', '7566', '19.04.1987 00:00:00', '3000', '20');               
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO) values ('7839', 'KING', 
    'PRESIDENT', '17.11.1981 00:00:00', '5000', '10');                           
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (
    '7844', 'TURNER', 'SALESMAN', '7698', '08.09.1981 00:00:00', '1500', '0', '30');  
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7876', 
    'ADAMS', 'CLERK', '7788', '23.05.1987 00:00:00', '1100', '20');                 
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7900', 
    'JAMES', 'CLERK', '7698', '03.12.1981 00:00:00', '950', '30');                  
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7902', 
    'FORD', 'ANALYST', '7566', '03.12.1981 00:00:00', '3000', '20');                
                                                                                    
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values ('7934', 
    'MILLER', 'CLERK', '7782', '23.01.1982 00:00:00', '1300', '10');                
                                                                                    
    
    14 rows selected.
  • 9. Re: generating INSERT statements
    533779 Newbie
    Currently Being Moderated
    Appers to me a great query, but when I tried to run it is comes up with following..

    select 'insert into ' || table_name || ' (' ||
    rtrim( xmlquery ('for $i in ROW/* return concat(name($i),",")
    ' passing t.column_value.extract('ROW') returning content),',') || ')
    values (' ||utl_i18n.unescape_reference(rtrim( xmlquery ('for $i in ROW/* return concat("''", $i, "''",",")'
    passing t.column_value.extract('ROW') returning content),',')) || ');'
    ins_stmt from user_tables, xmltable('ROW' passing dbms_xmlgen.getxmltype
    ('select * from ' || table_name).extract('ROWSET/ROW')) t
    where table_name = 'EMP'

    ' passing t.column_value.extract('ROW') returning content),',') || ')
    *
    ERROR at line 3:
    ORA-00907: missing right parenthesis

    hare krishna
  • 10. Re: generating INSERT statements
    MichaelS Guru
    Currently Being Moderated
    ORA-00907: missing right parenthesis
    Probably a db version issue: My query run on 11.1.0.7
  • 11. Re: generating INSERT statements
    668822 Explorer
    Currently Being Moderated
    hello michaels2 ,

    are you sure that it is going to work in oracle 10.2.0.1????

    because xml query seems problematic to me...and it is not going to create any fiile inwhich it is generating these sql statements....

    it is not working in 10.2.0.1...

    thanks and regards
    VD

    Edited by: vikrant dixit on Aug 2, 2009 10:03 PM
  • 12. Re: generating INSERT statements
    668822 Explorer
    Currently Being Moderated
    hello

    actualy your atatement worked but like this

    select 'insert into ' || table_name || ' (' ||
           rtrim( xmlquery ('for $i in ROW/* return concat(name($i),",")' passing t.column_value.extract('ROW') returning content),',') || ') values (' ||
           utl_i18n.unescape_reference(rtrim( xmlquery ('for $i in ROW/* return concat("''", $i, "''",",")' passing t.column_value.extract('ROW') returning content),',')) || ');' ins_stmt
     from user_tables, xmltable('ROW' passing dbms_xmlgen.getxmltype('select * from ' || table_name).extract('ROWSET/ROW')) t
    where table_name ='AC_AC_DTL';
    
    insert into AC_AC_DTL () values ();
    insert into AC_AC_DTL () values ();
    insert into AC_AC_DTL () values ();
    insert into AC_AC_DTL () values ();
    insert into AC_AC_DTL () values ();
    insert into AC_AC_DTL () values ();
    means with no values......

    how come is this???

    thanks and regards
    VD
  • 13. Re: generating INSERT statements
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    michaels2 wrote:
    Another way might be
    INS_STMT                                                                        
    --------------------------------------------------------------------------------
    insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) values (
    '7369', 'SMITH', 'CLERK', '7902', '17.12.1980 00:00:00', '800', '20');                  
    
    +<snipped>+
    Despite Micheals showing how it can be done.. this is a great way to totally trash the shared pool and cause memory errors - running hardcoded INSERT statements like these and not using bind variables.

    A prerequisite will be to force cursor sharing for the session first - this will prevent the shared pool from being trashed by non-sharable SQL:
    alter session set cursor_sharing=forced
    +See [Oracle® Database Reference|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams035.htm#i1125803] for details.+

    However... fact. It will be HORRIBLY slow. And not twice as slow or even three times as slow.. more like 20 times or more slower. So if the set of INSERT statements take a minute or so, doing it this way using hardcoded INSERT statements will easily take 20 minutes or more. As each and every statement is a hard parse.
  • 14. Re: generating INSERT statements
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    I will not consider using INSERT statements to do export of data... Have never had the reason to do this, despite often pushing data between databases. Also am very hard pressed to think of why I would ever want to do this. (no, I would not have had written the APEX installer to use this method either)

    What I usually do is run a SQL to spool the data to a CSV file. I.e. comma delimit the column values, one row per line.

    Then load in into the destination database using an external table (SQL*Loader functionality embedded in the SQL engine).
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points