Hi,
We filed already similar problem with previous version of ODP.NET provider:
190780
Someone said in this thread: "The fix will be available in the next release" - but the problem still exists, not sure if this is the same error.
Problem:
I have stored procedure that has clob as parameter. I have serious issues passing empty sting as parameter of the stored procedure using ODP.NET.
I created below sample code that can reproduce the issue. The stored procedure throws:
ORA-22275: invalid LOB locator specified
ORA-06512: at CLOBTEST_INSERT
The same happens with nclob parameter type.
Can you look into this issue. Should I modify my code to make it working or this is problem inside Oracle driver?
Best Regards,
Marcin Wodecki
.NET framework 1.1
Database version 9.2.0.1.0
Oracle9i Data Provider for .NET = 9.2.0.401
NLS_CHARACTERSET = AL32UTF8
NLS_NCHAR_CHARACTERSET = AL16UTF16
Preparation Scripts:
CREATE TABLE CLOBTEST(ID NUMBER(4) PRIMARY KEY,TEST CLOB);
CREATE OR REPLACE PROCEDURE CLOBTEST_INSERT(
i_ID IN number,
i_Test IN clob)
IS
BEGIN
INSERT INTO CLOBTEST(ID, TEST)
VALUES(i_ID, i_Test);
END CLOBTEST_INSERT;
Sample C# code:
OracleConnection con = new OracleConnection("*** your connection here ****");
try
{
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "CLOBTEST_INSERT";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter p1=new OracleParameter("i_ID", OracleDbType.Decimal);
p1.Value=1;
OracleParameter p2= new OracleParameter("i_Test", OracleDbType.Clob);
p2.Value=""; // this causes exception while executing
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Warning: {0}", ex.Message);
}
finally
{
con.Close();
con.Dispose();
}