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();
}
}
}