Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect issue (oracle11.2.0 Linux Server(don't know version))
Bulk collect issue [message #597434] Fri, 04 October 2013 11:46 Go to next message
MitchM
Messages: 9
Registered: August 2013
Junior Member
I am having an issue with a bulk collect I wrote. I have written many before but I am running into an issue

CREATE OR REPLACE PROCEDURE update_archive
AS
CURSOR cur_1
IS
SELECT ID,
SSN,
IDENTIFR,
FULL_NAME,
FIRST_MIDDLE_NAME,
LAST_NAME,
DOB,
ADDRESS_1,
ADDRESS_2,
CITY,
STATE,
ZIP_
FROM archive_process;

TYPE ct_person IS TABLE OF cur_1%ROWTYPE
INDEX BY BINARY_INTEGER;
t_person ct_person;
BEGIN
OPEN cur_1;

LOOP
FETCH cur_1
BULK COLLECT INTO t_person LIMIT 100;


FORALL ix IN t_person.first .. t_person.lAST
UPDATE ARCHIVEA
SET SSN = ssn(ix),
IDENTIFR = IDENTIFR(ix),
FULL_NAME = full_name(ix),
FIRST_MIDDLE_NAME = first_middle_name(ix),
LAST_NAME = last_name(ix),
DOB = dob(ix),
ADDRESS_1 = address_1(ix),
ADDRESS_2 = address_2(ix),
CITY = city(ix),
STATE = state(ix),
ZIP_ = zip_(ix)
WHERE ID =ID(ix) ;

EXIT WHEN cur_1%NOTFOUND;
END LOOP;
CLOSE cur_1;
END;
/

It keeps telling me that ID is invalid.I've tried this a few different ways including basing the type on the archive process table but no go.

Note: There may be a small syntax errors as I had to strip out properitary data names so that might not be the cause. If you see anything ilke that let me know but please look at the code structure itself
Re: Bulk collect issue [message #597438 is a reply to message #597434] Fri, 04 October 2013 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
do not use PL/SQL when the same can be done in plain SQL.

>WHERE ID =ID(ix) ;
how can above ever be valid syntax?

Re: Bulk collect issue [message #597439 is a reply to message #597434] Fri, 04 October 2013 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 16 September 2013 17:19
1/ As already told you in your previous topic:

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

2/ Barbara help you a lot in your previous topic, it should be fair you thank her or at leadt feedback.

...


MitchM wrote on Mon, 16 September 2013 19:52 in "Really don't care if I get banned for saying this"
Michel Cadot-

Why you have the urge to insult anyone who posts something you don't like? I don't mean correct them, but insult them. I have seen your answers questions that go beyond "read the documentation", calling people idiotsor after someone answers someone elses question you chime in with a snarky remark.

I suggest you cut it out before people on the boards realize what a jerk you are (if they haven't already).



[Updated on: Fri, 04 October 2013 13:57]

Report message to a moderator

Re: Bulk collect issue [message #597443 is a reply to message #597438] Fri, 04 October 2013 12:10 Go to previous messageGo to next message
MitchM
Messages: 9
Registered: August 2013
Junior Member
If I could do it in plain SQL I would- we are talking about 220,000,000 rows.

I am also not sure what you mean by
WHERE ID =ID(ix) being invalid;
I am saying this update should be on a row where the id matches the id from the collection. I took this directly from Feuerstein. I've done bulk collects before but I am missing something here.

[Updated on: Fri, 04 October 2013 12:14]

Report message to a moderator

Re: Bulk collect issue [message #597450 is a reply to message #597443] Fri, 04 October 2013 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If I could do it in plain SQL I would- we are talking about 220,000,000 rows.
SQL does not have any limit on the number of rows.

Re: Bulk collect issue [message #597451 is a reply to message #597450] Fri, 04 October 2013 12:32 Go to previous messageGo to next message
DrabJay
Messages: 32
Registered: May 2013
Member
You bulk collect into a table of records then update from some undeclared tables of scalars.
Re: Bulk collect issue [message #597457 is a reply to message #597451] Fri, 04 October 2013 12:50 Go to previous messageGo to next message
MitchM
Messages: 9
Registered: August 2013
Junior Member
>If I could do it in plain SQL I would- we are talking about 220,000,000 rows.
SQL does not have any limit on the number of rows.

No kidding Blackswan.I know you can do it in plain SQL. Would you like to calculate how long it would take to update 200,000 rows against a table with 220,000,000 rows? Both tables have indexes on ID and I ran DBMS_Stats before starting. After 24 hours I killed it.

DrabJay- I never did that before but I'll give it a whirl. Thanks for a constructive answer to my question.

Re: Bulk collect issue [message #597466 is a reply to message #597457] Fri, 04 October 2013 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I know you can do it in plain SQL. Would you like to calculate how long it would take to update 200,000 rows against a table with 220,000,000 rows?

please post actual SQL for above along with EXPLAIN PLAN.
Re: Bulk collect issue [message #597467 is a reply to message #597434] Fri, 04 October 2013 15:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
MitchM wrote on Fri, 04 October 2013 22:16

FORALL ix IN t_person.first .. t_person.lAST
UPDATE ARCHIVEA
SET SSN = ssn(ix),
IDENTIFR = IDENTIFR(ix),
FULL_NAME = full_name(ix),
FIRST_MIDDLE_NAME = first_middle_name(ix),
LAST_NAME = last_name(ix),
DOB = dob(ix),
ADDRESS_1 = address_1(ix),
ADDRESS_2 = address_2(ix),
CITY = city(ix),
STATE = state(ix),
ZIP_ = zip_(ix)
WHERE ID =ID(ix) ;
/

It keeps telling me that ID is invalid.


This is syntactically wrong.

Modify it to -

forall ix IN t_person.first .. t_person.last 
      UPDATE archivea 
      SET    ssn = t_person(ix).ssn, 
             identifr = t_person(ix).identifr , 
             full_name = t_person(ix).full_name, 
             first_middle_name = t_person(ix).first_middle_name , 
             last_name = t_person(ix).last_name , 
             dob = t_person(ix).dob , 
             address_1 = t_person(ix).address_1 , 
             address_2 = t_person(ix).address_2 , 
             city = t_person(ix).city , 
             state = t_person(ix).state , 
             zip_ = t_person(ix).zip_ 
      WHERE  id = t_person(ix).id;


You need to understand how to refer a collection element.

Regards,
Lalit

[Updated on: Fri, 04 October 2013 15:20]

Report message to a moderator

Re: Bulk collect issue [message #597470 is a reply to message #597467] Fri, 04 October 2013 15:38 Go to previous messageGo to next message
MitchM
Messages: 9
Registered: August 2013
Junior Member
Doh! Don't know what I was thinking- I've done this before but didn't have access to my old procs and made the mistake.

Thanks Lalit - easy answer and it's fixed.
Re: Bulk collect issue [message #597472 is a reply to message #597470] Fri, 04 October 2013 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You should NEVER use PL/SQL, when the same can be done in plain SQL.
Almost without exception the PL/SQL will consume more resources & run slower than the plain SQL.
Re: Bulk collect issue [message #597474 is a reply to message #597443] Fri, 04 October 2013 17:16 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
BlackSwan wrote on Fri, 04 October 2013 09:56
do not use PL/SQL when the same can be done in plain SQL.

>WHERE ID =ID(ix) ;
how can above ever be valid syntax?




MitchM wrote on Fri, 04 October 2013 10:10
If I could do it in plain SQL I would- we are talking about 220,000,000 rows.

I am also not sure what you mean by
WHERE ID =ID(ix) being invalid;
I am saying this update should be on a row where the id matches the id from the collection. I took this directly from Feuerstein. I've done bulk collects before but I am missing something here.


please post reproducible test case that includes
WHERE ID =ID(ix)
above WHERE clause above as part of working & valid procedure
Previous Topic: Is it possible to fetch file path from a folder through PL/SQL
Next Topic: How to retrieve first letter of every word from string
Goto Forum:
  


Current Time: Thu Apr 18 19:19:07 CDT 2024