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.

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.

PLS-00430: FORALL iteration variable XYZ is not allowed in this context

As i wrote in my previous posting, it was my intention to explain why i used a collection for storing the indices of the messages collected from dbms_output buffer.

Let's take my FORALL statement from the function source code and see where is the problem:

forall i in 1..messages_tab.count
execute immediate
'insert into '|| p_owner||'.'||p_table
||'('||p_txt_col||',' || p_cnt_col ||') values(:1, :2)'
using messages_tab(i), subscripts_tab(i);

In a perfect world i could have written (without using an additional collection):

forall i in 1..messages_tab.count
execute immediate
'insert into '|| p_owner||'.'||p_table
||'('||p_txt_col||',' || p_cnt_col ||') values(:1, :2)'
using messages_tab(i), i;

However if you try to compile a program containing this FORALL statement, you'll get:

PLS-00430: FORALL iteration variable I is not allowed in this context

This is why i decided to use the additional collection called subscripts_tab.

This collection contains the subscripts of the elements themselves plus an offset corresponding to the size of the "chunk" of dbms_ouput messages that i retrieve every time (100 at a time if you look at the value maxbuf), so i populate the collection with the help of a bizarre hierarchical SQL statement courtesy of Tom Kyte using BULK COLLECT:

select level + k
  bulk collect into subscripts_tab
  from dual
connect by level <= maxbuf;

And this should end the dbms_output tormentone, a nice italian word meaning "some gossip, tune or jingle that keeps buzzing in your head and you can't get rid of".

A practical example of using global temporary tables within Apex: displaying dbms_output messages

It's late so i am not going to make it longer than necessary.

Some days ago i read an interesting question in the OTN forum and i threw my 50 cent advice (well, honestly it may have been worth 60 or 70 cents as i did some homework before shooting...).

In short, a user was asking for a method for display the messages sent to the DBMS_OUTPUT buffer.

This question eventually aroused my fantasy, so i wrote a generic function for storing dbms_output messages into a generic table (a table in the same schema or in another one, provided the user has INSERT privilege).
Having used up all my fantasy in the development, i decided to call the function STORE_DBMS_OUTPUT.

In my test apex application the staging table is defined as follows:

create global temporary table
staging_table(
id number(5,0),
text varchar2(255)
) on commit delete rows
/

The source code of the function can be downloaded from Yocoya.com.

The function takes 4 parameters and returns a number:

p_owner owner of the staging table, default to current user.
p_table name of the table, mandatory
p_txt_col name of the column for storing messages, typically VARCHAR2(255)
p_cnt_col optional name of the column for storing message sequence number

The last parameter may be omitted if you already have a before-insert trigger on the table where you stuff an oracle sequence.

The function retrieves all the content in the buffer and the value it returns is the number of messages it found.

Although i wrote the function for using within an Apex page, it's completely independent from it and it can be used in different situations.
The table where the messages are going to be stored doesn't necessarily have to be a Global Temporary Table (GTT), although in that case you must devise a method for keeping it clean without interfering with other sessions and/or users.

Users who are interested in looking at a working example of dynamic SQL in combination with FORALL and bulk binds may be interested in this function too.
Note also the n-tier usage of DUAL i talked about some time ago that i use here for initializing a pl/sql table containing the subscripts, i'll probably write something in another posting on this subject.

In case you are interested in using this function inside apex, here is how i did it (please note that this techniques are still under testing).

before header processes
step 10: truncate staging table (just in case) and call the desired procedure returning dbms_output messages (conditional on request=OUTPUT)
step 20: get the dbms_output and store it in a global temporary table (conditional on request=OUTPUT), as follows:

declare
n integer;
begin
n := STORE_DBMS_OUTPUT(
p_owner => :OWNER,
p_table => 'STAGING_TABLE',
p_txt_col => 'TEXT',
p_cnt_col => 'ID');
end;

regions
sequence 10: some region with a button branching to the same page, setting request = OUTPUT
sequence 20: report region on staging table (conditional on request=OUTPUT)
sequence 30: PL/SQL type region truncating the table (conditional on request=OUTPUT), may be omitted altogether if GTT is defined as on commit delete rows.

As you see here i have up to two truncates statements. This is just to avoid having an idle session holding some temporary segment as i related in my previous posting.
If the GTT is defined as on commit delete rows, you can probably omit both truncates, i just prefer to leave a clean table after using it.

That's it.