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.