Home » SQL & PL/SQL » SQL & PL/SQL » Display REFCURSOR results through PL/SQL
Display REFCURSOR results through PL/SQL [message #267439] Thu, 13 September 2007 05:11 Go to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Hi,


CREATE OR REPLACE PACKAGE try AS
TYPE REF_CUR IS REF CURSOR;

FUNCTION AVG(num1 in number, num2 in NUMBER) RETURN REF_CUR;

END try;
/

SQL>
DECLARE
emp_curvar try.ref_cur;
val SYS_REFCURSOR;
BEGIN
OPEN emp_curvar FOR SELECT try.avg(34,65) from dual;
Fetch emp_curvar into val;
dbms_output.put_line(val);
END;
SQL> /
dbms_output.put_line(val);
*
ERROR at line 7:
ORA-06550: line 7, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 7, column 7:
PL/SQL: Statement ignored

My question is how value of val(REF_CURSOR) be displayed here as
DBMS_OUTPUT cannot return datatype-REF_CURSOR

Any suggestions would be highly appreciated.

Many thanks
Re: Display REFCURSOR results through PL/SQL [message #267456 is a reply to message #267439] Thu, 13 September 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267457 is a reply to message #267439] Thu, 13 September 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> desc dbms_output
PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT
PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINE                           VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(32767) OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          DBMSOUTPUT_LINESARRAY   OUT
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN
PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN

Where do you see put_line takes something that is not a VARCHAR2 as parameter?

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267466 is a reply to message #267457] Thu, 13 September 2007 06:44 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Hi Michel

Thanks for your reponse.

How could I display the VAL in PL/SQL ?

Thanks
Re: Display REFCURSOR results through PL/SQL [message #267467 is a reply to message #267466] Thu, 13 September 2007 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Display each field.

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267469 is a reply to message #267467] Thu, 13 September 2007 06:54 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Hi Michel

I want to return value of AVG in val via REF CURSOR.

When I run thru SQL it runs okay....

SQL> SELECT pkg_icm.func_get_average(34,65) from dual;

PKG_ICM.FUNC_GET_AVE
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

AVERAGE
----------
49.5

but how would is display value 49.5 thru PL/SQL ?

Thanks

Re: Display REFCURSOR results through PL/SQL [message #267474 is a reply to message #267469] Thu, 13 September 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you get a ref cursor then open it and fetch it.

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267478 is a reply to message #267474] Thu, 13 September 2007 07:38 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Thats what I tried and got error message ...if you pls see my message on top

It fetches but when I try to display it, there's datatype mismatch

Thanks

Re: Display REFCURSOR results through PL/SQL [message #267480 is a reply to message #267478] Thu, 13 September 2007 07:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To display the rows returned by the Ref Cursor, you need to actually fetch it into a variable, and then display that variable one row at a time.
Re: Display REFCURSOR results through PL/SQL [message #267482 is a reply to message #267480] Thu, 13 September 2007 07:45 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Hi,

CREATE OR REPLACE PACKAGE try AS
TYPE REF_CUR IS REF CURSOR;

FUNCTION AVG(num1 in number, num2 in NUMBER) RETURN REF_CUR;

END try;
/

SQL>
DECLARE
emp_curvar try.ref_cur;
val SYS_REFCURSOR;
BEGIN
OPEN emp_curvar FOR SELECT try.avg(34,65) from dual;
Fetch emp_curvar into val;
dbms_output.put_line(val);
END;
SQL> /
dbms_output.put_line(val);
*
ERROR at line 7:
ORA-06550: line 7, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 7, column 7:
PL/SQL: Statement ignored

My question is how value of val(REF_CURSOR) be displayed here as
DBMS_OUTPUT cannot return datatype-REF_CURSOR

I'm opening the cursor and fetching it.

I'm not looping bcoz its just going to return single row.

Many thanks
Re: Display REFCURSOR results through PL/SQL [message #267490 is a reply to message #267482] Thu, 13 September 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to format your post?
I can't read them like they are, so I can't give you help.

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267536 is a reply to message #267482] Thu, 13 September 2007 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm opening the cursor and fetching it.

You're opening and fetching "SELECT try.avg(34,65) from dual;", you're not opening and fetching the result of this.

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267574 is a reply to message #267536] Thu, 13 September 2007 13:44 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Hi Michel
I'm opening closing and fetching in following ways....

OPEN emp_curvar FOR SELECT try.avg(34,65) from dual;
Fetch emp_curvar into val;
dbms_output.put_line(val);

Thanks

Nitin
Re: Display REFCURSOR results through PL/SQL [message #267575 is a reply to message #267574] Thu, 13 September 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you are not opening and fetching val.

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267579 is a reply to message #267575] Thu, 13 September 2007 14:23 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Hi Michel

If you have understood my question could I pls ask you to write those lines of code that you are trying to explain ?

I can then try it out

Thanks a lot
Re: Display REFCURSOR results through PL/SQL [message #267581 is a reply to message #267579] Thu, 13 September 2007 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you try to understand what I point you.
What is the type of val?

Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267582 is a reply to message #267581] Thu, 13 September 2007 14:28 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
val is SYS_REFCURSOR

code is .....

CREATE OR REPLACE PACKAGE try AS
TYPE REF_CUR IS REF CURSOR;
FUNCTION AVG(num1 in number, num2 in NUMBER) RETURN REF_CUR;
END try;


DECLARE
emp_curvar try.ref_cur;
val SYS_REFCURSOR;
BEGIN
OPEN emp_curvar FOR SELECT try.avg(34,65) from dual;
Fetch emp_curvar into val;
dbms_output.put_line(val);
END;
Re: Display REFCURSOR results through PL/SQL [message #267583 is a reply to message #267582] Thu, 13 September 2007 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


FORMAT YOUR POST

THIS IS MY LAST MESSAGE TILL YOU FORMAT


Regards
Michel
Re: Display REFCURSOR results through PL/SQL [message #267584 is a reply to message #267582] Thu, 13 September 2007 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid that this discussion will never end (unless you all go to bed). The original poster doesn't understand what other Forum members suggest, and Forum members are too tired for today to do another push into the right direction.

In order to finish this torture, here you are; see if this can help:
SQL> CREATE OR REPLACE FUNCTION fun_avg
  2    (par_1 IN NUMBER, par_2 IN NUMBER)
  3  RETURN sys_refcursor
  4  IS
  5    l_rc sys_refcursor;
  6  BEGIN
  7    OPEN l_rc FOR
  8      SELECT (par_1 + par_2) / 2 average FROM dual;
  9    RETURN l_rc;
 10  END;
 11  /

Function created.

SQL> DECLARE
  2    l_avg NUMBER;
  3    l_rc sys_refcursor;
  4  BEGIN
  5    l_rc := fun_avg(10, 20);
  6    FETCH l_rc INTO l_avg;
  7    dbms_output.put_line(l_avg);
  8    CLOSE l_rc;
  9  END;
 10  /
15

PL/SQL procedure successfully completed.

SQL>
icon14.gif  Re: Display REFCURSOR results through PL/SQL [message #267586 is a reply to message #267584] Thu, 13 September 2007 15:09 Go to previous messageGo to next message
10guser
Messages: 16
Registered: September 2007
Junior Member
Thanks a bunch
Re: Display REFCURSOR results through PL/SQL [message #283416 is a reply to message #267584] Tue, 27 November 2007 01:51 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi LittleFoot,

Can I Use < For recordset in cursor loop> instead of open and Fetch statements.

I tried the same but it is throwing an error.

Thanks & Regards
Amit
Re: Display REFCURSOR results through PL/SQL [message #283430 is a reply to message #283416] Tue, 27 November 2007 02:18 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried as long as with your Oracle version.

Regards
Michel
Previous Topic: find check constraint what i have in specific column from data dictionary
Next Topic: using multiple "single quotation"
Goto Forum:
  


Current Time: Fri Dec 09 07:52:50 CST 2016

Total time taken to generate the page: 0.10497 seconds