Oracle provider Output Param bug?

[Guest]

Please could someone confirm whether this is a bug or not

Summar
----------
The maximum length of a varchar output parameter using the .Net Oracle native provider is 1333 characters, rather than the 4000 characters specified in the documentation. I've done extensive searching of documentation and the web and found no-one else who has encountered this problem

Passing a 4000 character string into a stored proc and reporting the length (inside the proc) works perfectly

Client machine is Windows 2003 Server, .Net 1.1
Oracle server is Solaris, Oracle 9.

Detail
-------

The following Stored Procedure was created in Oracle 9.

CREATE OR REPLACE PACKAGE BODY pkg_api_test_siz
I
PROCEDURE api_size_in_out
i_value IN VARCHAR
,o_value OUT VARCHAR

I
BEGI
o_value := i_value
END api_size_in_out
END pkg_api_test_size


The following C# code was written

static void RunInOutTest()

using(OracleConnection connection=new OracleConnection("Data Source=db1;User ID=xxx;Password=xxx;"))

connection.Open()

OracleCommand command=new OracleCommand()
command.CommandText = "pkg_api_test_size.api_size_in_out"
command.CommandType=CommandType.StoredProcedure

command.Connection = connection;


command.Parameters.Add(new OracleParameter("i_value", OracleType.VarChar, 0,
ParameterDirection.Input,false, 0,0,""
DataRowVersion.Default, Convert.DBNull)).Value = new string('R', 1334)
command.Parameters.Add(new OracleParameter("o_value", OracleType.VarChar, 4000,
ParameterDirection.Output,false, 0,0,""
DataRowVersion.Default, Convert.DBNull))

command.ExecuteNonQuery()

Console.WriteLine(command.Parameters["o_value"].Value.ToString().Length)


static void RunInOutTestOleDB()

using(OleDbConnection connection=new OleDbConnection("Provider=MSDAORA.1;Password=xxx;User ID=xxx;Data Source=db1;Persist Security Info=True"))

connection.Open()

OleDbCommand command=new OleDbCommand()
command.CommandText = "pkg_api_test_size.api_size_in_out"
command.CommandType=CommandType.StoredProcedure

command.Connection = connection;


command.Parameters.Add(new OleDbParameter("i_value", OleDbType.VarChar, 0,
ParameterDirection.Input,false, 0,0,""
DataRowVersion.Default, Convert.DBNull))
command.Parameters[0].Value = new string('R', 4000)

command.Parameters.Add(new OleDbParameter("o_value", OleDbType.VarChar, 4000,
ParameterDirection.Output,false, 0,0,""
DataRowVersion.Default, Convert.DBNull))

command.ExecuteNonQuery()

Console.WriteLine(command.Parameters["o_value"].Value.ToString().Length)



Running the OLEDB version reports a length of 4000 as expected. Running the Oracle native provider version causes the following exception

System.SystemException {"ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at \"XXX.PKG_API_TEST_SIZE\", line 31\nORA-06512: at line 1\n"} System.SystemExceptio

 

[David Sceppa]

Using version 1.1 of the Framework, I was able to pass a 4000
character string through a similar stored procedure using the following
code. The stored procedure call executes without throwing an error. After
calling the stored procedure, the value of the output parameter matches the
value of the input parameter.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.


OracleConnection cn = new OracleConnection(strConn);
cn.Open();

OracleCommand cmd = cn.CreateCommand();

cmd.CommandText = "CREATE OR REPLACE PROCEDURE BigParamString " +
"(pIn VARCHAR2, pOut OUT VARCHAR2) AS BEGIN " +
"pOut := pIn; END;";
cmd.ExecuteNonQuery();

int MAX_LENGTH = 4000;
string strParamIn = new string('Z', MAX_LENGTH);

cmd.CommandText = "BigParamString";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("pIn", OracleType.VarChar).Value = strParamIn;
cmd.Parameters.Add("pOut", OracleType.VarChar, MAX_LENGTH);
cmd.Parameters[1].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

string strParamOut = (string) cmd.Parameters["pOut"].Value;
Console.WriteLine(strParamOut.Equals(strParamIn));

cn.Close();

 

[scorpion53061]

David,

Are you writing a ADO.NET 2.0 book and do you need a proof reader like
of like what we did for Rebecca?

 

[David Sceppa]

Yes, I am planning a second edition of the ADO.NET book. You're
welcome to contact Microsoft Press regarding technical editing and
proofreading opportunities. That's something they've handled in the past.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[scorpion53061]

Nah. It is difficult to get a human being to respond over there.

Looking forward to the book though. Tell me did they do more (improve)
with datatable compute and filter methods in this version of ADO.NET?