10 Replies Latest reply on May 11, 2012 11:19 AM by Hesh

    Dynamic SQL with Bulk into record type

    Hesh
      Oracle 10.2 g

      I got this from Tom

      [http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO:::]

      I am able to do this without dynamic SQL, but my requirement is to do this in dynamic SQL
       create table t1 ( x int, y int );
      
       insert into t1 select rownum, rownum+1 from all_users where rownum <= 5;
      
       create table t2 ( x int, y int, z int );
      
       declare
                  type array is table of t1%rowtype;
                  l_data array;
          begin
                  select * bulk collect into l_data from t1;
        
                  forall i in 1 .. l_data.count
                  
                         execute immediate 'insert into (select x, y from t2) values :x' using l_data(i);
          end;
       
      Error at line 1
      ORA-06550: line 9, column 90:
      PLS-00457: expressions have to be of SQL types
      ORA-06550: line 9, column 20:
      PL/SQL: Statement ignored
      There is some workaround in 11g but can we do something in 10g?



      Thanks,
      Hesh.
        • 1. Re: Dynamic SQL with Bulk into record type
          BluShadow
          So you want some sort of dynamic collection that is determined at run time based on a dynamic SQL that is determined at runtime.
          And how do you expect any of your PL/SQL code to be able to access the data in this dynamic collection? Or are you planning on generating some dynamic PL/SQL code to do that too?

          Why you come across something that you think needs doing "dynamically" then it's always a good idea to step back and consider just what it is you are trying to achieve. There are very few good reasons for using dynamic queries or code.

          Here's an example from my library of examples that demonstrates the use of the DBMS_SQL package to deal with dynamic queries and the data returned from them...


          As sys user:
          CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
          /
          GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
          /
          As myuser:
          CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                               ,p_dir IN VARCHAR2
                                               ,p_header_file IN VARCHAR2
                                               ,p_data_file IN VARCHAR2 := NULL) IS
            v_finaltxt  VARCHAR2(4000);
            v_v_val     VARCHAR2(4000);
            v_n_val     NUMBER;
            v_d_val     DATE;
            v_ret       NUMBER;
            c           NUMBER;
            d           NUMBER;
            col_cnt     INTEGER;
            f           BOOLEAN;
            rec_tab     DBMS_SQL.DESC_TAB;
            col_num     NUMBER;
            v_fh        UTL_FILE.FILE_TYPE;
            v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
          BEGIN
            c := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
            d := DBMS_SQL.EXECUTE(c);
            DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
            FOR j in 1..col_cnt
            LOOP
              CASE rec_tab(j).col_type
                WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
                WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
                WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
              ELSE
                DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
              END CASE;
            END LOOP;
            -- This part outputs the HEADER
            v_fh := UTL_FILE.FOPEN(upper(p_dir),p_header_file,'w',32767);
            FOR j in 1..col_cnt
            LOOP
              v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
            END LOOP;
            --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
            UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
            IF NOT v_samefile THEN
              UTL_FILE.FCLOSE(v_fh);
            END IF;
            --
            -- This part outputs the DATA
            IF NOT v_samefile THEN
              v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
            END IF;
            LOOP
              v_ret := DBMS_SQL.FETCH_ROWS(c);
              EXIT WHEN v_ret = 0;
              v_finaltxt := NULL;
              FOR j in 1..col_cnt
              LOOP
                CASE rec_tab(j).col_type
                  WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                              v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                  WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                              v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
                  WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                              v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
                ELSE
                  v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
                END CASE;
              END LOOP;
            --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
              UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
            END LOOP;
            UTL_FILE.FCLOSE(v_fh);
            DBMS_SQL.CLOSE_CURSOR(c);
          END;
          This allows for the header row and the data to be written to seperate files if required.

          e.g.
          SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
           
          PL/SQL procedure successfully completed.
          Output.txt file contains:
          empno,ename,job,mgr,hiredate,sal,comm,deptno
          7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
          7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
          7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
          7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
          7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
          7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
          7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
          7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
          7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
          7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
          7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
          7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
          7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
          7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
          The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.

          Adapt to output different datatypes and styles are required.
          1 person found this helpful
          • 2. Re: Dynamic SQL with Bulk into record type
            Prabodh
            type array is table of t1%rowtype;
            Is Assoc Array of t1%rowtype (record)
            select * bulk collect into l_data from t1;
            Returns a comma separated list of columns, so you need one "type" assoc. array for each returned column to the right of the INTO.
            Something like
            select * bulk collect into l_col1, l_col2,.... from t1; -- means you need to know the columns being returned by query.
            
            --where
            
            l_col1    owa_util.vc_arr; -- I am lazy, so use existing defs
            Regards.

            %ROWTYPE is a PL/SQL thing, not SQL.
            So, if you must use %ROWTYPE then you have to use the CURSOR in PL/SQL to populate your Assoc. Array.

            Edited by: Prabodh on May 10, 2012 5:11 PM
            • 3. Re: Dynamic SQL with Bulk into record type
              YLN
               execute immediate 'insert into (select x, y from t2) values :x' using l_data(i);
              There is no table name into "Insert into" clause. Pls check
              • 4. Re: Dynamic SQL with Bulk into record type
                Hesh
                Thank you all..

                may be I should be more clear on what my intention is..

                I am able to run following code
                  declare
                            type array is table of t1%rowtype;
                            l_data array;
                    begin
                            select * bulk collect into l_data from t1;
                  
                            forall i in 1 .. l_data.count
                                    insert into (select x, y from t2) values l_data(i);
                    end;
                 
                but following not working,
                declare
                            type array is table of t1%rowtype;
                            l_data array;
                    begin
                            select * bulk collect into l_data from t1;
                  
                            forall i in 1 .. l_data.count
                            
                            execute immediate 'insert into (select x, y from t2) values :x' using l_data(i);
                    end;
                I just want a dynamic SQL for insert statement with FORALL caluse along with collections.

                There are very few good reasons for using dynamic queries or code.
                I too believe the same, but unfortunately(fortunately some times :) ) we need to deal with some old code to do modify


                Thanks,
                • 5. Re: Dynamic SQL with Bulk into record type
                  kendenny
                  forall must be followed by a SQL statement. Execute immediate is a PL/SQL statement and can't be used with a forall. You will have to use a for loop rather than a forall.
                  • 6. Re: Dynamic SQL with Bulk into record type
                    Hesh
                    forall must be followed by a SQL statement. Execute immediate is a PL/SQL statement
                    Following code(execute immediate followed by ForAll ) is working in 11g.. not in 10 g
                    declare
                    
                         TYPE t_a IS TABLE OF VARCHAR2(30);
                         TYPE t_b IS TABLE OF VARCHAR2(30);
                         
                         TYPE REC_CLM_ELIGDATA IS RECORD(z_a NUMBER,z_b number);  
                         
                         TYPE R_CLM_ELIGDATA IS TABLE OF REC_CLM_ELIGDATA INDEX BY BINARY_INTEGER;
                    
                         C_Ins_Eligdata   R_Clm_Eligdata;
                         
                         l_a t_a;
                         l_b t_b;
                         
                         cursor c_cursor is SELECT a, b  FROM tmp9;
                         
                         v_str long;
                         
                         BEGIN
                         dbms_output.put_line('Before Bulk Collect: ' || systimestamp);
                         open c_cursor;
                         
                         fetch c_cursor bulk collect into  C_Ins_Eligdata; --l_a,l_b; 
                            
                         
                         loop
                         
                         FORALL indx IN C_Ins_Eligdata.first..C_Ins_Eligdata.last 
                         
                         EXECUTE IMMEDIATE 'INSERT INTO ((select a, b from tmp9) ) values (:1, :2)'
                                            using
                                            C_Ins_Eligdata(indx).z_a ,C_Ins_Eligdata(indx).z_b;
                           
                         
                         dbms_output.put_line('222222222222222: ' || systimestamp);
                         
                         exit when c_cursor%notfound;
                         end loop;
                         COMMIT;
                         close c_cursor;
                         end;
                    Thanks,
                    • 7. Re: Dynamic SQL with Bulk into record type
                      Billy~Verreynne
                      Hesh wrote:

                      but following not working,
                      declare
                      type array is table of t1%rowtype;
                      l_data array;
                      begin
                      select * bulk collect into l_data from t1;
                      
                      forall i in 1 .. l_data.count
                      
                      execute immediate 'insert into (select x, y from t2) values :x' using l_data(i);
                      end;
                      I just want a dynamic SQL for insert statement with FORALL caluse along with collections.
                      Does not make much sense.

                      You fetch data from the SQL engine to the record type array. So the data output for that SQL cursor needs to be fetched from the SQL engine and copied into PL/SQL engine memory.

                      Next you send that VERY SAME DATA back to the SQL engine to be used by a SQL insert cursor.

                      Where is the logic behind pulling data from SQL into a PL/SQL array structure, and then pushing that very same array data structure back to the SQL engine? What is the purpose of sending the data on a non-performant and non-scalale detour via the PL/SQL engine?

                      Do you have any justification (technical or functional wise) to backup this nonsensical approach?

                      Why can this not be achieved using a single SQL cursor that does both the selecting (fetching) and (bulk) inserting - using the plain old INSERT ... SELECT structure?

                      And if the insert part is variable, so what? Create a dynamic INSERT .. SELECT cursor and execute it (using bind values). That simple... Right?
                      • 8. Re: Dynamic SQL with Bulk into record type
                        Hesh
                        Make sense..

                        Thanks Billy for your detail explanation regarding the context switches between SQL and PL/SQL engines...

                        In my new Organization all the code related ETL process is Dynamic SQL ! with very complex procedures with many DML/DDL statements,Hints(Append and other hints), Create Indexes, Gather Stats statements, Drop objects,etc.. embedded in Dynamic SQL , very hard to work with them. These are developed by very experienced professionals (around 10-15 Years of exp in Oracle).

                        Right now my chance to question the process is less as I am only 4 years experienced . I would like to spend some time with this code and try to understand why they adopted this process.

                        I have successfully developed many ETL process in my old organization but never thought of using Dynamic SQL :)


                        Thanks,
                        Hesh.
                        • 9. Re: Dynamic SQL with Bulk into record type
                          Billy~Verreynne
                          Hesh wrote:

                          In my new Organization all the code related ETL process is Dynamic SQL ! with very complex procedures with many DML/DDL statements,Hints(Append and other hints), Create Indexes, Gather Stats statements, Drop objects,etc.. embedded in Dynamic SQL , very hard to work with them. These are developed by very experienced professionals (around 10-15 Years of exp in Oracle).
                          Number of years is a very poor indicator of experience. It equally well could be a measure of how long a person has been incompetent.
                          • 10. Re: Dynamic SQL with Bulk into record type
                            Hesh
                            Number of years is a very poor indicator of experience. It equally well could be a measure of how long a >person has been incompetent.
                            :) Very interesting perspective ! I know one saying...

                            'Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction'