DBA_ERRORS and error lines reported for TRIGGERS

As you probably know, the lines shown in views DBA_ERRORS/ALL_ERRORS/USER_ERRORS in the case of triggers are wrong.

But they are not totally wrong, they are just shifted by the amount of lines between the line containing the keyword TRIGGER and either DECLARE or BEGIN, whichever occurs first.

See the example below for an error reported by the dba_errors view on line 2 caused by missing grants on the table used in the variable declaration:

TRIGGER GAGREA2.TR_ASSEGNAZIONE_AI
   AFTER INSERT ON ASSEGNAZIONE
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
   LI_ID_ASSEGNAZIONE_DOM_RICGEN             GAGREA2.DOMANDA_ASG_SPECIFICHE.ID_ASSEGNAZIONE_DOM_RICGEN%TYPE;
BEGIN

   GAGREA2.PKG_TRIGGER.SET_ID_ASSEGNAZIONE_DOM_RICGEN ( :NEW.ID_DOMANDA, LI_ID_ASSEGNAZIONE_DOM_RICGEN );

END TR_ASSEGNAZIONE_AI;

So, the real line number can be obtained adding the number of the line containing DECLARE (or BEGIN if DECLARE is missing) minus 1, that is 2 + 5 - 1 = 6.

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.

ORA-03001: unimplemented feature

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

ORA-03001: unimplemented feature

This error can be returned attempting to compile a PL/SQL unit containing a FORALL statement, as follows:

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

create or replace
procedure test_pls_435
is

type rec_type     is record (col_b tab_pls_435.col_b%type);
type tab_type     is table of rec_type;
plsql_rec_tab     tab_type;

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

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

end;

My current understanding is that currently is not possible to specify a subset of columns in an bulk INSERT statement that bulk binds a RECORD type collection inside FORALL.
Note that in the code above, if we get rid of column specification, we incur in ORA-00947, however this is caused by the mismatching number of columns between the table structure and the PL/SQL RECORD data type.
If you can redefine the RECORD definition to match the table structure, then the code can be compiled and executed successfully, as follows:

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;

I ignore if ORA-03001 can be returned also in different situations.

See message translations for ORA-03001 and search additional resources.

Updated January 4, 2008:
If you are getting this error when using a CAST/MULTISELECT construct, check out the comments section!

PLS-00363: expression cannot be used as an assignment target

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

PLS-00363: expression '%s' cannot be used as an assignment target

where %s is a placeholder for the actual string that applies to the particular case.

Oracle raises this error at compilation time when it detects an unsuitable parameter type in the parameter list of a procedure or function call, as in the following cases (the list i give here is presumably a sub-set of all possible situations):

case #1

declare
c_val   constant varchar2(10) := 'test';
begin
test_proc(c_val);           -- test_proc's parameter is declared as IN/OUT,
                           -- but c_val is a constant
end;

ORA-06550: line 4, column 11:
PLS-00363: expression 'C_VAL' cannot be used as an assignment target
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored

case #2

create or replace procedure test_proc2 (p_in in varchar2) is
begin
test_proc(p_in);           -- test_proc's parameter is IN/OUT,
                          -- but p_in is an IN parameter of the calling procedure
end;

PLS-00363: expression 'P_IN' cannot be used as an assignment target

case #3

begin
test_proc(10);         -- test_proc's parameter is IN/OUT,
                      -- but you are passing a literal value
end;

ORA-06550: line 2, column 11:
PLS-00363: expression '10' cannot be used as an assignment target
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

The same error is raised in the previous situations if procedure's parameter is declared as OUT instead of IN/OUT.

case #4

declare
rtn_val   constant varchar2(10) := 'test';
begin
rtn_val := test_fn('silly case');  -- cannot assign function's result to a constant
end;

ORA-06550: line 4, column 3:
PLS-00363: expression 'RTN_VAL' cannot be used as an assignment target
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

however we could use the function to initialize the constant value inside the declaration:

declare
rtn_val   constant varchar2(10) := test_fn('silly case');
begin
dbms_output.put_line(rtn_val);
end;

Needless to say, assigning a value to a constant inside the program body is forbidden in all cases as it is a nonsense. Constants must be always initialized with some value in the declaration section.

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