I have a stored procedure with the following signature and local variables:
PROCEDURE contract_boq_import(i_project_id IN RAW,
i_boq_id IN RAW,
i_master_list_version IN NUMBER,
i_force_update_if_exists IN BOOLEAN,
i_user_id IN NUMBER,
o_boq_rev_id OUT RAW) AS
v_contract_id RAW(16);
v_contract_no VARCHAR2(100);
v_series_rev_id_count NUMBER(1);
v_project_id_count NUMBER(5);
v_now DATE;
v_boq_import_rev_id RAW(16);
v_master_project_id RAW(16);
v_prj_duplicate_items VARCHAR2(1000) := '';
I set up an output parameter using one of our DAL utilities:
var revParam = new byte[16];
dataHandler.CreateParameterRaw("o_boq_rev_id", revParam).Direction = ParameterDirection.Output;
Where CreateParameterRaw
is declared as:
public DbParameter CreateParameterRaw(string name, object value)
{
OracleParameter oracleParameter = new OracleParameter();
oracleParameter.ParameterName = name;
oracleParameter.OracleDbType = OracleDbType.Raw;
oracleParameter.Value = value;
this.Parameters.Add((DbParameter) oracleParameter);
return (DbParameter) oracleParameter;
}
Then when I execute the procedure with ExecuteNonQuery
I get the following error:
Oracle.ManagedDataAccess.Client.OracleException
HResult=0x80004005
Message=ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "ITIS_PRCDRS.PA_PRJ_IMP", line 1235
The exception is thrown on line 1235:
o_boq_rev_id := v_boq_import_rev_id;
As you can see from the procedure declaration above, v_boq_import_rev_id
has type RAW(16)
and o_boq_rev_id
has type OUT RAW
, so why should the assignment on line 1235 fail? What am I doing wrong?
PS: The proc executes fine when I call it in plain PL/SQL.
This is an interesting problem with a weird solution.
Actually while using the RAW in output parameter you MUST provide some buffer space for it when adding this parameter.
Can you please provide some buffer space for this variable and try something like the following:
byte[] RAWPlaceholder = new byte[16];
cmd.AddParameter(new OracleParameter("o_boq_rev_id",
OracleDbType.Raw,
16,
RAWPlaceholder,
ParameterDirection.Output);
Please share the result of the aforementioned exercise.
Thanks
In OracleParameter
the default size is 0 for the parameters that may have size values. (Official reference here.)
That is why you need to modify your method which generates the raw values. Below you can find the modified method:
public DbParameter CreateParameterRaw(string name, object value, int parameterSize)
{
OracleParameter oracleParameter = new OracleParameter();
oracleParameter.ParameterName = name;
oracleParameter.OracleDbType = OracleDbType.Raw;
oracleParameter.Value = value;
oracleParameter.Size = parameterSize; /* THIS IS THE ADDED PARAMETER */
this.Parameters.Add((DbParameter) oracleParameter);
return (DbParameter) oracleParameter;
}
And as a result you can pass the size while calling CreateParameterRaw
as you did in your existing code:
var revParam = new byte[16];
/* CHECK THE 16 value in the parameters that are sent to CreateParameterRaw */
dataHandler.CreateParameterRaw("o_boq_rev_id", revParam, 16).Direction = ParameterDirection.Output;
Additional suggestion: In order to keep apples with apples, I would suggest you can take Direction
parameter also into the CreateParameterRaw
method. By this way CreateParameterRaw
becomes the whole responsible about generating the parameters.
Credits:
User contributions licensed under CC BY-SA 3.0