Inserting string data into blob

Question

Hi I am having a problem trying to insert string data into a blob in Oracle using C#. I have used this statement in "Toad" in a sql editor and it inserted the row without any problem so that is where the code is coming from.

INSERT INTO STORAGE ( STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID,       PARM_VAL_TXT,APPL_ITEM_TXT) VALUES (STRG_ID_SEQ.nextval, 2, 1, 1, 'cow',utl_raw.cast_to_raw('some string'))

the table is:

strg_id number, Appl_id number, task_id number , Parm_val_text varchar2(250), Appl_item_txt blob

the string data will be about 42k json file, but for the moment i can not even get a 5 character string to work. I have tried about everything i can think of but i am new to Oracle, been around Sql Server mainly. i found a few things on the web and those are what you see below. I have tried:

 try
    {
        using (connection)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.BindByName = true;
                cmd.CommandText =
                    "INSERT INTO CDP.STORAGE (STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID, PARM_VAL_TXT,APPL_ITEM_TXT) " +
                    " VALUES (:parmStorageId, :parmApplId, :parmTaskId, :parmTypeId, :ParmValTxt, :parmBlob)";
                cmd.Parameters.Add("parmStorageId", "CDP.STRG_ID_SEQ.nextval");
                cmd.Parameters.Add("parmApplId", appId);
                cmd.Parameters.Add("parmTaskId", taskId);
                cmd.Parameters.Add("parmTypeId", parmTypeId);
                cmd.Parameters.Add("ParmValTxt", parmValue);

                OracleParameter param = cmd.Parameters.Add("parmBlob", OracleDbType.Blob); //Add the parameter for the blobcolumn

                string tim = "this is some very large string 42k characters";

           //     byte[] bytes = new byte[tim.ToCharArray().Length * sizeof(char)];


                byte[] bytes = System.Text.Encoding.UTF8.GetBytes(tim);



                System.Buffer.BlockCopy(tim.ToCharArray(), 0, bytes, 0, bytes.Length);

                param.Value = bytes; //Asign the Byte Array to the parameter

                cmd.ExecuteNonQuery();
            }

            connection.Close();
        }
    }
    catch (Exception ex)
    {
        Log.Error(ex);
        return false;
    }

this is giving me the following error.

Oracle.DataAccess.Client.OracleException ORA-01722: invalid number    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at SeasonsSvc.Models.SeasonsOracleRepository.BlobInsert(Int32 appId, Int32 taskId, Int32 parmTypeId, String parmValue, SeasonsReturnData myDataSet) in c:\Users\e048014\Documents\Visual Studio 2012\Projects\SeasonsSvc\SeasonsSvc\Models\SeasonsOracleRepository.cs:line 174

Then i tried the below:

 public bool BlobInsert(int appId, int taskId, int parmTypeId, string parmValue, SeasonsReturnData myDataSet)
        {
            var connection = _dataUtility.GetOracleConnection("OracleConn");

            StringBuilder sb = new StringBuilder();

            var serializer = new JavaScriptSerializer();

           serializer.Serialize(myDataSet,sb);

               try
               {
                   using (connection)
                   {
                       using (var cmd = connection.CreateCommand())
                       {
                           cmd.CommandType = CommandType.Text;
                           cmd.BindByName = true;
                           cmd.CommandText =
                               "INSERT INTO STORAGE (STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID, PARM_VAL_TXT,APPL_ITEM_TXT) " +
                               " VALUES (STRG_ID_SEQ.nextval,2, 1, 1, 'cows', :parmBlob)";

                           cmd.Parameters.Add("parmBlob", "utl_raw.cast_to_raw('" + sb.ToString() + "')");

                           cmd.ExecuteNonQuery();
                       }

                       connection.Close();
                   }
               }
               catch (Exception ex)
               {
                   Log.Error(ex);
                   return false;
               }  
           return true;
        }

this gives me the following error:

{Oracle.DataAccess.Client.OracleException ORA-01461: can bind a LONG value only for insert into a LONG column    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at SeasonsSvc.Models.SeasonsOracleRepository.BlobInsert(Int32 appId, Int32 taskId, Int32 parmTypeId, String parmValue, SeasonsReturnData myDataSet) in c:\Users\e048014\Documents\Visual Studio 2012\Projects\SeasonsSvc\SeasonsSvc\Models\SeasonsOracleRepository.cs:line 118}

I am stuck, i can get the utl_raw.cast_to_raw('some string')) to work in the above insert statement in Toad, but if i copy the json in the place of "some string" I get an error in both Toad and Visual Studio that says the string is too long. my guess it is the 4000 character limit and that is why i am trying to bind the variables.

thanks for any help, ideas, ect...

have you stepped through the code using the debugger..? if so what is the value of sb after you step pass this line serializer.Serialize(myDataSet,sb); — MethodMan
– MethodMan, Commented Jun 26, 2015 at 20:53
Why are you storing a long string in a BLOB which is for binary data rather than a CLOB which is for character data? — Justin Cave
– Justin Cave, Commented Jun 26, 2015 at 21:07
In the first example I suggest putting the invocation of CDP.STRG_ID_SEQ.nextval into the SQL statement instead of trying to pass CDP.STRG_ID_SEQ.nextval as the value of a parameter. Similarly, in the second example put the call to utl_raw.cast_to_raw into the SQL itself instead of trying to have the parameter somehow invoke a procedure call, which I've never seen done. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jun 27, 2015 at 3:08
What are you using, ODP.NET or microsoft oracle provider? As they say, if you saving text than use CLOB - will be no issue just saving long text — T.S.
– T.S., Commented Jun 28, 2015 at 4:34
i am a noob with oracle so bear with me. i was under the impression that Clob could only support up to 4000 characters. The size of my data can be as large as 50,000 characters. is this incorrect? — user852566
– user852566, Commented Jul 6, 2015 at 20:49

Ponder Stibbons · Accepted Answer · 2015-06-29 12:11:16Z

ORA-01722: invalid number is not related with your BLOB column, but with the way you passed parmStorageId parameter. You should move it directly into CommandText like in C# code below (this solution worked for me, I also removed System.Buffer.BlockCopy ... line)

using (var cmd = connection.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.BindByName = true;
    cmd.CommandText =
        "INSERT INTO STORAGE (STRG_ID, APPL_ID, TASK_ID, PARM_TYPE_ID, PARM_VAL_TXT,APPL_ITEM_TXT) " +
        " VALUES (STRG_ID_SEQ.nextval, :parmApplId, :parmTaskId, :parmTypeId, :ParmValTxt, :parmBlob)";
    cmd.Parameters.Add("parmApplId", appId);
    cmd.Parameters.Add("parmTaskId", taskId);
    cmd.Parameters.Add("parmTypeId", parmTypeId);
    cmd.Parameters.Add("ParmValTxt", parmValue);

    OracleParameter param = cmd.Parameters.Add("parmBlob", OracleDbType.Blob); 
    string tim = "this is some very large string 42k characters";
    byte[] bytes = System.Text.Encoding.UTF8.GetBytes(tim);
    param.Value = bytes; 

    cmd.ExecuteNonQuery();
}

The most common way to fill fields like strg_id is by use of triggers, in your case you could create this trigger:

create or replace trigger strg_id_trg 
before insert on storage  
for each row
begin
  :new.strg_id := strg_id_seq.nextval;
end strg_id_trg;

and forget about filling this data in code. Also please consider changing your BLOB field to CLOB, like already suggested in comments. BLOB is designed for binary data, images, audio etc.

Collectives™ on Stack Overflow

Inserting string data into blob

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related