Home » SQL & PL/SQL » SQL & PL/SQL » Cursor as out parameter
Cursor as out parameter [message #178220] Tue, 20 June 2006 03:14 Go to next message
sxramas
Messages: 16
Registered: January 2006
Location: Chennai
Junior Member
Hello ,

I want to call a procedure which returns a cursor using Execute immediate statement. Is it possible ? If it is possible Please let me know the syntax.

Thanks in advance for your response.

Regards,
Siva
Re: Cursor as out parameter [message #178235 is a reply to message #178220] Tue, 20 June 2006 04:03 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Use OPEN-FOR instead. Example:

SQL> CREATE OR REPLACE PACKAGE my_types_pkg AS
  2    TYPE refCursor IS REF CURSOR;
  3  END MY_TYPES_PKG;
  4  /

Package created.

SQL>
SQL> CREATE OR REPLACE FUNCTION dynamic_cursor_example
  2     RETURN MY_TYPES_PKG.refCursor
  3  IS
  4    emp_cv   my_types_pkg.refCursor;
  5    sql_stmt VARCHAR2(100);
  6  BEGIN
  7    sql_stmt := 'SELECT * FROM emp';
  8    OPEN emp_cv FOR sql_stmt;
  9    RETURN emp_cv;
 10  END;
 11  /

Function created.
Re: Cursor as out parameter [message #178253 is a reply to message #178235] Tue, 20 June 2006 05:42 Go to previous messageGo to next message
sxramas
Messages: 16
Registered: January 2006
Location: Chennai
Junior Member
Hi

Thanks for your help .

I have created a package .

CREATE OR REPLACE PACKAGE Pack_Report_Catalogue AS
TYPE curr IS REF CURSOR;
FUNCTION dynamic_cursor_example RETURN Pack_Report_Catalogue.curr;
END Pack_Report_Catalogue;

CREATE OR REPLACE FUNCTION dynamic_cursor_example
RETURN ref cursor
IS
emp_cv curr;
sql_stmt VARCHAR2(100);
TYPE curr IS REF CURSOR;
BEGIN
sql_stmt := 'SELECT pvxp.parameter_id FROM PVX_Parameters pvxp,PVX_Report_Parameters b where pvxp.parameter_id = b.parameter_id';
OPEN emp_cv FOR sql_stmt;
RETURN emp_cv;
END;

The query will be returning 3 rows .
The function got compiled . But while executing the function it shows error as

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Please let me know how to fix this .

Thanks
Siva
Re: Cursor as out parameter [message #178257 is a reply to message #178253] Tue, 20 June 2006 06:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
c'mon, read the error message !!

SQL> select length('SELECT pvxp.parameter_id FROM PVX_Parameters pvxp,PVX_Report
_Parameters b where pvxp.parameter_id = b.parameter_id') from dual;

LENGTH('SELECTPVXP.PARAMETER_IDFROMPVX_PARAMETERSPVXP,PVX_REPORT_PARAMETERSBWHER

--------------------------------------------------------------------------------

                                                                             114

your code:
sql_stmt VARCHAR2(100);
Re: Cursor as out parameter [message #178263 is a reply to message #178253] Tue, 20 June 2006 06:41 Go to previous messageGo to next message
sxramas
Messages: 16
Registered: January 2006
Location: Chennai
Junior Member
Oops !!! Sorry for asking that quaetion . I didnt notice properly .

Thats great its working fine .

But according to my requirement i want to call a function dynamically . That is based on the IN parameter of the calling
procedure i will fetch the name of the function from a table.

THen i will execute that fucntion using execute immediate .

For ex,

PROCEDURE proc2(b out curr)
as
v_procedure VARCHAR2(10);
a varchar(1);
TYPE result_cur IS REF CURSOR;
res_cur result_cur;
begin
execute immediate 'begin res_cur := exec Pack_Report_Catalogue.dynamic_cursor_example(); end;' using out res_cur ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('b');
dbms_output.put_line(SQLCODE||' '||SQLERRM);
end proc2;

It is not accepting Pl/SQL as data type in using clause . Please
let me know how to fix this or there is any alternate solution for this .

Thanks a lot
Siva
Re: Cursor as out parameter [message #178400 is a reply to message #178220] Wed, 21 June 2006 01:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


PROCEDURE proc2(b out curr)
as
v_procedure VARCHAR2(10);
a varchar(1);
TYPE result_cur IS REF CURSOR;
res_cur result_cur;
begin
execute immediate 'begin :cur := Pack_Report_Catalogue.dynamic_cursor_example(); end;' using out res_cur ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('b');
dbms_output.put_line(SQLCODE||' '||SQLERRM);
end proc2;


Thumbs Up
Rajuvan.
Re: Cursor as out parameter [message #178412 is a reply to message #178400] Wed, 21 June 2006 01:41 Go to previous messageGo to next message
sxramas
Messages: 16
Registered: January 2006
Location: Chennai
Junior Member
Hi

I tried compiling it . Iam getting the same error stating that "PLS-00457: expressions have to be of SQL types"

The problem is it is not accepting cursor(non sql datat type) in using clause .


And also in the below statement ,
execute immediate 'begin :cur := Pack_Report_Catalogue.dynamic_cursor_example(); end;' using out res_cur ;

we didnt declare cur anywhere . i tried the above statement as well as the below one but both are giving teh same error.

execute immediate 'begin :res_cur := Pack_Report_Catalogue.dynamic_cursor_example(); end;' using out res_cur ;

Please help .

Thanks
Nirmala

Re: Cursor as out parameter [message #178449 is a reply to message #178220] Wed, 21 June 2006 03:29 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Frank,
Pls refer to your code:

SQL> CREATE OR REPLACE FUNCTION dynamic_cursor_example
  2     RETURN MY_TYPES_PKG.refCursor
  3  IS
  4    emp_cv   my_types_pkg.refCursor;
  5    sql_stmt VARCHAR2(100);
  6  BEGIN
  7    sql_stmt := 'SELECT * FROM emp';
  8    OPEN emp_cv FOR sql_stmt;
  9    RETURN emp_cv;
 10  END;
 11  /


Above we have opened the cursor. But it is no ware closed. I guess the cursor will be still open for certain period of time.

My questions are:
1) Whether my understanding is correct.
2) When the cursor will automatically get closed.
3) Whether this will make some performance issue.

The reason of me asking this question is because; I was working in an application which involves coldfusion as the front end and oracle as the database.
Normally for report generation, I used to call the pl/sql procedures using coldfusion code and inside the procedure I used to get the records using refcursor. Finally I will return the resultant set to the coldfusion with out closing the cursor. There were many reports and all the cursors declared in this way will be open...I found some performance issue..
What will be the workaround for this? Is there any option for closing the cursor automatically?


Please advise me on this.

Naveen

Re: Cursor as out parameter [message #268290 is a reply to message #178449] Tue, 18 September 2007 02:15 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
oracle 9i
i am also having similar problem..
the cursor is returning values but on fetching it gives only i value
 create or replace function apartment1
 return varchar2
 is
cursor c1 is
 select apt_name from apartment where reg_id in ( select reg_id from (select
 reg_id from region order by avg_rate_per_sqft desc)
 where rownum<=3);
begin
 for i in c1
 loop
 return  i.apt_name;
 end loop;
 end;
/
SQL> declare
  2  x varchar2(30);
  3  begin
  4  x:=apartment1;
  5  dbms_output.put_line(x);
  6  end;
  7  /
Krishna CHS

PL/SQL procedure successfully completed.

[Updated on: Tue, 18 September 2007 03:13]

Report message to a moderator

Re: Cursor as out parameter [message #268307 is a reply to message #268290] Tue, 18 September 2007 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you want to format your code and follow the guidelines?

Regards
Michel
Re: Cursor as out parameter [message #268323 is a reply to message #268307] Tue, 18 September 2007 03:15 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
sorry initially i didnt know how to format the ccode but i have done now.. plz answer my query
thanks.. i am having tom pl/sql exam
Re: Cursor as out parameter [message #268335 is a reply to message #268323] Tue, 18 September 2007 04:09 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your function return at the first "return" statement so you don't have other values.
Have a look at pipelined functions.

Regards
Michel
Previous Topic: COUNT NULL ROWS
Next Topic: Invalid file operation problem with 10g
Goto Forum:
  


Current Time: Wed Dec 07 12:24:36 CST 2016

Total time taken to generate the page: 0.08909 seconds