1 2 3 Previous Next 33 Replies Latest reply: Aug 18, 2009 3:46 AM by Billy~Verreynne RSS

    generating INSERT statements

    668822
      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
          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
            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
              SanjayRs
              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
                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
                  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
                    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
                      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
                        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
                          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
                            ORA-00907: missing right parenthesis
                            Probably a db version issue: My query run on 11.1.0.7
                            • 11. Re: generating INSERT statements
                              668822
                              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
                                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
                                  Billy~Verreynne
                                  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
                                    Billy~Verreynne
                                    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