6 Replies Latest reply on Apr 15, 2010 3:23 AM by 766797

    ORA-01461 can bind a LONG value only for insert into a LONG

    427533
      I Use ODP.net Insert and update Image files to Oralce blob columns(Oracle8i Server,ora92 client),
      When the files larger than 4000 characters,It doesn't work.error:ORA-01461 can bind a LONG value only for insert into a LONG

      My Vb.net code:
      Dim dlg As OpenFileDialog
      Dim strFileName() As String
      Dim fsBLOB As System.IO.FileStream
      Dim Adapter As OracleDataAdapter
      Dim ds As New DataSet
      Dim row As DataRow
      Dim cb As OracleCommandBuilder
      Dim bytes() As Byte


      dlg = New OpenFileDialog

      dlg.Filter = "Import File (*.*)|*.*"

      Adapter = New OracleDataAdapter("select * from a_blob_contents where 1=0", Me.OraConnection)

      Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

      Adapter.FillSchema(ds, SchemaType.Source, "BLOB")

      cb = New OracleCommandBuilder(Adapter)

      If dlg.ShowDialog() = DialogResult.OK Then

      strFileName = dlg.FileNames()

      Dim loopCount As Integer

      For loopCount = 0 To strFileName.GetLength(0) - 1
      row = ds.Tables("BLOB").NewRow()
      row("BLOB_ID") = GetMaxBh("A_BLOB_CONTENTS", "BLOB_ID")
      row("FILE_ID") = FileID
      row("FILE_TYPE") = FileType
      Dim file_name As String
      file_name = strFileName(loopCount)
      file_name = file_name.Substring(file_name.LastIndexOf("\") + 1, file_name.Length - file_name.LastIndexOf("\") - 1)
      row("BLOB_NAME") = file_name
      fsBLOB = New FileStream(strFileName(loopCount), FileMode.Open, FileAccess.Read)
      ReDim bytes(fsBLOB.Length)
      fsBLOB.Read(bytes, 0, System.Convert.ToInt32(fsBLOB.Length))
      fsBLOB.Close()
      row("blob_contents") = bytes
      row("user_code") = Global.user_code
      row("Operate_date") = Now

      ds.Tables("BLOB").Rows.Add(row)
      Next

      Try

      Adapter.Update(ds, "BLOB")

      Catch ex As Exception
      MessageBox.Show(ex.Message)
      End Try
      End If


      I'll appreciate for anyone who can help me solve this problem.
        • 1. Re: ORA-01461 can bind a LONG value only for insert into a LONG
          24208
          Hi Zhenquan,

          The issue is that you can not direct insert more than 4K (as you have discovered). Two methods I use in this situation are:

          1) sql statement or procedure that returns a lob locator into an ODP.NET OracleBlob parameter such as:

          insert into the_table (primary_key, blob_column) values (pk_value, empty_blob()) returning blob_column into :blob_parameter.

          Once you have the OracleBlob you should be able to write/update the data.

          2) use an anonymous pl/sql block or package that uses the dbms_lob supplied package to work with the LOB.

          Here is a simple console example...

          SQL> create table blob_test
          2 (
          3 blob_id number,
          4 blob_data blob,
          5 constraint blob_test_pk primary key (blob_id)
          6 )
          7 tablespace users;

          Table created.

          Sub Main()
          Dim strFileName As String = "c:\temp\testimage.jpg"
          Dim fsBlob As FileStream = New FileStream(strFileName, FileMode.Open)
          Dim bytes(fsBlob.Length) As Byte
          fsBlob.Read(bytes, 0, fsBlob.Length)
          fsBlob.Close()

          Dim con As OracleConnection = New OracleConnection("User Id=/; Data Source=lt8i")
          con.Open()

          Dim trans As OracleTransaction = con.BeginTransaction()

          Dim sql As String = "insert into blob_test (blob_id, blob_data) values (1,empty_blob()) returning blob_data into :blob_parameter"

          Dim blob_parameter As OracleParameter = New OracleParameter
          blob_parameter.OracleDbType = OracleDbType.Blob
          blob_parameter.Direction = ParameterDirection.InputOutput

          Dim cmd As OracleCommand = New OracleCommand(sql, con)
          cmd.Parameters.Add(blob_parameter)

          cmd.ExecuteNonQuery()

          Dim blob As OracleBlob = blob_parameter.Value

          blob.Write(bytes, 0, bytes.Length)

          sql = "update blob_test set blob_data = :blob_parameter where blob_id = 1"

          cmd.CommandText = sql

          blob_parameter.Value = blob

          cmd.ExecuteNonQuery()

          trans.Commit()
          End Sub

          This sample was tossed together pretty quickly, so it is not a complete, bullet-proof sample, but hopefully it will give you what you need to get started. The 'testimage.jpg' file is just over 5MB in size.

          Hope that helps,

          Mark

          =======================================
          Mark A. Williams
          Oracle DBA
          Author, Professional .NET Oracle Programming
          http://www.apress.com/book/bookDisplay.html?bID=378
          • 2. Re: ORA-01461 can bind a LONG value only for insert into a LONG
            420698
            I am having the same problem during executing an update which tries to update the contents of six Varchar2(2000) fields, I am using the oracle driver that shipped with .NET and C#.

            Any ideas?
            • 3. Re: ORA-01461 can bind a LONG value only for insert into a LONG
              pwelker
              What's your serverside charset? Something like UTF8, AL32UTF8 or similar? Maybe you hit that BUG 1400539 where you can not insert more than the maximal number of possible bytes (i.e. 1333 chars = 3999 Bytes).

              Try one of the following workarounds:
              - Use the database character set also as the client character set
              - Decrease the size of the columns
              - Do not use the multibyte character set as the database character set :-(
              - use 10g where this is "fixed"

              I don't know any fix for 9i or 8i. If you have access to Metalink, see note 241358.1 for detailed information.

              Good luck!
              Peter
              • 4. Re: ORA-01461 can bind a LONG value only for insert into a LONG
                531664
                I am having the same problem during executing an
                update which tries to update the contents of six
                Varchar2(2000) fields, I am using the oracle driver
                that shipped with .NET and C#.

                Any ideas?
                Did you solve your problem? I've been struggling with the same problem for some time now.
                • 5. Re: ORA-01461 can bind a LONG value only for insert into a LONG
                  738920
                  I having the same problem when I insert the data to clob cloumn,
                  database environment:
                  Database Oracle : 10g
                  C# + NHibernate + Oracle10gClient,

                  who have the good solution?

                  3Q

                  my contact email: flyear.cheng@gmail.com.
                  • 6. Re: ORA-01461 can bind a LONG value only for insert into a LONG
                    766797
                    Hi Cheng, i have the exact same setup as you, same issue, has anyone resolved this ?

                    Cheers