Bulk collect issue [message #597434] |
Fri, 04 October 2013 11:46 |
|
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 #597439 is a reply to message #597434] |
Fri, 04 October 2013 11:57 |
|
Michel Cadot
Messages: 68641 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Mon, 16 September 2013 17:191/ 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 |
|
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 #597467 is a reply to message #597434] |
Fri, 04 October 2013 15:19 |
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 #597474 is a reply to message #597443] |
Fri, 04 October 2013 17:16 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
BlackSwan wrote on Fri, 04 October 2013 09:56do 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:10If 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
|
|
|