Skip to Main Content
  • Questions
  • PLS-00436: implementation restriction error when using forall in update

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, sandeep .

Asked: May 05, 2004 - 10:16 am UTC

Last updated: May 10, 2019 - 9:01 am UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

I have a object as follows:
create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr number,fordergnr number);

and I have a a collection as:
create or replace type claims_only_collection as table of claims_only;

I have a table called forderung which has the 3 columns fordnr,glaeubigernr and fordergnr besides some other columns.

here is the table script
CREATE TABLE FORDERUNG (
GLAEUBIGERNR NUMBER NOT NULL,
FORDNR VARCHAR2 (15) NOT NULL,
FORDERGNR NUMBER NOT NULL,
MANDANTNR NUMBER,
VERTRAGSNR VARCHAR2 (15),
REFERENZNR VARCHAR2 (20),
ERLEDIGTMM NUMBER NOT NULL,
ERLEDIGTDATUM DATE,
ERLEDIGTGRUNDMM NUMBER,
SPARTE VARCHAR2 (4),
KATALOGNR NUMBER NOT NULL,
ANSPRVOMDATUM DATE,
ANSPRBISDATUM DATE,
ANSPRGRUNDMM NUMBER,
INKASSOMM VARCHAR2 (2),
ABTRETDATUM DATE,
ABTRETNR NUMBER,
GLWECHSELPARTID NUMBER,
GLWECHSELDATUM DATE,
VERBRKRGMM NUMBER NOT NULL,
VERTRAGSDATUM DATE,
EFFEKTIVZINSSATZ NUMBER (6,3),
VERTRSTREITGERMM NUMBER,
VERTRSTREITGERPLZ VARCHAR2 (6),
VERTRSTREITGERORT VARCHAR2 (31),
SONSTANSPR VARCHAR2 (174),
AUSNAHMEVORGGLSTMM NUMBER,
ZUSATZ VARCHAR2 (35),
VAAUSSCHLUSSMM NUMBER,
LEISTUNGSZUSATZ VARCHAR2 (35),
LEISTUNGSSTRASSE VARCHAR2 (35),
LEISTUNGSPLZ VARCHAR2 (6),
LEISTUNGSORT VARCHAR2 (28),
LEISTUNGSNATION VARCHAR2 (3),
WMM VARCHAR2 (3),
WMMUMSTELLDATUM DATE,
SOZIALGERICHTAVMM NUMBER,
UEBERNAHMEDATUM DATE,
EWBMETHODE NUMBER,
EWBMANUELLBETRAG NUMBER (19,7),
EWBMANUELLSATZ NUMBER (8,5),
EWBMETHODENTEXT VARCHAR2 (80),
EWBABSCHREIBMM NUMBER,
EWBERLEDIGTMM NUMBER,
EWBAENDSB NUMBER,
EWBAENDDATUM DATE,
SCHUFASTOPMM NUMBER,
STATUS VARCHAR2 (4),
STATUSVOMDATUM DATE,
STRITTIGMM NUMBER,
STRITTIGDATUM DATE,
EXTFORDNR VARCHAR2 (52),
EXTFORDERGNR NUMBER,
SBID NUMBER,
SBZUORDNUNGSMM NUMBER,
ABSCHREIBGRUNDMM_INK NUMBER,
ABSCHREIBGRUNDMM_MND NUMBER,
VERRECHNUNGMM NUMBER,
ABSCHREIBDATUM_INK DATE,
ABSCHREIBDATUM_MND DATE,
ABSCHREIBMM_INK NUMBER,
ABSCHREIBMM_MND NUMBER,
VEREGVERSIONNR NUMBER,
ERSTELLTVON NUMBER,
ERSTELLTAM DATE,
GEAENDERTVON NUMBER,
GEAENDERTAM DATE,
DLFLAG NUMBER (1),
TERFLAG NUMBER (1),
TERMODFLAG NUMBER (1),
TERCANFLAG NUMBER (1),
NEEDYCLAIMFLAG NUMBER (1),
SETTLEMENTFLAG NUMBER (1),
INDIVIDUALFLAG NUMBER (1)
);

One more column of interest which needs to be updated is sbid.
I need to update this sbid for all the records
thers lot of business logic for which i call 3-4 different procedures and get all the FORDNR,GLAEUBIGERNR,FORDERGNR in a collection and now i want to use forall while updating.

Assume i get the collection in variable l_claim_tranfer

where l_claim_tranfer is of type claims_only_collection
forall i in 1 .. l_claims_trans_actual.claim_transfer.count
update forderung
set sbid = 10
where fordnr = l_claim_tranfer(i).fordnr
and glaeubigernr = l_claim_tranfer(i).glaeubigernr
and fordergnr = l_claim_tranfer(i).fordergnr;


I get the following error:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

Pleaase help me Tom its very urgent


and Tom said...

two choices

1) use set operations instead of FORALL:


ops$tkyte@ORA9IR2> declare
2 l_claim_tranfer claims_only_collection := claims_only_collection();
3 begin
4 update forderung
5 set sbid = 10
6 where (fordnr, glaeubigernr , fordergnr ) in
7 ( select t.fordnr, t.glaeubigernr, t.fordergnr
8 from TABLE(cast(l_claim_tranfer as claims_only_collection)) t );
9 end;
10 /

PL/SQL procedure successfully completed.

see also:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3779680732446#15740265481549 <code>



2) use arrays (tables) of SCALARS -- three collections, instead of one collection of records.



Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reply to question PLS-00436: implementation restriction

sandeep gajbhiye, May 06, 2004 - 2:13 am UTC

Hi tom
I really really appreciate your prompt response.
It helped me immensely.
I Thank you for the same.
Regards
sandeep

Performance 1 vs 2

Koshal, April 06, 2006 - 11:34 am UTC

If you are inserting, which one would you prefere Option 1 or 2. If there are around 150 columns with around 200000 records. Thant needs to be inserted


Tom Kyte
April 07, 2006 - 4:02 pm UTC

what am I inserting from?

if from table1 to table2 - option 3 - just sql

But how would you do if the update had more than one column?

Srinivas Narashimalu, September 29, 2006 - 2:34 pm UTC

Hi Tom,

Can you show the sql for the option 2 of your answer?

Can you please show how you would write the for loop when using 3 scalar arrays in update? for eg.

1.update a
set name=something,
age=something,
loc=something
where some condition;

1a. Here, I can use a cursor but that would be a bad approach. How to do it in 1 update using forall?

1b. Is there any other way than creating a temporary (or staging) table and updating from that table instead of the cursor?

2.update a
set name=something
where age=something
loc=something;
2a. Here, how to use the forall loop to condition the age and loc columns to something, considering name has to be looped across different values (as in cursor)?

Finally, in the question asked by the user, he has declared

create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr
number,fordergnr number);

and

create or replace type claims_only_collection as table of claims_only;

what is the difference between object and table here, how would it be different if he had declared it as

type claims_only IS TABLE OF (fordnr varchar2(15),glaeubigernr
number,fordergnr number);

claims_only_collection claims_only;

Thanks,
Srinivas



Tom Kyte
September 30, 2006 - 7:57 am UTC

forall i in 1 .. array1.count
update a
set name=array1(i),
age=array2(i),
loc=array3(i)
where some condition;

1b - umm, that was option #1 in the above??? no temp table.

2a - does not compute, didn't get it.


3 - a sql type can be used in SQL as they are sql types. not so plsql types.

Thanks very much!

A reader, October 02, 2006 - 12:04 pm UTC

Thanks very much Tom for your reply!

What I meant with 1(b) was -

SQL is always faster than PL/SQL, so instead of creating arrays and doing it using PL/SQL wouldn't it be a good idea to create a staging table and issue the update directly using the column values of that staging table?

for eg (just to show what I mean).


declare
TYPE a_name IS TABLE OF a.name%type;
TYPE a_age IS TABLE OF a.age%type;
TYPE a_location IS TABLE OF a.location%type;

a_name_v a_name;
a_age_v a_age;
a_loc_v a_location;

BEGIN

select age,name,location bulk collect into a_age_v,a_name_v,a_loc_v from a where some condition;

forall i IN a_name_v.FIRST .. a_name_v.LAST
update b
set name=a_name_v(i),
location=a_loc_v(i)
where age=a_age_v(i);

END;

Instead of the above I could even do -

create table stage as
(select name,location,age from a where some condition);

and update b
set name=stage.name,
location=stage.location
where age=stage.age;

Also, we can directly give the CTAS query in the update statement, Please tell which would be most efficient in terms of speed -

1. using pl/sql
2. staging tables
3. directly specifying the query in the update, I think this would not be preferred as this would execute the query against the main table each time the update is run.

Tom Kyte
October 02, 2006 - 12:33 pm UTC

right, but as I said, my original answer shows how 1b is accomplished??

Thanks much!

Srinivas Narashimalu, October 02, 2006 - 4:21 pm UTC

Thanks much for the response!

-Srinivas

Future plans

Cal, November 16, 2006 - 3:09 pm UTC

I read in another post an additional option of inserting the whole record at once to get around this. Unfortunately, this doesn't work easily if your source table and target table for the FORALL statement have differing number / types of columns.

I imagine this problem outlined by the original poster is a common occurence for people doing ETL-type jobs. Do you think in future versions this implementation restriction (i.e. not being able to address individual columns in a bulk-bind table) might be removed/improved upon?

Tom Kyte
November 16, 2006 - 3:41 pm UTC

why not?

ops$tkyte%ORA10GR2> create table t1 ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> insert into t1 select rownum, rownum+1 from all_users where rownum <= 5;

5 rows created.

ops$tkyte%ORA10GR2> create table t2 ( x int, y int, z int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t2;

no rows selected

ops$tkyte%ORA10GR2> declare
  2          type array is table of t1%rowtype;
  3          l_data array;
  4  begin
  5          select * bulk collect into l_data from t1;
  6
  7          forall i in 1 .. l_data.count
  8                  insert into (select x, y from t2) values l_data(i);
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t2;

         X          Y          Z
---------- ---------- ----------
         1          2
         2          3
         3          4
         4          5
         5          6


in general, however, using COLLECTIONS as demonstrated makes it "infinitely" flexible as you can bind the collection and select whatever columns in whatever order you want.
 

Good example, but can this be made to work for an UPDATE?

A reader, July 17, 2007 - 4:19 am UTC

SYS@(hodev:MIS):273:1518> DECLARE type array IS TABLE OF t1 % rowtype;
2 l_data array;
3 BEGIN
4 SELECT *
5 bulk collect
6 INTO l_data
7 FROM t1;
8 forall i IN 1 .. l_data.COUNT INSERT
9 INTO
10 (SELECT x,
11 y
12 FROM t2)
13 VALUES l_data(i);
14 forall i IN 1 .. l_data.COUNT
15 UPDATE
16 (SELECT y,
17 x
18 FROM t2)
19 SET ROW = l_data(i)
20 WHERE x = l_data(i).x;
21 END;
22 /
WHERE x = l_data(i).x;
*
ERROR at line 20:
ORA-06550: line 20, column 14:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 20, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 20, column 14:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 15, column 4:
PL/SQL: SQL Statement ignored

SYS@(hodev:MIS):273:1518> DECLARE type array IS TABLE OF t1 % rowtype;
...
14 forall i IN 1 .. l_data.COUNT
15 UPDATE
16 (SELECT y,
17 x
18 FROM t2)
19 SET ROW = l_data(i)
20 WHERE x = i;
21 END;
22 /
WHERE x = i;
*
ERROR at line 20:
ORA-06550: line 20, column 14:
PLS-00430: FORALL iteration variable I is not allowed in this context

Tom Kyte
July 17, 2007 - 11:43 am UTC

what use would that construct be - "where x = i" - you do not have sequentially numbered rows in tables (if you do, we need to talk)

A reader, July 18, 2007 - 4:15 am UTC

You're right Tom, the second example is entirely artificial and has no real value other than to show that the reference was illegal.

But the first example is an operation a developer will be interested in, if its supported.

That is perform modifications to a collection of records, then bulk update the changes back to the corresponding table.

I know this can be done with scalars, just want to verify whether there's support for the operation using collection of records yet.
Tom Kyte
July 18, 2007 - 10:39 am UTC

you would have to

a) bulk collect out the record into your collection
b) as you iterated through them - you would populate another single attribute array with the "x" (key) value


ops$tkyte%ORA10GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(user_id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where rownum <= 2;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
USER2                                 135 01-MAY-07
USER1                                 134 01-MAY-07

ops$tkyte%ORA10GR2> declare
  2          type array1 is table of t%rowtype;
  3          type array2 is table of t.user_id%type index by binary_integer;
  4          l_data array1;
  5          l_ids  array2;
  6  begin
  7          select * bulk collect into l_data from t;
  8          for i in 1 .. l_data.count
  9          loop
 10                  l_ids(i) := l_data(i).user_id;
 11                  l_data(i).username := lower( l_data(i).username );
 12          end loop;
 13          forall i in 1 .. l_data.count
 14          update t set ROW = l_data(i) where user_id = l_ids(i);
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t where rownum <= 2;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
user2                                 135 01-MAY-07
user1                                 134 01-MAY-07


A reader, July 19, 2007 - 4:21 am UTC

Thanks Tom, that approach may provide some benefits.

A bit of a fiddle having to populate the additional array, but a good workaround until support for arrays of records is complete.

treat() - in response to original question

Stan, March 24, 2008 - 1:47 pm UTC

I ran across this solution when researching this error today. Just wanted to check it's merit with Tom.

Original:

forall i in 1 .. l_claims_trans_actual.claim_transfer.count
update forderung
set sbid = 10
where fordnr = l_claim_tranfer(i).fordnr
and glaeubigernr = l_claim_tranfer(i).glaeubigernr
and fordergnr = l_claim_tranfer(i).fordergnr;


I get the following error:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of
records

Alternate solution using the TREAT command:

forall i in 1 .. l_claims_trans_actual.claim_transfer.count
update forderung
set sbid = 10
where fordnr = treat(l_claim_tranfer(i) as claims_only).fordnr
and glaeubigernr = treat(l_claim_tranfer(i) as claims_only).glaeubigernr
and fordergnr = treat(l_claim_tranfer(i) as claims_only).fordergnr;


Tom Kyte
March 24, 2008 - 7:29 pm UTC

the conversion can be expensive

if l_claim_transfer were a proper SQL collection, you could do this without "forall", just a single update.


update forderung
set sbid = 10
where( fordnr, glaeubigernr, fordergnr )
in (select fordnr, glaeubigernr, fordergnr from TABLE( l_claim_tranfer ) )

Selected Elements in Update

Ramchandra Joshi, October 29, 2008 - 6:36 am UTC

Hi Tom,

What if I have to update just a selected number of columns in the target table?

Say my user_name is coming from a big cursor from somewhere and when I try to UPDATE the table using SET ROW clause,It eventually tries to update the ENTIRE row including primary key.

Given this scenario if I try use this method ,I would end up in updating some of the columns in the table to NULL as those are not populated in the collection.

The example best suites for SELECT * queries but how about just few elements in the collection?

Just to illustrate ,
1.)Say my TAble has 25 Columns and I'm selecting values for 10 columns from a cursor in a collection with joins on many other tables.
2.) I'm declaring a collection as TABLE%ROWTYPE and assign only 10 values from the above cursor fetched collection.
Thus the other 15 values in the collection are set to NULL by default.
3.) Now I use FORALL UPDATE and SET ROW which potentially tries to update those 15 values to NULL as well which I want to avoid.
I hope you got my point.Is there any workaround or solution provided in 10gr2 wherein I can reference only the fields that are of my interest in the update statement?
Declaring 10 ( or even more) individual arrays doesnt seem to be a good idea specially if the number of columns are more.
Any thoughts or workaround ?

Thanks in Advance
Ram.
Tom Kyte
October 29, 2008 - 8:34 am UTC

it seems rather obvious that SET ROW would be entirely and utterly inappropriate for you then?

set row does one thing, it sets the entire row to the entire record you bind in. There is no 'workaround' short of 'use a record that matches your goal'.

You don't have to define your record as table%rowtype, you can define your record as cursor%rowtype.

I'm not a fan of SET ROW at all - opens up the pathway to bugs (accidentally coded by you and me) in the developed code. It is just a bad idea.

I like being explicit.


In 11g, you could do this:

ops$tkyte%ORA11GR1> create table t
  2  as
  3  select *
  4    from all_objects
  5   where rownum <= 542;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> declare
  2
  3      cursor c is select rowid rid, object_name, object_type, owner
  4                    from t;
  5
  6      type l_array is table of C%rowtype;
  7      l_data l_array;
  8  begin
  9      open C;
 10      loop
 11          fetch C bulk collect into l_data LIMIT 100;
 12
 13          for i in 1 .. l_data.count
 14          loop
 15              null;
 16              /* .... some process that cannot be done in sql
 17                 goes here, for we all know that if it could
 18                 be done in sql without plsql, we'd be doing
 19                 it that way....
 20              */
 21          end loop;
 22
 23          forall i in 1 .. l_data.count
 24              update t set object_name = l_data(i).object_name, object_type = l_data(i).object_type, owner = l_data(i).owner where rowid = l_data(i).rid;
 25          exit when c%notfound;
 26      end loop;
 27      close c;
 28  end;
 29  /

PL/SQL procedure successfully completed.


In 10g and before, I'd do it this way:
ops$tkyte%ORA10GR2> declare
  2
  3      cursor c is select rowid rid, object_name, object_type, owner
  4                    from t;
  5
  6          type l_rowid_array is table of rowid;
  7          type l_oname_array is table of t.object_name%type;
  8          type l_otype_array is table of t.object_type%type;
  9          type l_owner_array is table of t.owner%type;
 10
 11          l_rids   l_rowid_array;
 12          l_onames l_oname_array;
 13          l_otypes l_otype_array;
 14          l_owners l_owner_array;
 15  begin
 16      open C;
 17      loop
 18          fetch C bulk collect into l_rids, l_onames, l_otypes, l_owners LIMIT 100;
 19
 20          for i in 1 .. l_rids.count
 21          loop
 22              null;
 23              /* .... some process that cannot be done in sql
 24                 goes here, for we all know that if it could
 25                 be done in sql without plsql, we'd be doing
 26                 it that way....
 27              */
 28          end loop;
 29
 30          forall i in 1 .. l_rids.count
 31              update t set object_name = l_onames(i), object_type = l_otypes(i), owner = l_owners(i) where rowid = l_rids(i);
 32          exit when c%notfound;
 33      end loop;
 34      close c;
 35  end;
 36  /

PL/SQL procedure successfully completed.


Why 11g???

Ramchandra Joshi, October 29, 2008 - 9:52 am UTC

Hi Tom,

Thank you for your quick response.

This is exactly what I needed but
i'm very sad to know that this can only be done in 11g :(
I'm running 10gr2 currently and It is really really sad to know that I would have to declare arrrays for EACH rows that are affected.
I have around 28 columns in the table and I need to update 17 of those.
I was thinking of using the %ROWTYPE attribute and get away with it.
I would certainly not like to declare arrays for each n every column maybe I would SELECT ALL the columns in the main cursor to get the EXISTING record values and use SET ROW.

If the target table is "t" ,My cursor query SELECTs ONLY object_type and object_name values from many other tables a,b,c,d,e ,Hence using CURSOR%ROWTYPE isn't useful anyway and the UPDATE SET ROW eventually NULLifies the OWNER column.

The option that I meant is use "t" in the cursor query and select OTHER column values from t which are not selected by the query(so that the values will be retained) and use SET ROW

But since it is prone to many bugs as you pointed out is that really a good thing to do Tom??
I really really dont want to declare arrays for each column !!! :(:(
Thanks again,
Ram.
Tom Kyte
October 29, 2008 - 1:36 pm UTC

.... is that really a good
thing to do Tom??
...

I sort of answered that - no, I don't think it is good - it is too prone to allowing us to make mistakes, avoid SET ROW.

Ok, here is yet another approach...

ops$tkyte%ORA10GR2> create or replace type myScalarType as object
  2  ( rid varchar2(30),
  3    oname varchar2(30),
  4    otype varchar2(30),
  5    owner varchar2(30)
  6  )
  7  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace type myArrayType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2      cursor c is select myScalarType( rowid , object_name, object_type, owner )
  3                    from t;
  4
  5      l_data myArrayType;
  6  begin
  7      open C;
  8      loop
  9          fetch C bulk collect into l_data limit 100;
 10
 11          for i in 1 .. l_data.count
 12          loop
 13              null;
 14              /* .... some process that cannot be done in sql
 15                 goes here, for we all know that if it could
 16                 be done in sql without plsql, we'd be doing
 17                 it that way....
 18              */
 19          end loop;
 20
 21          merge into t
 22          using (select * from TABLE(l_data)) x
 23          on (t.rowid = chartorowid(x.rid))
 24          when matched then update set object_name = x.oname, object_type = x.otype, owner = x.owner;
 25
 26          exit when c%notfound;
 27      end loop;
 28      close c;
 29  end;
 30  /

PL/SQL procedure successfully completed.


(and if that doesn't match precisely into the framework you are using now, I'll only be able to say "sorry" or "so, change the way you are doing it", I cannot change the way it works - I can only show what is possible...)

question about your response

Steven Kladitis, October 31, 2008 - 6:15 pm UTC

In your July 18th ,2007 followup

you wrote
select * from t where rownum <= 2;

to show that two records had been updated, Is this correct without a where clause? How do you know the first two rows would be the ones that were updated?
Tom Kyte
November 02, 2008 - 4:58 pm UTC

no, that is what I said.

I used that to show an example of the set of data that was modified - so as to not fill the screen with tons of "junk"

I showed that of all of the rows (more than two) that had been modified - here were the before and after images of two of them

I did not show that "only two, or exactly two" had been - just that here are two of them before and after the update.

restriction removed 11g?

Tom, March 26, 2010 - 9:15 am UTC

It seems the PLS-00436 implementation restriction has been lifted in 11g, we can now reference the individual fields in FORALL dml! Great news for me, although I do try to use set operations where I can (something I've learned from you Tom)

Anyway, any thoughts or comments on this? It seems that this will attempting to update very large number of records that can be "chunked" in updates of say 1000 and committed, and not worry about rollback issues for a single update statement, and still take advantage of bulk collect/forall. (although you may say run a CTAS at this point and drop/rename ;) Just curious on your thoughts.

Love this site btw, thank you for it!
Tom Kyte
March 26, 2010 - 3:33 pm UTC

... Anyway, any thoughts or comments on this? ...

I'm not 100% sure what you wanted me to comment on?

In short:

a) do it in a single sql statement if at all possible (and it often is, especially with DML ERROR LOGGING!)

b) if (a) is physically impossible, use as little plsql as possible to do it (forall would fit here nicely)

c) if (b) is not possible - drop down to a 3gl outside of the database, but this would be rare indeed.

Issue while inserting

kapil, May 07, 2019 - 12:30 pm UTC

Hi Tom,
I am facing the same issues while inserting the rows into the table.
there are 2 servers.
10g and 11.2g

Insert statement is working on the BULK COLLECT and FORALL in case of 10g to 10g insertion but slapping the same error while inserting data from 11.2g to 10g.

Obviously, I am selecting 11.2g table through DBLINK.

Can you please help?

declare
type hrc is table of varchar2(50);
fname hrc;
cursor hrf is select first_name from hr.employees@dbl_ab where rownum<=10;
begin
open hrf;
loop
fetch hrf bulk collect into fname limit 2;
exit when fname.count = 0;
forall i in 1..fname.count
insert into t1 (fi_name) values (fname(i));

end loop;
close hrf;
end;



Thank you,
Kapil
Chris Saxon
May 10, 2019 - 9:01 am UTC

Do you need bulk processing? Can you replace all that code with:

begin

  insert into t1 (fi_name)
    select first_name
    from hr.employees@dbl_ab 
    where rownum <= 10

end;
/

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library