Cursor as out parameter [message #178220] |
Tue, 20 June 2006 03:14  |
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   |
Frank Naude
Messages: 4590 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   |
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   |
Frank
Messages: 7901 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   |
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 #178412 is a reply to message #178400] |
Wed, 21 June 2006 01:41   |
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 #268290 is a reply to message #178449] |
Tue, 18 September 2007 02:15   |
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 #268335 is a reply to message #268323] |
Tue, 18 September 2007 04:09  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|