Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    17

    Unanswered: other option for refcursors

    Hi,

    I am using refcursor for fetching the resultset from stored procedure.

    I heard something Piperow (not confirmed) can also be used to fetch records from the stored procedure.

    Can anybody do let me is it true? if it is then I would like to know about it.
    Pls send me links so that I can go through.


    Regards,
    Sandeep

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

  3. #3
    Join Date
    Aug 2009
    Posts
    17
    Thanks andrewst

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    you are using refcursor .

    try using system.refcursor

    create or replace procedure wth2 (a_id in number,t_cursor OUT SYS_REFCURSOR)
    is
    begin
    if a_id is null
    then
    open t_cursor for

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Here's some code you can follow. Using the PIPELINED function allows you to call the resultset from SQL (see the description block in the PACKAGE SPEC)

    Code:
    CREATE OR REPLACE PACKAGE SCHEMA.XXNNNN_PG AS
    -- *-----------------------------------------------------------------
    -- *  CREATED Date:     MM/DD/YYYY BY <your initials>
    -- *  MODIFIED Date:    MM/DD/YYYY BY <modifier initials>
    -- *  VERSION #:        ORA001.
    -- *
    -- *  TITLE:  
    -- *
    -- *  FUNCTION:
    -- *    Brings back data using SQL, where the SQL uses the syntax like
    -- *    the following:
    -- *
    -- *    select * from table(SCHEMA.XXNNNN_PG.MAIN('R'));
    -- *
    -- *    or even better, mentioning each field specifically:
    -- *
    -- *    select ui_number,
    -- *           user_id,
    -- *           status,
    -- *           status_date,
    -- *           report_id
    -- *      from table(SCHEMA.XXNNNN_PG.MAIN('R'));
    -- *
    -- *-----------------------------------------------------------------
    -- *                      MODIFICATION HISTORY
    -- *                      ====================
    -- * VER#  DPSR#  BY     Date    DESCRIPTION OF CHANGE
    -- * ====  =====  ===  ========  ====================================
    -- *
    -- *ORA001 xxxx   XWZ  MM/DD/YY  Created package.
    -- *
    -- *              PLEASE PUT LATEST MOD INFO FIRST.
    -- *-----------------------------------------------------------------
    
    -- create a row which you'd like the calling application to read 
    --
    TYPE RETURN_ROW is RECORD (UI_NUMBER   NUMBER(10),
                               USER_ID     VARCHAR2(3),
                               STATUS      VARCHAR2(30),
                               STATUS_DATE DATE,
                               REPORT_ID   NUMBER(1)
                              );
            
    -- create a PL/SQL table capable of holding that row
    --
    TYPE RETURN_TAB is TABLE of RETURN_ROW;       
    
    -- create the main function with which the calling application will interface
    -- with, making sure that the return variable is of the PL/SQL table type
    -- defined above, and set to be PIPELINED
    --
    FUNCTION Main(Arg_Report  IN VarChar2) return RETURN_TAB PIPELINED;
    
    END XXNNNN_PG;
    /
    
    
    CREATE OR REPLACE PACKAGE BODY SCHEMA.XXNNNN_PG AS
    
    -- Global Variable Declarations ---------
    -- *----------------------------------------------------------------
    --     SWITCHES, COUNTERS, WORK FIELDS
    -- *----------------------------------------------------------------
    
      -- use this variable to determine if a record should be output to the calling
      --  application. Note that every row from your cursor may not go out to the 
      --  report, and so this second boolean is necessary
      bool_output_rec          BOOLEAN;
    
    
    -- *----------------------------------------------------------------
      Lk_Report                               VarChar2(1);
    
    -- Cursor Declarations ------------------
      CURSOR CSR is
       select 1 as THE_NUM from dual;
    
    -- Table Declarations -------------------
    
      -- this row variable, of the type declared in the PACKAGE spec, is set up at the top of the PACKAGE BODY
      --  so that it can be populated in any procedure, but still be visible in MAIN, where
      --  it is passed back to the calling procedure
      OUTPUT_ROW        RETURN_ROW;
    
    
    -- Procedure Definitions ----------------
      PROCEDURE p100_Initialization;
      PROCEDURE p200_Main(P_CSR_ROW CSR%ROWTYPE);
    
    -- Functions and Procedures -------------
    
    -----------------------------------------
    -----------------------------------------
      FUNCTION Main(Arg_Report  IN VarChar2) return RETURN_TAB PIPELINED IS
      BEGIN
    
        -- set default values for parms, if appropriate, or as in this case, exit the PACKAGE completely
        IF Arg_Report IS NULL THEN
          return;
        ELSE
          Lk_Report := Arg_Report;
        END IF;
    
        p100_Initialization;
    
        For CSR_ROW in CSR
        Loop
    
          p200_Main(CSR_ROW);
    
          -- this is where you pass a specific row back out to the calling procedure
          If bool_output_rec then
            PIPE ROW(OUTPUT_ROW);
          End If;
    	
        END LOOP;
    
     
        --Must be the last line in MAIN, and actually ends the function
        RETURN;
    
      END Main;
    -----------------------------------------
    ----------------------------------------- 
      PROCEDURE p100_Initialization IS
      BEGIN
    
        -- initialize any variables that need a starting value
        null;
     
      END p100_Initialization;
    -----------------------------------------
    -----------------------------------------
      PROCEDURE p200_Main(P_CSR_ROW CSR%ROWTYPE) IS
      BEGIN
    
        -- starts as TRUE, and can be set to FALSE if a test against CSR_ROW invalidates
        --  it's inclusion in the set of output records
        bool_output_rec := TRUE;
    
        If P_CSR_ROW.THE_NUM = 5 Then
          bool_output_rec := False;
          return;
        Else
          -- Fill the row you want output to the calling procedure
          OUTPUT_ROW.UI_NUMBER   := 1;
          OUTPUT_ROW.USER_ID     := 'XYZ';
          OUTPUT_ROW.STATUS      := 'BAD';
          OUTPUT_ROW.STATUS_DATE := sysdate;
          OUTPUT_ROW.REPORT_ID   := P_CSR_ROW.THE_NUM;
        End If; 
    
      end p200_Main;
    
    -----------------------------------------
    END XXNNNN_PG;
    /

Posting Permissions

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