OraFAQ Forum: SQL & PL/SQL » Display REFCURSOR results through PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Display REFCURSOR results through PL/SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Display REFCURSOR results through PL/SQL [message #267439]

Thu, 13 September 2007 05:11

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267456 is a reply to message #267439]

Thu, 13 September 2007 06:20

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267457 is a reply to message #267439]

Thu, 13 September 2007 06:22

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267466 is a reply to message #267457]

Thu, 13 September 2007 06:44

10guser
Messages: 16
Registered: September 2007

Junior Member

Hi Michel

Thanks for your reponse.

How could I display the VAL in PL/SQL ?

Thanks

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267467 is a reply to message #267466]

Thu, 13 September 2007 06:47

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Display each field.

Regards
Michel

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267469 is a reply to message #267467]

Thu, 13 September 2007 06:54

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267474 is a reply to message #267469]

Thu, 13 September 2007 07:22

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you get a ref cursor then open it and fetch it.

Regards
Michel

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267478 is a reply to message #267474]

Thu, 13 September 2007 07:38

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267480 is a reply to message #267478]

Thu, 13 September 2007 07:42

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.

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267482 is a reply to message #267480]

Thu, 13 September 2007 07:45

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267490 is a reply to message #267482]

Thu, 13 September 2007 08:36

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267536 is a reply to message #267482]

Thu, 13 September 2007 11:19

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267574 is a reply to message #267536]

Thu, 13 September 2007 13:44

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267575 is a reply to message #267574]

Thu, 13 September 2007 13:45

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But you are not opening and fetching val.

Regards
Michel

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267579 is a reply to message #267575]

Thu, 13 September 2007 14:23

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267581 is a reply to message #267579]

Thu, 13 September 2007 14:26

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Could you try to understand what I point you.
What is the type of val?

Regards
Michel

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267582 is a reply to message #267581]

Thu, 13 September 2007 14:28

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;

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267583 is a reply to message #267582]

Thu, 13 September 2007 14:43

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

FORMAT YOUR POST

THIS IS MY LAST MESSAGE TILL YOU FORMAT

Regards
Michel

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267584 is a reply to message #267582]

Thu, 13 September 2007 14:47

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #267586 is a reply to message #267584]

Thu, 13 September 2007 15:09

10guser
Messages: 16
Registered: September 2007

Junior Member

Thanks a bunch

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #283416 is a reply to message #267584]

Tue, 27 November 2007 01:51

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

Report message to a moderator

Re: Display REFCURSOR results through PL/SQL [message #283430 is a reply to message #283416]

Tue, 27 November 2007 02:18

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post what you tried as long as with your Oracle version.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 15 17:18:34 CST 2025