multiple parameter issue with direct connection

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
powder
Posts: 2
Joined: Tue 10 Jul 2007 15:28

multiple parameter issue with direct connection

Post by powder » Tue 10 Jul 2007 15:51

Sorry if this has been documented elsewhere, I looked but could not find a solution or explanation.

I've noticed a case where a query that references a bind variable multiple times will succeed if connecting using the OCI but will fail if using a direct connection. I have included a small program illustrating the problem.

The query reference the SCHEMA_NAME parameter twice. Under an OCI connection the query executes normally but under a direct connection the exception "ORA-01008: not all variables bound" is raised.

I'm using version 4.0.13 against Oracle 10.2.0.1.0 (XE) on Windows.

Cheers,

Code: Select all

using System;
using System.Collections.Generic;
using System.Text;

using CoreLab.Oracle;

namespace BindVariablesTest {

    class Program {
    
        static void Main(string[] args) {

            // succeeds
            OracleConnection oci = new OracleConnection();
            oci.UserId = "system";
            oci.Password = "oracle";
            oci.Server = "xe";
            oci.Open();
            testParameters(oci);
            oci.Close();

            // fails
            OracleConnection direct = new OracleConnection();
            direct.UserId = "system";
            direct.Password = "oracle";
            direct.Direct = true;
            direct.Server = "localhost";
            direct.Port = 1521;
            direct.Sid = "xe";
            direct.Open();
            testParameters(direct);
            direct.Close();

        }

        static void testParameters(OracleConnection connection) {

            StringBuilder builder = new StringBuilder();
            builder.AppendLine("SELECT      DISTINCT ");
            builder.AppendLine("            OWNER AS SCHEMA_NAME, ");
            builder.AppendLine("            TABLE_NAME ");
            builder.AppendLine("FROM        ALL_TAB_COLUMNS ");
            builder.AppendLine("WHERE       ( OWNER = :SCHEMA_NAME AND COLUMN_NAME = :COLUMN_NAME ) ");
            builder.AppendLine("AND NOT	    ( OWNER = :SCHEMA_NAME AND TABLE_NAME = :TABLE_NAME ) ");

            String sql = builder.ToString();

            OracleCommand select = new OracleCommand(sql, connection);
            select.Parameters.AddWithValue(":SCHEMA_NAME", "HR");
            select.Parameters.AddWithValue(":TABLE_NAME", "EMPLOYEES");
            select.Parameters.AddWithValue(":COLUMN_NAME", "JOB_ID");
            
            OracleDataReader reader = select.ExecuteReader();
            while(reader.Read()){
                System.Console.WriteLine(reader.GetString(0));
            }
            reader.Close();
           
        }

    }

}

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 11 Jul 2007 09:48

We are investigating this problem.
Look forward to hearing from me again.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Sat 14 Jul 2007 10:19

Unfortunately, we cannot fix this. You should not repeat parameters.

Post Reply