Home » SQL & PL/SQL » SQL & PL/SQL » Getting error in procedure
icon5.gif  Getting error in procedure [message #225471] Tue, 20 March 2007 00:52 Go to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi,
I'm trying to run a procedure, but it is giving me following errors:

LINE/COL ERROR
-------- --------------------------------------------------------------
38/52 PLS-00225: subprogram or cursor 'C2' reference is out of scope
38/55 PL/SQL: ORA-00984: column not allowed here
37/6 PL/SQL: SQL Statement ignored


Procedure code is:
CREATE OR REPLACE PROCEDURE LOAD_MCK_MCDS_RESPONSE AS

V_MCK_Surveyid Number;
V_Pli2_survey_id Number;
V_question_id Number;
V_Respondent_id Number;
V_ResponseValue Number;
V_Metatag Varchar2(20);


CURSOR C1 IS
SELECT MCK_Surveyid,Pli2_survey_id
FROM T1
where Pli2_survey_id=1;

CURSOR C2 IS
SELECT c.question_id,a.empid,a.response,b.qmetatext
from pli2_t_response a,pli2_t_questionmaster b,mck_mcds_question c
where b.questionid=a.qid
and c.metatag_tx=b.qmetatext
and a.surveyid=V_Pli2_survey_id
and a.empid=1;



BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_MCK_Surveyid,V_Pli2_survey_id;
EXIT WHEN C1%NOTFOUND;

OPEN C2;
LOOP
FETCH C2 INTO V_question_id,V_Respondent_id,V_ResponseValue,V_Metatag;
EXIT WHEN C2%NOTFOUND;

INSERT INTO MCK_MCDS_RESPONSE(Survey_Id,Question_id,RESPONDENT_ID,NUMERIC_RESPONSE_VALUE)
VALUES(V_MCK_Surveyid,c2.question_id,c2.empid,c2.response);
COMMIT;
END LOOP;
CLOSE C2;

END LOOP;
CLOSE c1;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);


END LOAD_MCK_MCDS_RESPONSE;


Where is it going wrong, I don't know if I how to use parameterized cursors. Do I have to use those?
Re: Getting error in procedure [message #225479 is a reply to message #225471] Tue, 20 March 2007 01:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ouch. This is about as wrong as a cursor handling procedure can be..
First your error: you refer to c2.question_id, c2.empid and c2.response in your insert statement. You must use the names of the variables you fetched the cursor into.

Then about your procedure:

First of all, the whole thing can be rewritten as an insert into.. select ...
Second: Your exception-handler has no use. It only hides the original linenumber that raised the error. Get rid of it.
Third: Join c1 and c2 into a single cursor.
Fourth: Use implicit cursor loops: (for r_cur in c_cur loop...). This saves you the trouble of explicitly opening and closing your cursor and the check whether you reached the end etc..
Fifth: Commit when you're done. Not after each insert.

Re: Getting error in procedure [message #225516 is a reply to message #225479] Tue, 20 March 2007 03:48 Go to previous messageGo to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
Hi Frank, thank you for your inputs, I have corrected my mistakes, do you find this ok now?

CREATE OR REPLACE PROCEDURE LOAD_MCK_MCDS_RESPONSE AS

V_MCK_Surveyid Number;
V_question_id Number;
V_Respondent_id Number;
V_ResponseValue Number;


CURSOR C1 IS
SELECT t1.MCK_Surveyid,b.question_id,b.empid,b.response
FROM t1,(SELECT a.surveyid,c.question_id,a.empid,a.response,b.qmetatext
FROM pli2_t_response a,pli2_t_questionmaster b,mck_mcds_question c
WHERE b.questionid=a.qid
AND c.metatag_tx=b.qmetatext
AND a.surveyid in ('1','2','3','5','6','7','8','179','236','14','15','180','18','198','19','197','20','262','21','261','22','23','24','227','263','25', '26','28','29','199','31','32','38',
'33','39','41','42','265','264','51','52','54','200','56','266','238','57','58','59','60','61','62','194','65','66','70','71','195',' 250','72','73',
'184','217','183','76','78','79','80','81','82','83','84','85','86','87','98','99','100','248','103','104','106','121','122','124',
'125','129','268','130','225','132','133','136','271','239','138','139','142','143','147','273','240','148','274','149','150','151',' 155',
'156','157','159','161','162','163','164','165','166','167','207','168','169','170','218','172','173','190','175','208','210',
'176','211','178')) b
WHERE t1.Pli2_survey_id=b.surveyid;

BEGIN
For V_C1 in C1
LOOP
EXIT WHEN C1%NOTFOUND;

INSERT INTO MCK_MCDS_RESPONSE(Survey_Id,Question_id,RESPONDENT_ID,NUMERIC_RESPONSE_VALUE)
VALUES(V_C1.MCK_Surveyid,V_C1.question_id,V_C1.empid,V_C1.response);
END LOOP;
COMMIT;

END LOAD_MCK_MCDS_RESPONSE;


Thanks once again for your help.
Re: Getting error in procedure [message #225521 is a reply to message #225516] Tue, 20 March 2007 03:58 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no need for 'EXIT WHEN cursor%NOTFOUND'; when cursor reaches its end, it will automatically exit the loop.
Re: Getting error in procedure [message #225642 is a reply to message #225521] Tue, 20 March 2007 12:34 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have no need for your local variables anymore.
And why do you insist on using a loop instead of insert <your column_list here> select <your cursor text here>
Previous Topic: Backup before Update
Next Topic: Delete all records from all tables
Goto Forum:
  


Current Time: Wed Dec 07 12:56:48 CST 2016

Total time taken to generate the page: 0.20854 seconds