ORA-03001: unimplemented featureThis error can be returned attempting to compile a PL/SQL unit containing a FORALL statement, as follows:
create table tab_pls_435 (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.
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;
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 replaceI ignore if ORA-03001 can be returned also in different situations.
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 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!
I've got the same error in Apex too,it worked wonderful in examples but then got this error on following sql:
ReplyDeleteselect rownum IND,11 as CAT_ID , 3 AS QUERY_ID,0 AS QUERY_SUB_ID ,1130 AS QUERY_RES_ID, CAST(MULTISET(
SELECT EMPL_ID||WIN_NMBR||ORG_UNIT_ID||DIV_CD||RBG_CD FROM MV_EMP_ASGN_CRRNT WHERE ORG_UNIT_LVL_NMBR=2
MINUS
SELECT EMPL_ID||WIN_NMBR||ORG_UNIT_ID||DIV_CD||RBG_CD FROM MV_EMP_ASGN_CRRNT WHERE ORG_UNIT_LVL_NMBR=3) AS vRES_TAB
)
from dual
is it have to do smth with dual?
Hi simplyOG,
ReplyDeletei reproduced the same situation on my XE db. Apparently the parser doesn't like the set operator in this context, however the following workaround will do the trick:
select rownum IND,11 as CAT_ID , 3 AS QUERY_ID,0 AS QUERY_SUB_ID ,1130 AS QUERY_RES_ID, CAST(MULTISET(
SELECT * FROM ( -- workaround
SELECT EMPL_ID||WIN_NMBR||ORG_UNIT_ID||DIV_CD||RBG_CD as T
FROM MV_EMP_ASGN_CRRNT WHERE ORG_UNIT_LVL_NMBR=2
MINUS
SELECT EMPL_ID||WIN_NMBR||ORG_UNIT_ID||DIV_CD||RBG_CD as T
FROM MV_EMP_ASGN_CRRNT WHERE ORG_UNIT_LVL_NMBR=3
) -- closing parenthesis for workaround
) AS vRES_TAB
)
from dual
Thanks for reporting this interesting quirk!