Calling Stored Proc from stored proc witth Cursor as param
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Calling Stored Proc from stored proc witth Cursor as param

  1. #1
    Join Date
    Feb 2002
    Posts
    14
    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
    Jagannadh

  2. #2
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110
    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

  3. #3
    Join Date
    Feb 2002
    Posts
    14
    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
    Jagannadh

  4. #4
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110


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

  5. #5
    Join Date
    Feb 2002
    Posts
    14
    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

    Jagannadh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width