I'm calling a procedure which has an OUT parameter (of CURSOR Type). It seems I'm missing something because I get error: "wrong number or types of arguments...". My code is as follows:
public static DataTable SearchClient()
{
try
{
using (OleDbCommand cmd = new OleDbCommand(packetName + ".select_A1001310"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output;
SqlManager sqlManager = new SqlManager();
return sqlManager.GetDataTable(cmd);
}
}
catch (Exception ex)
{
ex.ToString();
System.Console.WriteLine(ex);
//TODO; Handle exception
}
return null;
}
PROCEDURE:
TYPE lcursor_data IS REF CURSOR;
PROCEDURE select_A1001310(io_cursor OUT lcursor_data)
AS
BEGIN
OPEN io_cursor FOR
--
SELECT client_id
FROM a1001310
WHERE status = 'A';
--
EXCEPTION
WHEN OTHERS THEN
IF io_cursor%ISOPEN THEN
CLOSE io_cursor;
END IF;
--REVIRE: EXCEPTION HANDLER
END select_A1001310;
Any ideas?
UPDATE: If I change my code, to use OracleClient instead of OleDb, it works. Is there a way to include a Cursor Type parameter using oleDb instead of OracleClient? The OracleClient code approach is as follows
public static DataTable SearchClient()
{
string connection = ConfigurationManager.ConnectionStrings["DBConnection_Oracle"].ToString();
string procedure = packetName + ".p_search_client";
OracleParameter[] parameters = new OracleParameter[1];
parameters[0] = new OracleParameter("io_cursor", OracleType.Cursor, 4000, ParameterDirection.Output, true, 0, 0, "", DataRowVersion.Current, String.Empty);
DataTable dt = new DataTable();
dt = DataManager_Oracle.GetDataTable_(connection, procedure, parameters);
return dt;
}
<connectionStrings> <add name="DBConnection" connectionString="Provider=MSDAORA.1;Data Source=DB;User Id=user;Password=pass;enlist=false;OLE DB Services = -4" providerName="Oracle.DataAccess"/> </connectionStrings>