Wednesday, January 30, 2013

ORA-06502 with RAW OUT parameter called from ODP.NET

An interesting problem popped up today while calling a PL/SQL stored procedure from ODP.NET. It turns out that when you have an output RAW parameter you MUST provide some buffer space for it while adding this parameter, even though the parameter is OUTPUT only.

Here's a part of the definition of the stored procedure in Oracle:

PROCEDURE bla-bla(o_user_guid OUT RAW(16)) ...

Here's how it's called from C#:

using (OracleCommand cmd = con.CreateCommand())
{
     ...
     cmd.AddParameter(new OracleParameter("o_user_guid", 
                                                                   OracleDbType.Raw,  
                                                                   ParameterDirection.Output));
}

Strangely enough this code returns the following error:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

Adding the size of 16 to the creation of the parameter does not help.


What helps though is adding a dummy buffer while creation of the parameter as follows:

using (OracleCommand cmd = con.CreateCommand())
{
     ...
     byte[] userGuidPlaceholder = new byte[16];

     cmd.AddParameter(new OracleParameter("o_user_guid", 
                                                                  OracleDbType.Raw,  
                                                                  16, 
                                                                  userGuidPlaceholder, 
                                                                  ParameterDirection.Output);
}

Weird.

No comments:

Post a Comment