CODEWITHSUNDEEP
c#oracle-databaseblob
user852566
user852566

Reputation: 181

Inserting string data into blob

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...

Upvotes: 3

Views: 7546

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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.

Upvotes: 2

Related Questions