How to store the result of an execute immediate in PLSQL?

Question

I have a table which has select queries and insert queries stored as varchars. I have to execute the select query and insert the result of the select query using the insert query using a procedure. Right now I'm executing immediate and bulk collecting the select query into a table of varchars.

After this, I go to each row of vartable and take the values and put it in the insert query. I have a number of pairs of select and insert queries for a number of tables. So this has to be done dynamically. My question is, is there a better way to store the result of the select query? other than using a table of varchar? Because the result set of the select query might have millions of records and this might cause a problem. Would using a nested table type and using EXTEND on it solve the problem?

PROCEDURE SEL_INS
AS
  CURSOR C
  IS
    SELECT 
      SELEQRY SELQRY,
      INSQUERY INSERTQRY,
      cols COLS
      FROM TAB1;
      selqry  VARCHAR2(1000);
      insqry VARCHAR2(1000);
      tab1 vartable:=vartable();
      cols   NUMBER;

 BEGIN
   tab1:=vartable(NULL);
     FOR X    IN C
      LOOP
          selqry:= X.SELQRY;
          cols:=X.COLS;
  EXECUTE immediate selqry bulk collect INTO tab1;
 -- select statement is concatenated before executing. so that each index has one record    
 --with values separated by commas 
   --- a sample column in tab1 will have values like (abc,abc1,abc2)
     FOR i IN 1..tab1.count
     LOOP
       insqry :=X.INSERTQRY; 
       --- insert query will have values like insert into tab2   values('abc,'abc1','abc2')

         EXECUTE immediate insqry;

     END LOOP;
   END LOOP;


  END SEL_INS;

vartable is a table of type varchars2(4000)

Answer 1

As given in comments you should try to rewrite your statement as INSERT INTO ... SELECT .... Let's assume this is not possible for whatever reason. In this case you can use procedure as below:

PROCEDURE SEL_INS AS

    CURSOR C IS
    SELECT SELEQRY, INSQUERY, COLS
    FROM TAB1;

    selqry  VARCHAR2(1000);
    insqry VARCHAR2(1000);
    tab1 vartable;
    cols   NUMBER;

    cur INTEGER;
    res INTEGER;
    col_cnt INTEGER;
    desctab DBMS_SQL.DESC_TAB;
    i INTEGER;

BEGIN

    FOR aQuery IN C LOOP
        tab1 := vartable();

        cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur, aQuery.SELEQRY, DBMS_SQL.NATIVE);
        DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, desctab)
        FOR i IN 1..col_cnt LOOP
            DBMS_SQL.DEFINE_COLUMN(cur, i, desctab(i).COL_NAME, 2000);
        END LOOP;
        res := DBMS_SQL.EXECUTE_AND_FETCH(cur, TRUE);
        FOR i IN 1..col_cnt LOOP
            tab1.EXTEND;
            DBMS_SQL.COLUMN_VALUE(cur, i, tab1(tab1.LAST));
        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(cur);

        -- ... do whatever with tab1(xyz) -> otherwise this procedure would be an overkill

        cur := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur, aQuery.INSQUERY, DBMS_SQL.NATIVE);
        i := tab1.FIRST;
        WHILE i IS NOT NULL LOOP
            DBMS_SQL.BIND_VARIABLE(cur, ':b'||i, tab1(i));
            i := tab1.NEXT(i);
        END LOOP;
        res := DBMS_SQL.EXECUTE(cur);
        DBMS_SQL.CLOSE_CURSOR(cur);

    END LOOP;   

END;

Note, this procedure presumes that all columns are VARCHAR2 data type (with max. length of 2000 chars). If you have other data types, then line DBMS_SQL.DEFINE_COLUMN(cur, i, desctab(i).COL_NAME, 2000); must be extended like IF desctab(c).col_type = 1 THEN ...

Also, note DBMS_SQL.EXECUTE_AND_FETCH will fail unless your select returns exactly one row. If your query may return more than just one row you have to use

DBMS_SQL.EXECUTE(cur);
WHILE (DBMS_SQL.FETCH_ROWS(cur) > 0) LOOP
   ...
END LOOP;

See Oracle Built-in Data Types to get the code number of each data type.

Answer 2

Assuming you are able to change the data that is stored in the table that stores the sql statements to be run, this is an example of what I'd get the stored data to look like:

INSERT INTO sel_ins (selqry, insquery)
SELECT 'select col1, col2, col3 from table1' selqry, 'insert into other_table1 (col1, col2, col3)') insqry FROM dual
UNION ALL
SELECT 'select col1, col2 from table2' selqry, 'insert into other_table2 (col1, col2)') insqry FROM dual
UNION ALL
SELECT 'select col1, col2, col3 from table3 where col4 = ''fred''' selqry, 'insert into other_table3 (col1, col2, col3)') insqry FROM dual;

By doing that, your procedure to do the inserts is now much simpler:

PROCEDURE sel_ins IS
  CURSOR ins_sel_cur IS
    SELECT seleqry selqry, insquery insqry, cols cols
    FROM   tab1;
  v_selqry tab1.seleqry%TYPE;
  v_insqry tab1.insquery%TYPE;
BEGIN
  FOR ins_sel_rec IN ins_sel_cur
  LOOP
    EXECUTE IMMEDIATE ins_sel_rec.v_insqry || CHR(10) || ins_sel_rec.v_selqry;
  END LOOP;
END;
/

This way, you're not retrieving a load of data from a table and storing it in memory, only to take that data and add it back into another table row-by-row - you're doing all that work in a single DML statement (equivalent to a builder getting the truck containing all the bricks she needs to deliver right to the point at which she's building the wall, rather than at the bottom of the drive and then going and fetching each brick one by one). This should make things a lot faster, not to mention easier to read, maintain etc.

If you prefer to store the columns separately, e.g.:

INSERT INTO sel_ins (selqry, insquery, cols)
SELECT 'select <COLS> from table1' selqry, 'insert into other_table1 (<COLS>)') insqry, 'col1, col2, col3' cols FROM dual
UNION ALL
SELECT 'select <COLS> from table2' selqry, 'insert into other_table2 (<COLS>)') insqry, 'col1, col2' cols FROM dual
UNION ALL
SELECT 'select <COLS> from table3 where col4 = ''fred''' selqry, 'insert into other_table3 (<COLS>)') insqry, 'col1, col2, col3' cols FROM dual;

Then your execute immediate becomes:

execute immediate replace(ins_sel_rec.v_insqry, '<COLS>', ins_sel_rec.cols) ||
                  chr(10) ||
                  replace(ins_sel_rec.v_selqry, '<COLS>', ins_sel_rec.cols);