pl/sql cursors [message #274885] |
Wed, 17 October 2007 10:36 |
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 #274900 is a reply to message #274889] |
Wed, 17 October 2007 11:13 |
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 #274936 is a reply to message #274931] |
Wed, 17 October 2007 14:43 |
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 #274939 is a reply to message #274937] |
Wed, 17 October 2007 15:28 |
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 #274947 is a reply to message #274885] |
Wed, 17 October 2007 17:28 |
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 #275130 is a reply to message #274992] |
Thu, 18 October 2007 07:39 |
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 |
|
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
|
|
|