PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following error along with PLS-00435.

PLS-00436: implementation restriction:
cannot reference fields of BULK In-BIND table of records

if you attempt to compile a PL/SQL procedure containing native dynamic SQL like this:

create table tab_pls_435 (
col_a number,
col_b varchar2(30));

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
execute immediate
'insert into tab_pls_435
values (:1,:2)'
using
plsql_rec_tab(i).col_a
,plsql_rec_tab(i).col_b;

end;

The problem here is that i defined a PL/SQL table of records and i am trying to bulk bind the individual fields of the record. The main problem however is with the EXECUTE IMMEDIATE type of native dynamic SQL that is also considered invalid thus returning PLS-00435.

The solution is to completely rewrite the code, depending on the specific case, in one of these ways:

1) by eliminating the bulk FORALL (slower solution)

 ...
for i in 1..plsql_rec_tab.count
loop
execute immediate
'insert into tab_pls_435
values (:1,:2)'
using
plsql_rec_tab(i).col_a
,plsql_rec_tab(i).col_b;
end loop;
...

2) by splitting the individual record fields into different collections, thus eliminating the RECORD data type collection, while keeping the bulk FORALL:

create or replace
procedure test_pls_435
is

type col_a_tab_type is table of tab_pls_435.col_a%type;
type col_b_tab_type is table of tab_pls_435.col_b%type;
plsql_col_a_tab     col_a_tab_type := col_a_tab_type();
plsql_col_b_tab     col_b_tab_type := col_b_tab_type();

begin
plsql_col_a_tab.extend;
plsql_col_b_tab.extend;
plsql_col_a_tab(1)           := 100;
plsql_col_b_tab(1)           := 'TEST';

forall i in 1..plsql_col_a_tab.count
execute immediate
'insert into tab_pls_435
values (:1, :2)'
using
plsql_col_a_tab(i), plsql_col_b_tab(i);

end;

3) It's worth noting that using native dynamic SQL makes things more complex.
Indeed if there isn't a valid reason for using native dynamic SQL, we could rewrite the code getting rid of EXECUTE IMMEDIATE and using a shorter syntax,:

create or replace
procedure test_pls_435
is

type tab_type     is table of tab_pls_435%rowtype;
plsql_rec_tab     tab_type := tab_type();

begin
plsql_rec_tab.extend;
plsql_rec_tab(1).col_a           := 100;
plsql_rec_tab(1).col_b           := 'TEST';

forall i in 1..plsql_rec_tab.count
insert into tab_pls_435
values plsql_rec_tab(i);

end;

The shorter syntax requires that the PL/SQL RECORD structure matches exactly the table structure. Trying to perform the insert on a subset of columns may lead to ORA-03001.

See message translations for PLS-00436 and search additional resources.