Run time insert using bulk update ,giving an internal error?

Question

Hi , I am trying to make a run time table named dynamic and inserting data into it from index by table using bulk update,but when i am trying to execute it this error is coming:

ERROR at line 1: ORA-06550: line 0, column 0: PLS-00801: internal error [74301

]

declare

     type index_tbl_type IS table of
        number
     index by binary_integer;
     num_tbl index_tbl_type;
     TYPE ref_cur IS REF CURSOR;
     cur_emp ref_cur;
    begin
         execute immediate 'create table dynamic (v_num number)';--Creating a run time tabl

         FOR i in 1..10000 LOOP
              execute immediate 'insert into dynamic values('||i||')';--run time insert
         END LOOP;
        OPEN cur_emp FOR 'select * from dynamic';--opening ref cursor
            FETCH cur_emp bulk collect into num_tbl;--bulk inserting in index by table
        close  cur_emp;

        FORALL i in num_tbl.FIRST..num_tbl.LAST --Bulk update
             execute immediate 'insert into dynamic values('||num_tbl(i)||')';
    end;

Answer 1

The FORALL statement is expecting a SQL statement - INSERT, UPDATE or DELETE. EXECUTE IMMEDIATE is a PL/SQL statement, which is why your code is hurling that exception.

Pulling this kind of stunt is not a good idea in Production. Tables should be built using DDL scripts not dynamic SQL.

Anyhoo, if you want to do something in this sort of dynamic stylee this is how to go about it:

Step 1: create a SQL type, which can be used in SQL statements

SQL> create or replace type my_nums as table of number
  2  /

Type created.

SQL>

Step 2: my version of your procedure, which uses the SQL table type instead of the PL/SQL one. I have rewritten the FORALL clause as a dynamic INSERT statement which uses the collection in a TABLE() clause.

SQL> declare
  2
  3        num_tbl my_nums;
  4        TYPE ref_cur IS REF CURSOR;
  5        cur_emp ref_cur;
  6  begin
  7      execute immediate 'create table dynamic (v_num number)';
  8
  9      FOR i in 1..10000 LOOP
 10           execute immediate 'insert into dynamic values('||i||')'
 11      END LOOP;
 12      OPEN cur_emp FOR 'select * from dynamic';
 13      FETCH cur_emp bulk collect into num_tbl;
 14      close  cur_emp;
 15
 16      execute immediate 
 17         'insert into dynamic select * from table(:1)' using num_tbl;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>

Step 3: it works

SQL> select count(*) from dynamic
  2  /

  COUNT(*)
----------
     20000

SQL>

Answer 2

DECLARE

            TYPE numlist is table of number index by binary_integer;

            var_num numlist;

BEGIN

            for i in 1..1000 loop

                        var_num(i):=i;

            end loop;

            EXECUTE IMMEDIATE 'create table exe_table(col1 number(10))';

            forall i in var_num.first..var_num.last

                        EXECUTE IMMEDIATE 'INSERT INTO exe_table values(:P)' USING var_num(i);

end loop; 

WHY this is working then?