Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Call a stored procedure from another stored procedure?

    Hi

    Is it possible to call a stored procedure from another stored procedure and pass a reference cursor? Perhaps if I lay out the sequence of events that I want to follow someone can help me:

    I am using a stored procedure that needs to return a recordset with 126 columns. Some of these values can be extracted from a table, but others need to be calculated from the values extracted from the table.

    I want a secondary stored procedure to extract the values from a table; I want this secondary stored procedure to be called by a primary stored procedure.

    The primary stored procedure needs to be able to use the columns from a reference cursor returned from the secondary stored procedure both to display these values, and to calculate new values with some simple math.

    The primary procedure needs to return a reference cursor.

    Could someone give me some idea how to do this, particularly the calling of a second stored procedure, the declaration of reference cursors and how to reference columns that are returned from the called stored procedure.

    Sorry to have asked such a complex question, but I am moving from SQL Server to Oracle, and I am expected to move at breakneck speed whilst learning it!

    Thank you

    Michael

  2. #2
    Join Date
    Mar 2004
    Posts
    1
    1. Create a package and declare a reference cursor in specification.

    CREATE OR REPLACE PACKAGE pkg_var
    IS

    TYPE gen_cur is ref cursor;

    END pkg_var;
    /

    2. Your secondary procedure should be something like this :

    This basically defines your result set and returns.

    create or replace PROCEDURE SEC_PROC(i_cur out pkg_var.gen_cur) is

    begin
    open i_cur for select sal, comm from emp; --Your 120 columns query.
    end;

    3. Use the returned procedure in primary procedure :

    create or replace procedure PRIM_PROC
    v_val1 NUMBER;
    v_val2 NUMBER;
    v_returned_cursor pkg_var.gen_cur;
    BEGIN

    SEC_PROC(v_returned_cursor);

    LOOP
    FETCH v_returned_cursor INTO v_val1, v_val2;

    EXIT WHEN v_returned_cursor%NOTFOUND;
    -- Do your calculations here:
    DBMS_OUTPUT.put_line (v_val1 || ';' || v_val2);
    END LOOP;

    CLOSE v_returned_cursor;
    END;


    Good luck.
    Jo

Posting Permissions

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