Remove From My Forums

Read cursor and varchar2 from storedprocedure

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

Answered by:

Read cursor and varchar2 from storedprocedure

Question

Answers