0

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;
}
3
  • Could you add your connection string? Thanks! Commented Mar 30, 2017 at 21:39
  • Hi. Connection string is as follows. <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> Commented Mar 31, 2017 at 15:26
  • I have updated my answer. Commented Mar 31, 2017 at 15:48

1 Answer 1

1

If your stored procedure returns a rowset then, PLSQLRSet must be set to TRUE in your web.config

<connectionStrings> 
        <add name="DBConnection" connectionString="Provider=MSDAORA.1;Data Source=DB;User Id=user;Password=pass;enlist=false;PLSQLRSet=true;OLE DB Services = -4" providerName="Oracle.DataAccess"/> 
</connectionStrings>
Sign up to request clarification or add additional context in comments.

3 Comments

I included the mentioned attribute as suggested, but still I have the same issue. Now if I change my whole approach by using OracleClient instead of OleDb, it works. Is there a way to include an Cursor type parameter using oleDb instead of OracleClient? I will display the mentioned approach (OracleClient) as an update...
You were absolutely right @Nagaraj Raveendran; after some troubleshooting, your method (adding attribute PLSQLRset) worked for me. Thanks a lot friend!
Btw in order to work I had to remove the OUT parameter declaration in my code (cmd.Parameters.Add("io_cursor",...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.