Additional sql command options with Oracle Client

Question

I am trying to pass sqlcommand with more parameters than it uses. Because of this, I get an ORA 01036 exception. The description leads me to the fact that I can only pass parameters that will actually be used in the request.

I can’t find anything - is that true? It seems pretty dumb and limited to me ...

Answer 1

• I tried 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 the query with and without parameters. The code below is successful with uncommented 'string sql ...'. The commented line "sql" will meet the same error as 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 makes me think that you are right: in an Oracle query, the number of parameters passed in the command must match the number of parameters in the query.

  a. When working with Oracle, I would also recommend storing your parameters in the same order as their use in the query. Depending on which DLL you are using, the options are not displayed based on the name paramater, but in insertion order.

Dave