Hello, you are not logged in.  Login or sign up
Community >> Blogs
Search Toad World Search

Blogs
Toad and Database Commentaries

 Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies.

Do you have a topic that you'd like discussed?  We'd love to hear from you.  Send us your idea for a blog topic.

Undocumented But Supported Feature In Oracle11g: Reference Fields Of Records In A FORALL Statement!
 
Location: Blogs Steven Feuerstein's Blog    
 StevenFeuersteinTW 8/22/2007 7:41 AM

Kudos to Alex Nuijten for bringing this gem to light, he wrote in his latest AMIS blog entry that:

"If you are a regular user of the FORALL statement, you are probably also familiar with this message:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

While I was playing around on our Oracle 11g database, I found that this restriction was lifted. How cool is that?"

You are right, Alex, that is very cool. When I read it, I thought to myself, "very cool, but is it supported? Is it really going to be in the final, production version of Oracle11g?" 

Who better to know than Bryn Llewellyn, Oracle PL/SQL Product Manager? So I asked him and here is his reply:

Regarding 

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records or objects
Cause: table(bulk_index).field is not supported at run-time.
Action: Use FOR loop with instead of the FORALL DML statement (MERGE/INSERT/DELETE/UPDATE).

We have made improvements in this area in response to ER #5186976. Sadly, the documentation was not edited to reflect this, so you'll still find this:

<<
You cannot refer to individual record fields within DML statements invoked by a FORALL statement. Instead, you can specify the entire record with the SET ROW clause in an UPDATE statement, or the VALUES clause in an INSERT statement.
>>

in the 11. PL/SQL Language Reference in Chapter13. PL/SQL Language Elements, in the section titled "FORALL Statement". I just filed doc bug #6355690 to complain about this.
______________________________________________________________________

The following illustrates the changes.

Create a user with tables Emp (CREATE TABLE AS SELECT from Scott.Emp) and Newemp (CREATE TABLE AS SELECT from Emp where 0=1). Use a block with this shape:

declare
  type empcol_t is table of emp%rowtype index by pls_integer;
  empcol empcol_t;
begin
  select * bulk collect into empcol from emp
    where hiredate < to_date('01-JAN-1982', 'DD-MON-YYYY');

  for i in empcol.first..empcol.last loop
    empcol(i).sal := empcol(i).sal * 1.1;
  end loop;

  < test >
end;

Use Case #1: Update rows where some key value (empno in this case) matches the key value in the record bind into the bulk DML operation.

  forall i in empcol.first..empcol.last
    update emp set row = empcol(i)
      where empno = empcol(i).empno;

This failed to compile in 10.2 with PLS-00436; it compiles and runs fine in 11.1.

Use Case #3: Update a subset of columns in the SET clause with values from the record in bind into the bulk DML operation.


  forall i in empcol.first..empcol.last
    update emp set (sal) = empcol(i).sal
      where empno = empcol(i).empno;

This failed to compile in 10.2 with PLS-00436; it compiles and runs fine in 11.1.

Use Case #4: Variant on Use Case #3, but with insert statement.

  forall i in empcol.first..empcol.last
    insert into newemp (empno, ename, sal)
      values (empcol(i).empno, empcol(i).ename, empcol(i).sal);

This failed to compile in 10.2 with PLS-00436; it compiles and runs fine in 11.1.

NOTE: Use Case #2 still fails to compile. Allow attribute reference in a returning clause.


  forall i in empcol.first..empcol.last
    update emp set row = empcol(i)
      where empno = empcol(i).empno
      returning sal bulk collect into empcol(i).sal;

This failed to compile in 10.2 with PLS-00436; it fails to compile in 11.1 with this error:

PLS-00437: FORALL bulk index cannot be used in RETURNING clause.

Bryn concludes with this exciting news:

You can regard this as the official reply from Oracle's PL/SQL Product Manager: the improvements brought by ER #5186976 are supported from 11.1.0.6 onwards. 

So there you have it, folks! You can reference fields of records in collections that are bound into the DML statement of a FORALL statement – in Oracle11g.

Copyright ©2007 Quest Software Inc.
Permalink |  Trackback

Comments (2)   Add Comment
By alexnuijten on 8/30/2007 7:15 AM
Are you sure it's "Bryn.Llewellyn@quest.com"? Or is that just wishful thinking? ;-)

By StevenFeuersteinTW on 9/17/2007 3:46 PM
Yikes! OK, fixed that....

SF


Comment:
Add Comment   Cancel 
Search Blog Entries
 
Blogger and Topic List
 

 

All Recent Entries
 

 

Johannes Ahrends
Unicode

Steven Feuerstein
Oracle PL/SQL

Daniel Norwood
Toad for Data Analysis
John Pocknell
Toad for Oracle
Bert Scalzo
Toad for Oracle, Data Modeling, Benchmarking
Jeff Smith
Toad product family
Richard To
SQL Optimization
Jim Wankowski
DB2 - LUW and z/OS
John Weathington
Compliance
Doug Williams
Database Musings
  Henrik "Mauritz" Johnson
Toad Tips & Tricks on the "other" Toads
  Toad World Editor
Toad World issues

  Toad Data Modeler Opens in a new window
Data Modeling
 

Copyright 2008 by Quest Software  | Terms Of Use | Privacy Statement | Contact Us