Home » SQL & PL/SQL » SQL & PL/SQL » BulkCollect
BulkCollect [message #238243] Thu, 17 May 2007 03:13 Go to next message
pstanand
Messages: 102
Registered: February 2005
Location: Chennai,India
Senior Member
Hi see the below code. I couldn't find the solution for this. Can anyone help me. I have given the code and spool result.

SQL> DECLARE
2 CURSOR LOAN_CUR IS
3 SELECT IH.LOAN_NBR,
4 IH.BRANCH_NBR,
5 IH.MORTGAGE_INSURANCE_NAME,
6 IH.DOC_CD
7 FROM INTERFACE_HIWAY IH
8 WHERE IH.LOAN_NBR NOT IN (SELECT LOAN_NBR
9 FROM LOAN L
10 WHERE IH.LOAN_ID= L.LOAN_ID);
11
12 L_START NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
13
14 TYPE LOAN_TYPE IS TABLE OF LOAN_CUR%ROWTYPE;
15 T_LOAN_TYPE LOAN_TYPE;
16
17 BEGIN
18 OPEN LOAN_CUR;
19 LOOP
20 FETCH LOAN_CUR BULK COLLECT INTO T_LOAN_TYPE LIMIT 1000;
21 FOR i IN 1..T_LOAN_TYPE.COUNT LOOP
22 INSERT
23 INTO LOAN
24 (
25 LOAN_NBR,
26 BRANCH_NBR,
27 MORTGAGE_INSURANCE_NAME,
28 DOC_CD
29 )VALUES(
30 T_LOAN_TYPE.LOAN_NBR,
31 T_LOAN_TYPE.BRANCH_NBR,
32 T_LOAN_TYPE.MORTGAGE_INSURANCE_NAME,
33 T_LOAN_TYPE.DOC_CD
34 );
35 END LOOP;
36 EXIT WHEN LOAN_CUR%NOTFOUND;
37 END LOOP;
38 DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME-L_START)/100,2)||'SECONDS');
39 EXCEPTION
40 WHEN OTHERS THEN
41 DBMS_OUTPUT.PUT_LINE(SQLERRM);
42 END;
43
44 /
END LOOP;
*
ERROR at line 35:
ORA-06550: line 34, column 21:
PLS-00302: component 'DOC_CD' must be declared
ORA-06550: line 34, column 21:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 23, column 65530:
PL/SQL: SQL Statement ignored


SQL> spool off;

anand
Re: BulkCollect [message #238248 is a reply to message #238243] Thu, 17 May 2007 03:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you describe the LOAN table for us.
Re: BulkCollect [message #238250 is a reply to message #238243] Thu, 17 May 2007 03:35 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Seems like you've missed the index notation from the t_loan_type collection, try t_loan_type(i).doc_cd (and the other references, of course) instead of t_loan_type.doc_cd.

Also, just as a thought, this routine would be much better as an INSERT INTO .. SELECT. Much simpler.

Regards
Re: BulkCollect [message #238254 is a reply to message #238243] Thu, 17 May 2007 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't fix your code, change it and use FORALL.

Btw, read How to format your posts and apply it.

Regards
Michel
Re: BulkCollect [message #238268 is a reply to message #238243] Thu, 17 May 2007 04:45 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
As I mentioned, even better... use an INSERT INTO .. SELECT. Wink
Re: BulkCollect [message #238270 is a reply to message #238268] Thu, 17 May 2007 04:53 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! I didn't see it.
Of course, even better! Cool

Regards
Michel
Previous Topic: Analyze table
Next Topic: how cani pass reference varibles in a procedure
Goto Forum:
  


Current Time: Fri Dec 09 05:55:35 CST 2016

Total time taken to generate the page: 0.05202 seconds