21st Century PL/SQL

April 04, 2005

Assigning Index using bulk collect

When populating pl/sql table using bulk collect, assign index value using a column from the sql statement. For example:

---

select itemID, desc
bulk collect into
l_itemID, l_plsql_table[itemID].desc
from items;

---

Right now to do this we've to loop thru the data and set the index.

Posted by Uday K at 01:08 AM | Add your thoughts (2)

April 06, 2005

Allow RETURNING for INSERT....SELECT

Allow RETURNING for INSERT....SELECT and similar SQL statements:

FORALL ..
INSERT INTO ... SELECT ... FROM ... WHERE ...
RETURNING ... BULK COLLECT INTO ....

Posted by gary myers at 03:33 AM | Add your thoughts (2)

Incremental bulk processing

There should be a provision to incrementally bulk collect the query columns in a select statement into a plsql collection.

---

for e.g.

select * bulk collect into emp_tab
from emp increment 100;

emp_tab should be populated with emp in increments of 100.

---

Currently there is a limit clause. But you have to keep two plsql tables, one to bulk collect and another in which the results are appended. If the increment clause is there, then this can be avoided, and also coding will be more elegant. A more thorough explanation follows.

Consider a table with nearly 5 million rows. In case for some purpose you need to bulk collect 50000 rows, in to a plsql table for some processing.

A common solution for this is:

Declare
tbl_emp is table of emp%type index by binary_integer;
emp_tbl tbl_emp
Begin
Select * bulk collect into emp_tbl from emp;
< Do the required processing with emp_tbl>
End;

Now, it is not advisable to bulk collect nearly 50000 rows at one stretch, as it might cause the server to hang. So the limit clause steps in here. The above solution can be rewritten as follows

Declare
tbl_emp is table of emp%type index by binary_integer;
emp_tbl1 tbl_emp;
emp_tbl2 tbl_emp;
cursor c is select * from emp;
Begin
open c;
loop
fetch c bulk collect into emp_tbl limit 200;
/** append the contents of emp_tbl2 to emp_tbl1 */
for i in 1..emp_tbl1.count
loop
emp_tbl2.count +1 := emp_tbl1;
end loop;
exit when c%notfound;
end loop;

< Do the required processing with emp_tbl2>
End;

in order to populate the plsql table, we need to temporarily have one plsql table , which will fetch 200 rows everytime and then we need to consolidate everything in the final table. Also, so more coding is required in this method. This is what I want to avoid.

If we have a syntax to incrementally bulk collect as per the limit (say 200), then all these things will be done under the covers for us. The obvious advantages are reduction in coding and hences reduction in errors and finally it obviates the need to maintain the two plsql tables. The syntax could be similar to this

--> Select * bulk collect into emp_tbl from emp increment 200;

Emp_tbl will be populated in batches of 200. Since it is an implicit cursor, the lines of coding is also significantly reduced.
 

Posted by viswanathan venkat ramanan at 08:44 AM | Add your thoughts (0)

April 12, 2005

Add ERROR_MESSAGE to SQL%BULK_EXCEPTIONS

Right now this pseudo-collection has ERROR_INDEX and ERROR_CODE fields, which is great. The problem is that we lose the specific error information, which is held in the error message and not the code. So if you save this value as well when the error occurs during FORALL processing, we will be able to log and/or recover more effectively.

Posted by sf at 07:44 AM | Add your thoughts (0)

June 30, 2005

Returning * from DELETE

You can now write:

SELECT * BULK COLLECT INTO vLst FROM MyTable;

It is strange that you cannot write:

DELETE FROM MyTable RETURNING * BULK COLLECT INTO vLst;

It seems that RETURNING * is not supported. Instead, I have to write RETURNING col1, col2...

Posted by John Ng at 09:13 PM | Add your thoughts (0)