Bulk Collect with Cursor 1

[jhammer98]

Hi!

I'm a newbie looking for some advice...

I'm trying to use a cursor variable to obtain an id number from a sequence and then I'm trying to use that to insert the value into a table. I was told to use 'Bulk Collect' but I'm having some troubles...does anyone have any experience with this?

Thanks in advance,

~Jake

 

[sem]

Do you need to insert record or a huge number of records at a time? In first case you may forget about BULK keyword. BTW, could you describe you troubles a bit more? Performance? Syntax? Anything else?

Regards, Dima

http://seminihin.narod.ru

 

[jhammer98]

Initially this will be for a small number of records (1000's), but the intent is to make the procedure scable. Within the next 6-8 months this will be used for 100's of thousands of records

DECLARE
CURSOR seq_cur IS
SELECT a.blah a, b.blah b, c.blah c
FROM table1 a,
table2 b,
table3 c,
WHERE a.field = b.field
and b.field = c.field
GROUP BY a.blah, b.blah, c.blah;
TYPE s_rec IS TABLE OF seq_cur%ROWTYPE
INDEX BY BINARY_INTEGER;
srec s_rec;
BEGIN
OPEN seq_cur;
LOOP
FETCH seq_cur BULK COLLECT INTO srec;
EXIT WHEN seq_cur%NOTFOUND;
INSERT INTO table4
(summary_id,
a, b, c)
VALUES
(summary_id.NEXTVAL,
srec.data_source(i),
srec.a(i),
srec.b(i),
srec.c(i);
END LOOP;
CLOSE seq_cur;
END;

This is the error I get:
"PLS-00597: expression 'SREC' in the INTO list is of wrong type"

Any help would be greatly appreciated.

~Jake

 

[jhammer98]

ooops,

please ignore the 'srec.data_source(i),' in the values portion of the insert.

 

[sem]

How about simply insert..select? I can not understand the need in intermediary.

Your own code needs FORALL clause before insert. Then, I'm not sure you may use sequence there, so I'd recommend you to fetch it from cursor itself. You should also clear srec variable after inserting.

Regards, Dima

http://seminihin.narod.ru

 

[jhammer98]

The purpose for the bulk collect is performance...we are developing 'generic scalable procedures' to eliminate the need for further coding with each new client. I've added:

FORALL i IN 1..srec.COUNT
INSERT INTO...

and still get the same error.
"PLS-00597: expression 'SREC' in the INTO list is of wrong type"

Are you saying this is due to the sequence?

 

[sem]

I ignored srec.data_source(i) as you asked

Could you post you current code? I suppose you understand that the error itself is not in FORALL i IN 1..srec.COUNT part.

BTW afaik you can not use record variables in bulk bind thus have to create a set of collections of basic types.

And the last but not the least, INSERT...SELECT produces the best performance as it uses bulk operations internally. Moreover, it may be parallelized.

Regards, Dima

http://seminihin.narod.ru

 

[jhammer98]

I can't do the insert-select because I am using a group by clause which is hosing the sequence. Here is my code...kinda. I don't want to post it exactly for fear of loosing my lob

DECLARE
CURSOR seq_cur IS
SELECT ...
FROM ...
WHERE ...
GROUP BY ...;
TYPE s_rec IS TABLE OF seq_cur%ROWTYPE
INDEX BY BINARY_INTEGER;
srec s_rec;
BEGIN
OPEN seq_cur;
LOOP
FETCH seq_cur BULK COLLECT INTO srec;
EXIT WHEN seq_cur%NOTFOUND;
FORALL i IN 1..srec.COUNT
INSERT INTO SALES_SUMMARY
(...)
VALUES
(seq_omni_sales_summary_id.NEXTVAL,
srec.a(i),
srec.b(i), ...);
END LOOP;
CLOSE seq_cur;
END;

 

[jhammer98]

I think I found the problem...


Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for HPUX: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

 

[sem]

You CAN do insert..select by using subquery:

insert into dest select s.*, seq.nextval from (select smth from src group by smth) s

Regards, Dima

http://seminihin.narod.ru