Optional sql command parameters with Oracle Client

Question

I'm trying to feed sqlcommand with more parameters than it uses. Because of that I'm getting exception ORA 01036. The description leads me to a fact that I can only pass parameters that will actually be used in the query.

I cannot find it anywhere - is it really true? It seems pretty stupid and limited to me...

@w0lf, from top of my head - because I have a generic parameters base class? Or there's a lot of parameters that I don't want to prepare by hand everytime. It WORKS for Postgres and SqlServer... — IamDeveloper, Nov 05 '12 at 15:44
@IAmDeveloper Wow, I never realized that until now. Just checked in SQL Server and it *does* work. — Cristian Lupascu, Nov 05 '12 at 15:49

score 1 · Accepted Answer · answered Nov 08 '12 at 20:56

I attempted to recreate your situation using System.Data.SqlClient.SqlConnection to connect to an Oracle database and was unsuccessful.

Using System.Data.OracleClient.OracleConnection I was able to test a query with and without parameters. The below code is successful with the uncommented 'string sql ...'. The commented 'string sql' will encounter the same error mentioned in your question.

    StringBuilder sb = new StringBuilder ();

    string sql = "SELECT CASE_ID, CLAIM_NR, CLAIM_PHS_CD FROM TABLENAME WHERE CASE_ID = :ci";
    //string sql = "SELECT CASE_ID, CLAIM_NR, CLAIM_PHS_CD FROM TABLENAME";

    using ( OracleConnection connection = new OracleConnection ( RegistryConnectionInformation.GetDBConnString () ) )
    {
        OracleParameter ci = new OracleParameter();
        ci.ParameterName = "ci";
        ci.Value = "12345";

        OracleCommand command = new OracleCommand ( sql, connection );
        command.Parameters.Add ( ci );

        connection.Open ();
        OracleDataReader reader = command.ExecuteReader ();

        try
        {
            while ( reader.Read () )
            {
                sb.Append ( string.Format ( "{0} - {1} - {2}\n", reader [ 0 ], reader [ 1 ], reader [ 2 ] ) );
            }
        }
        catch ( Exception ex )
        {
            sb.Append ( string.Format ( "{0}\n\n{1}", ex.Message, ex.StackTrace ) );
        }
        finally
        {
            reader.Close ();
        }
    }

    return sb.ToString ();

This leads me to believe you are correct: when querying Oracle, the number of paramaters passed in the command must match the number of paramaters in the query.

a. When working with Oracle, I would also recommend you keep your paramaters in the same order as their usage in the query. Depending on what DLL you are using, the paramaters do not map based on paramater name, but by order of insertion.

Dave

Optional sql command parameters with Oracle Client

1 Answers1