1 Reply Latest reply: Apr 25, 2014 1:14 AM by 8547569b-6bca-48e6-8e11-bf988caa9c1e RSS

    Pass a byte array to oracle stored procedure

    8547569b-6bca-48e6-8e11-bf988caa9c1e

      I have one problem with passing byte array into Oracle stored procedure as input parameter using odp.net.

      Here is stored procedure signature:

      SOMEPROCEDURE(session IN NUMBER, data IN RAW)

      Here is C# code, which call procedure:

      var cmd = new OracleCommand("SOME_PROCEDURE", _connection);

      cmd.CommandType = CommandType.StoredProcedure;

      var bt = new byte[]{1,68,0,83,128,1};

      OracleParameter sessionId = new OracleParameter("dbSessionId", OracleDbType.Decimal, new OracleDecimal(_dbSessionId), ParameterDirection.Input);

      OracleParameter data = new OracleParameter("statusData", OracleDbType.Raw, new OracleBinary(bt), ParameterDirection.Input);

      cmd.Parameters.Add(sessionId);

      cmd.Parameters.Add(data);

      cmd.ExecuteNonQuery();

      This code fails(stored procedure throws exception, that can't get data), because in byte array thereis number 128!, if I chage 128 on another number,less 128, it works fine!

      What should i do?

        • 1. Re: Pass a byte array to oracle stored procedure
          8547569b-6bca-48e6-8e11-bf988caa9c1e

          After some experiments I have found solution. At first, You can't pass to stored procedure array of bytes via odp.net, if this array contains values great than 127.

          So, solution: 1. Create wrapper procedure

          procedure SOME_PROCEDURE_WRAPPER (p_session_id in number, p_data  in varchar2)

          is v_data raw(1024);

          rawdata raw(1024);

          rawlen number;

          hex varchar2(32760);

          i number;

          begin

            rawlen := length(p_data);

          i := 1;

          while i <= rawlen-1

            loop

          hex := substrb(p_data,i,2);

               rawdata := rawdata || HEXTORAW(hex);

               i := i + 2; end loop;

               SOME_PROCEDURE(p_session_id , rawdata);

          end;

          2 Then need to modify C# code in this way:

          var cmd = new OracleCommand("SOME_PROCEDURE_WRAPPER", _connection);

          cmd.CommandType = CommandType.StoredProcedure;

          string @string = statusData.ToHexString();

          OracleParameter sessionId = new OracleParameter("dbSessionId", OracleDbType.Decimal, new OracleDecimal(_dbSessionId), ParameterDirection.Input);

          OracleParameter data = new OracleParameter("statusData", OracleDbType.Varchar2,@string.Length, new OracleString(@string), ParameterDirection.Input);

          cmd.Parameters.Add(sessionId);

          cmd.Parameters.Add(data);

          cmd.ExecuteNonQuery();

          where

          public static string ToHexString(this byte[] bytes)

          {

               if(bytes == null || bytes.Length == null)

                    return string.Empty;

               StringBuilder hexStringBuilder = new StringBuilder();

               foreach (byte @byte in bytes)

               {

                    hexStringBuilder.Append(@byte.ToString("X2"));

                }

                return hexStringBuilder.ToString();

          }