I want to bulk up I

A recent query on a private forum at work had a fellow working on a piece of code with a cursor fetching rows from a table and then a for loop inserting those rows into a table.

His Oracle Architect had decreed that this should be improved to use bulk processing. The poster had changed the cursor to do a bulk collect into a table variable which was of the type cursor%ROWTYPE.

And then he was doing a FORALL insert into another destination table and was running into the old “PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.”

This is a frustrating limitation. I wonder why Oracle decided to implement this functionality in this way. It doesn’t seem an unreasonable thing to want to do.

One obvious alternative was to fetch into multiple single dimension arrays – i.e. one array per column fetched. The downside being that some 35 columns were being selected which makes for wordy code.

Another alternative was to go through the rigmarole, usually my preferred rigmarole, of creating a sql object type, a sql table type, bulk collecting the cursor into an array and then just using sql with the TABLE and CAST functionality.

At this point, I thought I should just double check the facts. Why was it necessary to do open a CURSOR, FETCH using BULK COLLECT, close and then do a FORALL insert? Would not a simple “INSERT … SELECT” work?

Well, apparently it would work. But the poster was reluctant to rework the original beyond a certain extent. Besides, he says, “INSERT … SELECT” is not a bulk operation and that’s what his Oracle Architect had decreed preferable.

“Insert … select” is the original bulk operations. Maybe it’s more reassuring that you can see the progressive development on a piece of code through code control and see how it has developed (craziness more like). In other words, a manager could look at the code and see that the words BULK COLLECT had been added therefore job done.

Anyway, I put together a simple demo script to demonstrate some of the options to the poster.

One of the interesting things that came out of the demo was that the SELECT being used to generate the data had some interesting behaviour. This rang a distant bell. I think I remember reading about this caveat on the website where I originally read about the ability to CONNECT BY ROWNUM.

In Oracle XE:

SQL> l
  1 select rownum
  2 from dual
  3* connect by rownum < 5
SQL> /

ROWNUM
----------
  1
  2
  3
  4

In 9.2.0.6:

SQL> l
  1 select rownum
  2 from dual
  3* connect by rownum < 5
SQL> /


ROWNUM
----------
  1

But:


SQL> l
  1 select *
  2 from (
  3 select rownum
  4 from dual
  5 connect by rownum < 5
  6* )
SQL> /


ROWNUM
----------
  1
  2
  3
  4

This oddity apart, it's a cool little piece of functionality.Anyway, in case your interested, what follows is the demo in full.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: