what is the C# code to read types.cursortypes in procedure

Question

I have a stored procedure like this PROCEDURE  PRC_ABCD_GETALL (resultset_out  OUT  TYPES.cursorType)   AS    BEGIN    OPEN    resultset_out  FOR   SELECT  *  FROM  ABCD;   END  PRC_ABCD_GETALL;   And my C# code is using  (OracleConnection conn =  new  OracleConnection(cnn))   {       conn.Open();       OracleCommand cmd =  new  OracleCommand();       cmd.Connection = conn;       cmd.CommandText =  "PRC_ABCD_GETALL" ;       cmd.CommandType = CommandType.StoredProcedure;       cmd.Parameters.Add( "resultset_out" , OracleType.Cursor, ParameterDirection.Output);       OracleDataReader rdr = cmd.ExecuteReader();        while  (rdr.Read())       {              result.Add(Construct(rdr));       }   }   but I'm getting error 'OracleDbType' does not contain a definition for 'Cursor' in OracleType.Cursor. Any solution for this??

Laxmidhar Sahoo · Answer

hi   If you are using a version of Oracle before 9i, then create the following package and replace any references to SYS_REFCURSOR with TYPES.cursor_type.   CREATE OR REPLACE PACKAGE types AS TYPE cursor_type IS REF CURSOR; END Types; /

Sudipta Saha · Answer

hi Laxmidhar   I tried everything no luck :( . My C# code is   public static IEnumerable GetAll() { string cnn = "connectionstring"; var result = new List(); using (OracleConnection conn = new OracleConnection(cnn)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "PRC_ABCD_GETALL"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("resultset_out", OracleDbType.RefCursor).Direction = ParameterDirection.Output; OracleDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { result.Add(Construct(rdr)); } } return result; }   and I am facing ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PRC_ABCD_GETALL' ORA-06550: line 1, column 7: PL/SQL: Statement ignored . any solution on this?

Sudipta Saha · Answer

happy new year :) and once again thanks for reply :)

Laxmidhar Sahoo · Answer

Sorry I forgot   Happy new year and hope ur  new year be successfull   With regards Laxmidhar

Laxmidhar Sahoo · Answer

Hi Actualy u r tellig about ref cusor I send  u some links   https://community.oracle.com/thread/2371168?tstart=0     https://stackoverflow.com/questions/6781916/how-to-test-an-oracle-stored-procedure-with-refcursor-return-type   Thanks

Sudipta Saha · Answer

hi thanks for reply :) . what if i don't want to change TYPES.cursortype into SYS_REFCURSOR then what will be the c# code?can you tell me what is the difference between TYPES.cursortype and SYS_REFCURSOR?

Laxmidhar Sahoo · Answer

hi The bellow code may help you   CREATE   OR   REPLACE    PROCEDURE  get_emp_rs (p_deptno     IN   emp.deptno%TYPE,                         p_recordset  OUT  SYS_REFCURSOR)  AS     BEGIN        OPEN  p_recordset  FOR         SELECT  ename,              empno,              deptno        FROM    emp        WHERE   deptno = p_deptno        ORDER   BY  ename;   END  GetEmpRS;