Home » SQL & PL/SQL » SQL & PL/SQL » Need help with Error: subprogram or cursor reference is out of scope (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 )
Need help with Error: subprogram or cursor reference is out of scope [message #677571] |
Thu, 26 September 2019 11:26 |
rappaj
Messages: 147 Registered: June 2010 Location: NYC
|
Senior Member |
|
|
Hi All,
Wow........I have not been in here in so many years and I see some familiar names still helping out the Oracle community. Very nice. I could use some help today too.
I'm trying to write a double cursor procedure. Grab a distinct email_address from the first cursor, then additional data from the 2nd cursor for that email_address and display it on my screen.
However, I think I may have something wrong with the dbms_output.
Can someone tell me if what I have is ok, or incorrect?
Thank you.
I get this error:
PROCEDURE PAY_SEND_EMAIL_PROC compiled
Errors: check compiler log
29/66 PLS-00225: subprogram or cursor 'GET_THE_REST' reference is out of scope
29/11 PL/SQL: Statement ignored
CREATE OR REPLACE PROCEDURE PAY_SEND_EMAIL_PROC is
v_empl_email varchar2(40);
v_PAY_PERIOD varchar2(2);
v_PAY_YEAR varchar2(4);
v_PAyrL_DIST_CODE varchar2(4);
/* First cursor */
CURSOR get_email IS
SELECT distinct a.empl_email
FROM pay_test_email_temp_t a;
/* Second cursor */
CURSOR get_the_rest IS
SELECT b.PAY_PERIOD, b.PAY_YEAR, b.PAyrL_DIST_CODE, b.empl_email
FROM pay_test_email_temp_t b
WHERE b.empl_email = v_empl_email;
BEGIN
-- Open first cursor
OPEN get_email;
LOOP
FETCH get_email INTO v_empl_email;
-- Open second cursor
OPEN get_the_Rest;
LOOP
FETCH get_the_Rest INTO v_PAY_PERIOD, v_PAY_YEAR, v_PAyrL_DIST_CODE, v_empl_email;
dbms_output.put_line('v_empl_email = ' || GET_THE_REST.empl_email ||
' -- ' ||
'v_PAyrL_DIST_CODE = ' || GET_THE_REST.PAyrL_DIST_CODE );
END LOOP;
CLOSE get_the_rest;
END LOOP;
CLOSE get_email;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end PAY_SEND_EMAIL_PROC;
/
|
|
|
|
|
|
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677575 is a reply to message #677574] |
Thu, 26 September 2019 14:20 |
rappaj
Messages: 147 Registered: June 2010 Location: NYC
|
Senior Member |
|
|
Thanks for fixing the dbms_output, that works now too. The cursor also works and returns my expected 10 rows. However, the dbms_output displays all 10, and then continuously displays the last rec until buffer runs out.
So I created the DDL for you to test with to see what I mean. WARNING. But if you run it, most likely you will have to kill your session. The DBMS_OUTPUT reads the last record continuously...
It looks ok to me. OPEN, loop, fetch and print. Close, then read the next rec...etc...
Thank you.
CREATE TABLE PAY_TEST_EMAIL_TEMP_T
(
PAY_YEAR VARCHAR2(4),
PAY_PERIOD NUMBER,
PAYRL_DIST_CODE VARCHAR2(7),
EMPL_EMAIL VARCHAR2(50)
);
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',9, '0023', 'joe@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',9, '0033', 'joe@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',9, '0033', 'Praveen@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',10, '0034', 'Rohit@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',10, '0034', 'joe@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',13, '0035', 'joe@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',13, '0035', 'Praveen@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',9, '0037', 'Praveen@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',15, '0044', 'Rohit@GOOGLE.nyc.gov');
insert into PAY_TEST_EMAIL_TEMP_T VALUES ('2019',15, '0044', 'Praveen@GOOGLE.nyc.gov');
select * from PAY_TEST_EMAIL_TEMP_T;
drop table PAY_TEST_EMAIL_TEMP_T;
CREATE OR REPLACE PROCEDURE PAY_SEND_EMAIL_PROC is
v_empl_email varchar2(40);
v_PAY_PERIOD varchar2(2);
v_PAY_YEAR number;
v_PAyrL_DIST_CODE varchar2(4);
/* cursor */
CURSOR get_email IS
select a.empl_email,
b.PAY_PERIOD, b.PAY_YEAR, b.PAyrL_DIST_CODE--, b.empl_email
from (SELECT distinct a.empl_email FROM pay_test_email_temp_t a) a,
pay_test_email_temp_t b
where b.empl_email = a.empl_email
order by 1;
BEGIN
-- Open cursor
OPEN get_email;
LOOP
FETCH get_email INTO v_empl_email, v_PAY_PERIOD, v_PAY_YEAR, v_PAyrL_DIST_CODE;
dbms_output.put_line('v_empl_email = ' || v_empl_email ||
' -- ' ||
'v_PAyrL_DIST_CODE = ' ||v_PAyrL_DIST_CODE );
END LOOP;
CLOSE get_email;
end PAY_SEND_EMAIL_PROC;
/
execute PAY_SEND_EMAIL_PROC
|
|
|
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677576 is a reply to message #677575] |
Thu, 26 September 2019 14:32 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
An EXIT statement is missing in the LOOP after the FETCH;
EXIT WHEN get_email%NOTFOUND;
To simplify you can use a cursor loop and let PL/SQL manage itself the cursor:
SQL> BEGIN
2 FOR rec IN (
3 select a.empl_email,
4 b.PAY_PERIOD, b.PAY_YEAR, b.PAyrL_DIST_CODE--, b.empl_email
5 from (SELECT distinct a.empl_email FROM pay_test_email_temp_t a) a,
6 pay_test_email_temp_t b
7 where b.empl_email = a.empl_email
8 order by 1
9 ) LOOP
10 dbms_output.put_line('empl_email = ' || rec.empl_email ||
11 ' -- ' ||
12 'PAyrL_DIST_CODE = ' ||rec.PAyrL_DIST_CODE );
13 END LOOP;
14 END;
15 /
empl_email = Praveen@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0037
empl_email = Praveen@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0035
empl_email = Praveen@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0044
empl_email = Praveen@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0033
empl_email = Rohit@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0044
empl_email = Rohit@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0034
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0035
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0033
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0023
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0034
PL/SQL procedure successfully completed.
[Updated on: Thu, 26 September 2019 14:33] Report message to a moderator
|
|
|
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677577 is a reply to message #677576] |
Thu, 26 September 2019 15:05 |
rappaj
Messages: 147 Registered: June 2010 Location: NYC
|
Senior Member |
|
|
Michel,
Thanks a lot. Both ways work great now and both ways get the results I need. This is just the beginning for me. I am looking at a SEND_EMAIL package here to actually email all these people that gets returned a message. I may have to use the long version with the variables as parameters to pass into the To: From: etc...
But that is something I will be looking into tomorrow. You were a big help with this today.
Thank you again.
Have a good night.
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 16 09:31:02 CDT 2024
|