PLS-00457: expressions have to be of SQL types

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

PLS-00457: expressions have to be of SQL types

This error can be seen when attempting to compile a PL/SQL unit containing a FORALL statement in combination with EXECUTE IMMEDIATE, as follows:

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'
using
plsql_rec_tab(i);
end;

The PL/SQL parser doesn't like the syntax of the FORALL statement in combination with EXECUTE IMMEDIATE and a collection of RECORD data type.

Note however that the program can be successfully compiled and executed if we make the FORALL statement static (as opposed to native dynamic):

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;

See also PLS-00436 for a list of other possibilities with native dynamic SQL.

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

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

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

PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL

You may see this error when you attempt to compile a PL/SQL unit containing a FORALL statement combined with an EXECUTE IMMEDIATE statement like, as follows:

create or replace
procedure test_pls_435
is

type tab_type  is table of tab_pls_435%rowtype; -- defines a RECORD data type
plsql_rec_tab  tab_type := tab_type();          -- defines a collection of RECORDs

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;

Whilst it is possible to specify bulk FORALL statements, in practice there are limitations when RECORD data type collections are involved. If PLS-00436 is present in the list of parsing errors, then i suggest you to read that entry for a detailed explanation and try out alternative solutions.

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

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.