1

I have the need to dynamic know the name of the table that has the same data structure as many others and I can pass in a generic associative array that is of the same structure. Here is the proc

PROCEDURE INSRT_INTER_TBL(P_TABLE_NAME IN VARCHAR2, P_DATA IN tt_type)

IS

BEGIN
    FORALL i IN P_DATA.FIRST .. P_DATA.LAST
        EXECUTE IMMEDIATE
          'INSERT INTO ' || P_TABLE_NAME ||
          ' VALUES  :1'
        USING P_DATA(i);
END INSRT_INTER_TBL;

I am getting the following error

ORA-01006: bind variable does not exist

What am I missing here?

So I had to specify all the columns necessary to insert to the table out in the insert statement like:

PROCEDURE INSRT_INTER_TBL(P_TABLE_NAME IN VARCHAR2, P_DATA IN inter_invc_ln_item_type)

IS

BEGIN
    FORALL i IN P_DATA.FIRST .. P_DATA.LAST
        EXECUTE IMMEDIATE
          'INSERT INTO ' || P_TABLE_NAME || ' (ITEM_PK, pk, units, amt) ' ||
          ' VALUES  (:P_INVC_LN_ITEM_PK, :PK, :UNITS, :AMT)'
        USING IN P_DATA(i).item_pk, P_DATA(i).pk, P_DATA(i).units, P_DATA(i).amt;
END INSRT_INTER_TBL;
programmerNOOB
  • 121
  • 3
  • 19

2 Answers2

2

The TABLE operator works better than a FORALL here. It uses less code and probably skips some SQL-to-PL/SQL context switches.

--Simple record and table type.
create or replace type tt_rec is object
(
    a number,
    b number
);

create or replace type tt_type is table of tt_rec;

--Sample schema that will hold results.
create table test1(a number, b number);

--PL/SQL block that inserts TT_TYPE into a table.
declare
    p_table_name varchar2(100) := 'test1';
    p_data             tt_type := tt_type(tt_rec(1,1), tt_rec(2,2));
begin
    execute immediate
    '
        insert into '||p_table_name||'
        select * from table(:p_data)
    '
    using p_data;
    commit;
end;
/

You can run the above code in this SQL Fiddle.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
0

Try VALUES (:1) i.e. have brackets around :1

BA.
  • 924
  • 7
  • 10
  • tried that with no luck but that does bring up a good point. This AA has 5ish columns in it, do I need to specify all of the columns in the values? IE specify all column names and all values? the table is unique among all associated tables except for 1 column and I got around that by creating a new type:that is generic for that one column which is a FK, pk value, so instead of like clm_pk, it is just pk in my record type – programmerNOOB Oct 03 '17 at 15:42
  • If you have more than just the column in the table, then, yes, it won't work unless you specified the columns you're inserting: `insert into table1 (col1) values (:1)` or what you did to have all columns – BA. Oct 03 '17 at 16:17