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 Go to next message
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 #677572 is a reply to message #677571] Thu, 26 September 2019 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post all necessary DDL for us to execute your code.

Note that you can do it in a single SQL:
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,
     pay_test_email_temp_t b
where b.empl_email = a.empl_email
/

Also "WHEN OTHERS THEN" is a bug, read WHEN OTHERS.

Re: Need help with Error: subprogram or cursor reference is out of scope [message #677573 is a reply to message #677572] Thu, 26 September 2019 11:59 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Michel, Nice to hear from you again. Thanks for your re-write and the quick response. It worked and is much cleaner your way. And thanks for letting me know about WHEN OTHERS.

I appreciate it.

I will also create the DDL for this if I need any additional help.

Thanks,
Joe
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677574 is a reply to message #677571] Thu, 26 September 2019 12:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And what's this:

          dbms_output.put_line('v_empl_email = ' || GET_THE_REST.empl_email || 
                               ' -- ' || 
                               'v_PAyrL_DIST_CODE = ' || GET_THE_REST.PAyrL_DIST_CODE );

If should be:

          dbms_output.put_line('v_empl_email = ' || v_empl_email || 
                               ' -- ' || 
                               'v_PAyrL_DIST_CODE = ' ||v_PAyrL_DIST_CODE );

SY.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
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.
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677580 is a reply to message #677577] Fri, 27 September 2019 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.
Have a look at UTL_MAIL package.
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677586 is a reply to message #677580] Fri, 27 September 2019 07:31 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Why even use the sub select. The following does the same thing

BEGIN
    FOR Pnt
        IN (SELECT    'empl_email = '
                   || Empl_email
                   || ' -- '
                   || 'PAyrL_DIST_CODE = '
                   || Payrl_dist_code    Out_line
              FROM (  SELECT A.Empl_email,
                             Pay_period,
                             A.Pay_year,
                             A.Payrl_dist_code
                        FROM Pay_test_email_temp_t A
                    ORDER BY A.Empl_email,
                             A.Pay_period,
                             A.Pay_year,
                             A.Payrl_dist_code))
    LOOP
        DBMS_OUTPUT.Put_line (Pnt.Out_line);
    END LOOP;
END;
/

empl_email = Praveen@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0033
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 = Rohit@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0034
empl_email = Rohit@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0044
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0023
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0033
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0034
empl_email = joe@GOOGLE.nyc.gov -- PAyrL_DIST_CODE = 0035
Re: Need help with Error: subprogram or cursor reference is out of scope [message #677587 is a reply to message #677586] Fri, 27 September 2019 08:48 Go to previous message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Michel, thanks for the UTL_MAIL pkg link. I will check it out.

Bill, thanks for your input as well.

Joe
Previous Topic: SQL to find Consecutive Halves
Next Topic: date usage in SQL query
Goto Forum:
  


Current Time: Fri Mar 29 07:29:49 CDT 2024