Recently I tried to use the ODP.NET provider provided by Oracle in order to connect to an Oracle 9i database. Actually, I chose ODP.NET over the Microsoft Oracle provider basically because I wanted to call a PL\SQL procedure which included a CLOB in the parameters. So, after a bit of googling, I decided to go with the safe way : use the .NET provider by Oracle. It turned out to bit a bad choice after all. The main problem was with the varchar parameters. The PL\SQL procedure is :
PROCEDURE insxml (
username IN VARCHAR2, password IN VARCHAR2,
cvxml IN CLOB,
create_date OUT DATE,
result_int OUT INTEGER
);
All the procedure does is insert do a lookup for the username and password and the inserted the CV clob in a table. The username and password columns were declared as VARCHAR2 20.
The C# code that I used is:
OracleCommand command = new OracleCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "insxml";
command.Connection = connection;
command.Parameters.Add("username", OracleDbType.Varchar2, ParameterDirection.Input);
command.Parameters["username"].Size = 20;
command.Parameters.Add("password", OracleDbType.Varchar2, ParameterDirection.Input);
command.Parameters["password"].Size = 20;
command.Parameters.Add("cvxml", OracleDbType.Clob, ParameterDirection.Input);
command.Parameters.Add("create_date", OracleDbType.Date, ParameterDirection.Output);
command.Parameters.Add("result_int", OracleDbType.Int32, ParameterDirection.Output);
try {
connection.Open();
OracleGlobalization info = connection.GetSessionInfo();
info.NumericCharacters = ".,";
connection.SetSessionInfo(info);
OracleClob xml = new OracleClob(connection);
xml.Append(...);
command.Parameters["firstname"].Value = Username;
command.Parameters["lastname"].Value = Password;
command.Parameters["cvxml"].Value = xml;
command.ExecuteNonQuery();
....(Get the returned int)...
} catch (Oracle.DataAccess.Client.OracleException oraex) {
log.Error(oraex.Message, oraex);
throw;
} finally {
connection.Close();
connection.Dispose();
}
The problem was non-deterministic… sometimes the call worked… sometimes it failed with the lovely error : Error -6502: ORA-06502: PL/SQL: numeric or value error.
At first I thought the problem was somewhere inside the procedure, so I commented out all the PL\SQL code. No luck.
Since the problem was non-deterministic I found a workaround:
use CHAR and not VARCHAR at the command parameters declaration and do a trim afterwards in the PL\SQL code.
i.e
command.Parameters.Add("username", OracleDbType.Char, ParameterDirection.Input);
command.Parameters["username"].Size = 20;
and in PL\SQL
PROCEDURE insxml (
username IN VARCHAR2, password IN VARCHAR2,
cvxml IN CLOB,
create_date OUT DATE,
result_int OUT INTEGER
) IS
v_username VARCHAR2(20) DEFAULT '';
BEGIN
v_username := TRIM(username)
...
After a while I came across this post :
http://forums.oracle.com/forums/thread.jspa?forumID=146&tstart=0&threadID=283165&trange=15
that suggests that:
1. all VARCHAR2 parameters must be declared in the right order and sizes must be declared explicitly too.
2. the return parameter MUST be added in the parameter collection BEFORE the input parameters.
I have tried it with output parameters but it seems to work only with ReturnValue parameters.
