What is wrong with my Oracle RAW return param?

1

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.

oracle
exception
stored-procedures
parameters
asked on Stack Overflow Jun 7, 2019 by ProfK • edited Jun 10, 2019 by ProfK

2 Answers

0

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

answered on Stack Overflow Jun 10, 2019 by Tejash • edited Jun 13, 2019 by Tejash
0

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 CreateParameterRawmethod. By this way CreateParameterRawbecomes the whole responsible about generating the parameters.


Credits:

answered on Stack Overflow Jun 13, 2019 by Mehmet Kaplan

User contributions licensed under CC BY-SA 3.0