Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql cursors
pl/sql cursors [message #274885] Wed, 17 October 2007 10:36 Go to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
The goal is to have a stored procedure that will be called by a .net app it should return a dataset/datable. From reading on these forums, reference cursors are the way to achieve this.

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

code so far:

create or replace PROCEDURE jrtest(v_repaircur OUT refcur,
                          v_ordernum IN VARCHAR2,  
                         v_errorcode OUT number )
IS
  BEGIN 
     OPEN v_repaircur FOR 
       SELECT ordernum, Reptech, rephours,model,Serialnum1
                     from repair_detail 
       where ordernum = v_ordernum;  
  END;


I get this error:
SQL> show err
Errors for PROCEDURE JRTEST:

LINE/COL ERROR
-------- -----------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/34     PLS-00201: identifier 'REFCUR' must be declared



I've tried a few ideas on this forum w/ no success. Any ideas? TIA-
Re: pl/sql cursors [message #274888 is a reply to message #274885] Wed, 17 October 2007 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com has many fine coding example
Re: pl/sql cursors [message #274889 is a reply to message #274885] Wed, 17 October 2007 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is "refcur"?

Regards
Michel
Re: pl/sql cursors [message #274900 is a reply to message #274889] Wed, 17 October 2007 11:13 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
Thanks for the replies and the link..
refcur was just some text I had put back after trying some other things...

I'm still not sure how I would use/declare the ref cursor, I've been trying to find a way to get rid of this error. Would it be helpful to post all the code that didn't work for me as well?

If you could you post a sample to get me started that would be great.

[Updated on: Wed, 17 October 2007 11:14]

Report message to a moderator

Re: pl/sql cursors [message #274931 is a reply to message #274900] Wed, 17 October 2007 14:09 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example based on Scott's schema. I hope you'll manage to figure out what and how to do it in your case.
SQL> CREATE OR REPLACE PROCEDURE Jrtest (
  2     v_repaircur   OUT      sys_refcursor,
  3     v_deptno      IN       VARCHAR2
  4  )
  5  IS
  6  BEGIN
  7     OPEN v_repaircur FOR
  8        SELECT ename, sal
  9          FROM EMP
 10         WHERE deptno = v_deptno;
 11  END;
 12  /

Procedure created.

SQL> variable emps refcursor;
SQL>
SQL> exec jrtest(:emps, 20);

PL/SQL procedure successfully completed.

SQL> print emps;

ENAME             SAL
---------- ----------
SMITH             800
JONES            2975
SCOTT            3000
ADAMS            1100
FORD             3000

SQL>
Re: pl/sql cursors [message #274936 is a reply to message #274931] Wed, 17 October 2007 14:43 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
Thank you LittleFoot, this is exactly what I was looking for! Unfortunately, I'm still getting the same error. Do you suspect that this is a permission error or a version error then?

SQL> select * from v$version;

BANNER
-------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production


SQL> CREATE OR REPLACE PROCEDURE Jrtest (
  2        v_repaircur   OUT      sys_refcursor,
  3        v_deptno      IN       VARCHAR2
  4     )
  5     IS
  6     BEGIN
  7        OPEN v_repaircur FOR
  8           SELECT ename, sal
  9            FROM EMP
 10            WHERE deptno = v_deptno;
 11     END;
 12  /

Warning: Procedure created with compilation errors.

SQL> sho err;
Errors for PROCEDURE JRTEST:

LINE/COL ERROR
-------- ---------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/30     PLS-00201: identifier 'SYS_REFCURSOR' must be declared
Re: pl/sql cursors [message #274937 is a reply to message #274936] Wed, 17 October 2007 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is why I asked what is "refcur", I wanted you explain what you mean with this.
You have to create a type for a "ref cursor".

Regards
Michel
Re: pl/sql cursors [message #274939 is a reply to message #274937] Wed, 17 October 2007 15:28 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
Declare the type where?
Tried:
var sys_refcursor REFCURSOR

Also, can't get it to compile anywhere in the pl/sql block. Do I need to create a package and declare it there? What's the syntax? This is day 1 for me and pl/sql.

I understand the 'teach a man to fish...' methodology, but I don't even know what to google at this point. Thanks again-
Re: pl/sql cursors [message #274942 is a reply to message #274939] Wed, 17 October 2007 16:00 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
sys_refcursor started in version 9i.
Re: pl/sql cursors [message #274947 is a reply to message #274885] Wed, 17 October 2007 17:28 Go to previous messageGo to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
If you're going to frequently use Ref Cursors and that too loosely-bound, then I would suggest you first create a package which has a ref cursor type defined.
You can then use that package.refcursor type as argument in your procedure's parameter.

for e.g.
CREATE PACKAGE myVars AS
TYPE CommonCurTyp IS REF CURSOR;
END emp_data;

you can then use myVars.CommonCurTyp as a data type for an OUT parameter in your procedure (the way you've sys_refcursor currently)

And you can this all over your other PL/SQL based program units.

Hope this helps.
Re: pl/sql cursors [message #274952 is a reply to message #274947] Wed, 17 October 2007 21:45 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member
this is exactly what I want .thank you. ora_balan
Re: pl/sql cursors [message #274992 is a reply to message #274952] Thu, 18 October 2007 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I saw you need to be spoon fed.
If you just typed "ref cursor" in the search field of Oracle documentation, you got it.

Regards
Michel
Re: pl/sql cursors [message #275130 is a reply to message #274992] Thu, 18 October 2007 07:39 Go to previous messageGo to next message
jriggs
Messages: 12
Registered: October 2007
Junior Member
michel, welcome to my ignore list. This is a forum for people who are trying to learn. Does 'newbies' not translate for you? Your posts in this thread, or ANY OTHER, I have read have done nothing to aid in the learning process, or even provide the slightest shred of useful information.

You have failed to grasp the concept of a forum or its purpose. Why do you even bother to post? Does it make you feel so much better to exult your greatness over those who are trying to learn?

Regards,

jriggs

[mod-edit]Keep it polite, thank you.

[Updated on: Thu, 18 October 2007 08:41] by Moderator

Report message to a moderator

Re: pl/sql cursors [message #275155 is a reply to message #275130] Thu, 18 October 2007 10:16 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

It's a pity you can't search and read.

I'm always sad when I see someone thinking he will go higher and better if (he thinks) he succeeds to lower others.

Regards
Michel
Previous Topic: How to know the information about packages that are getting executed in the database
Next Topic: How to drop all constraints on a schema at once?
Goto Forum:
  


Current Time: Sat Nov 09 14:50:23 CST 2024