Calling Stored Proc from stored proc witth Cursor as param

Hi,

I am facing problem, in calling a stored procedure from another stored procedure with cursor as one of the argument (OUT).

Create or Replace mai_proc as
begin

call_proc(a, cursor_b);

end mai_proc;

This is what i want to do.

Here call_proc as two arguments, 1. a IN, 2. cursor_b OUT.

Problem is how to declare cursor_b?
I cannot use "Cursor cursor_b is <>". But the cursor exepect some return.

Please give the syntax

Thanks in Advance
Regards Jagannadh

Hi

CREATE OR REPLACE PACKAGE procpack AS
TYPE curtype IS REF CURSOR;
procedure main_proc;
procedure call_proc(a in number,b out curtype);
end procpack;

CREATE OR REPLACE PACKAGE BODY procpack AS
proceduer main_proc is
begin
call_proc(a in number,b out curtype);
end;

procdure call_proc(b in number,c curtype) is
begin
--Following is the syntax for opening REF cursor
OPEN c FOR SELECT a,b.c from xyz;
end;


Hope this would help

Manoj

Thanks for the help.

Now i facing problem while using the returned Cursor type,

I want to fetch some data out of that cursor b, in main_proc.

If i do a open , it says invalid datatype.

I have to open the cursor and fetch data and do, some operation on data.

Can u tell me whatr is the problem

Thanks in advance

Jagannadh

How did u open it ? Please paste the code how u open it

Hi Manoj,

create or replace procedure main_proc as

cur_get_approval_list PKG_MYPKG.RCT1;

Begin
call_proc(a, out cur_get_approval_list);
--no out is there in code
OPEN cur_get_approval_list;
LOOP
FETCH cur_get_approval_list INTO a, b
------
----------
end;

Open, it throws error saying

PLS-00382: expression is of wrong type

Regards Jagannadh