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!

ORA-00947: not enough values

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

You may encounter the following exception:

ORA-00947: not enough values

when you attempt to compile a PL/SQL unit containing a FORALL construct in conjunction with a collection based on a record data type:

DECLARE
TYPE prf_runs_type     IS TABLE OF plsql_profiler_runs%ROWTYPE;
rmt_runs_tab           prf_runs_type;
...
BEGIN
...
FORALL i IN 1..rmt_runs_tab.COUNT
INSERT INTO plsql_profiler_runs
  (runid, related_run, run_owner, run_date, run_comment, ..., spare1)
VALUES rmt_runs_tab(i);
...
END;

The problem with the FORALL statement above is in that i am specifying the list of fields to be inserted whereas the PL/SQL parser is expecting a simpler syntax:

FORALL i IN 1..rmt_runs_tab.COUNT
INSERT INTO plsql_profiler_runs
VALUES rmt_runs_tab(i);

Note that bulk operations involving record data types are perfectly legal, however i could not verify if this type of constructs is valid on Oracle versions prior to 10gR2.

ORA-00947 is also returned in trivial situations like INSERT statements containing an insufficient number of parameters in the VALUES clause or too few columns in a SELECT list as in these examples:

INSERT INTO plsql_profiler_runs
(runid, run_owner)
VALUES(1);

INSERT INTO plsql_profiler_runs
(runid, run_owner)
SELECT 1 FROM dual;

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