locked
Read cursor and varchar2 from storedprocedure RRS feed

  • Question

  • User2015309869 posted

    Procedure example:

    CREATE OR REPLACE PACKAGE CURSPKG AS 
      TYPE T_CURSOR IS REF CURSOR; 
      PROCEDURE XPTO (TEST OUT VARCHAR2, 
                                  DEPTCURSOR OUT T_CURSOR); 
    END CURSPKG;
    
    CREATE OR REPLACE PACKAGE BODY CURSPKG AS 
      PROCEDURE XPTO  (TEST OUT VARCHAR2, 
                                  DEPTCURSOR OUT T_CURSOR) 
      IS 
      BEGIN 
        TESTE := 'YES'; 
        OPEN DEPTCURSOR FOR SELECT ID,  NAME FROM DEPART; 
      END ; 
    END CURSPKG; 

    OracleCommand cursCmd = new OracleCommand("CURSPKG.XPTO", oraConn);
    cursCmd.Parameters.Add("TEST", OracleType.Varchar).Direction = ParameterDirection.Output;
    cursCmd.Parameters.Add("DEPTCURSOR", OracleType.Cursor).Direction = ParameterDirection.Output;

    Now I want to know what method I should use to execute procedure and How I read cursor and varchar.

    I know how to read if it return only cursor, but varchar and cursor as output I dont know how to do it.



    Saturday, October 29, 2011 7:39 PM

Answers

  • User269602965 posted

    Put the variable in your ref cursor and read it with datareader as the third returned variable

    CREATE OR REPLACE PACKAGE CURSPKG AS  
      TYPE T_CURSOR IS REF CURSOR;  
      PROCEDURE XPTO (DEPTCURSOR OUT T_CURSOR);  
    END CURSPKG; 
     
    CREATE OR REPLACE PACKAGE BODY CURSPKG AS  
      PROCEDURE XPTO  (DEPTCURSOR OUT T_CURSOR)  
      IS
      DECLARE
        varTEST VARCHAR2(3) := 'Yes';
      BEGIN  
        OPEN DEPTCURSOR FOR SELECT ID, NAME, CAST(varTEST AS VARCHAR2(3)) AS TEST FROM DEPART;  
      END ;  
    END CURSPKG;  
     
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 29, 2011 10:21 PM