Home » SQL & PL/SQL » SQL & PL/SQL » Error PLS-00201:SYS_REFCURSOR must be declared
Error PLS-00201:SYS_REFCURSOR must be declared [message #223165] Wed, 07 March 2007 19:11 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have this procedure

CREATE OR REPLACE PROCEDURE p (p_rc OUT SYS_REFCURSOR) AS
l_sql VARCHAR2(32700) := 'SELECT TRUNC(es.LIC_NO) LIC_NO';
l_rc SYS_REFCURSOR;
BEGIN
FOR c IN (SELECT DISTINCT ES.LIC_NO FROM MSS_V_CUST_RETENTN es)
LOOP
l_sql := l_sql
|| ',SUM(DECODE(es.LIC_NO,'''
|| REPLACE(c.LIC_NO,'''','''''')
|| ''',es.RO_NO,'')) "'
|| UPPER(SUBSTR(REPLACE(c.LIC_NO,'''',''''''),1,30))
|| '" ';
END LOOP;
l_sql := l_sql
|| 'FROM MSS_V_CUST_RETENTN es GROUP BY TRUNC(es.LIC_NO)';
OPEN p_rc FOR l_sql;
END p;

When I run the procedure it gives me the error SYS_REFCURSOR must be declared.

Can anyone help me?
Re: Error PLS-00201:SYS_REFCURSOR must be declared [message #223166 is a reply to message #223165] Wed, 07 March 2007 19:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ERROR? What Error? I do not see any error.
Re: Error PLS-00201:SYS_REFCURSOR must be declared [message #223705 is a reply to message #223165] Sat, 10 March 2007 09:55 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Are you sure you get the error at runtime and not at compilation time? A more likely scenario is that you get it when you try to compile it against an ancient version of Oracle (pre-9i) that did not have SYS_REFCURSOR. I'm guessing though...
Previous Topic: Peformance when using rowlevel trigger and column trigger
Next Topic: Disadvantages of Bulk Collect
Goto Forum:
  


Current Time: Fri Dec 09 08:00:16 CST 2016

Total time taken to generate the page: 0.11395 seconds