ORA-01704: string literal too long when updating a record

I am trying to update an Oracle Database record and i keep getting this error:

ORA-01704: string literal too long 5

I looked up that error and it seems that i have a limit of 4000 charters since i am using Oracle 10g. However, the prgblem is that its the same exact data i am putting back into that record so that is why i am unsure as to why its giving me that error for the same amount of data i took out of it.

Here is my update code:

    Dim myCommand As New OracleCommand()
    Dim ra As Integer

    Try
        myCommand = New OracleCommand("Update CSR.CSR_EAI_SOURCE Set STATUS_CODE = 'Blah', COMPLETE_DATE = '', DATA = '" & theData & "' WHERE EID = '81062144'", OracleConnection)
        ra = myCommand.ExecuteNonQuery()
        OracleConnection.Close()
    Catch
        MsgBox("ERROR" & Err.Description & " " & Err.Number)
    End Try

I'm not sure if there is anything special you have to do in order to update a clob or not.

I extract the clob like so:

 Dim blob As OracleClob = dr.GetOracleClob(9)
 Dim theData As String = ""

 theData = blob.Value

And it works just fine extracting but just not putting it back in.

Any help would be great!

David

UPDATE CODE

 Dim OracleCommand As New OracleCommand()
 Dim myCommand As New OracleCommand()
 Dim ra As Integer

 While dr.Read()
    Dim blob As OracleClob = dr.GetOracleClob(9)
    Dim theData As String = ""

    theData = blob.Value
    theData = Replace(theData, "…", " ")

    Try
       Dim strSQL As String
       isConnected2 = connectToOracleDB2()
       OracleConnection.Close()

       If isConnected2 = False Then
           MsgBox("ERRORConn: " & Err.Description & " " & Err.Number)
       Else
           myCommand.Connection = OracleConnection2
           strSQL = "Update CSR.CSR_EAI_SOURCE Set STATUS_CODE = 'ERROR', COMPLETE_DATE = '', DATA = :1 WHERE EID = '" & theEID & "'"
           myCommand.CommandText = strSQL

           Dim param As OracleParameter = myCommand.Parameters.Add("", OracleDbType.Clob)
           param.Direction = ParameterDirection.Input
           param.Value = theData
           Application.DoEvents()

           ra = myCommand.ExecuteNonQuery()
           Application.DoEvents()
           OracleConnection2.Close()
           Application.DoEvents()
       End If
    Catch
       MsgBox("ERROR: " & Err.Description & " " & Err.Number)
       OracleConnection2.Close()
    End Try
 End While开发者_开发技巧

 dr.Close()
 OracleConnection.Close()

---

Do not hardcode the value into your SQL query. Instead wrap it in a parameter. Like this:

Dim strSQL As String
strSQL = "Update CSR.CSR_EAI_SOURCE Set STATUS_CODE = 'Blah', COMPLETE_DATE = '', DATA = :1 WHERE EID = '81062144'"
myCommand.CommandText=strSQL

And then:

Dim param As OracleParameter=myCommand.Parameters.Add("",OracleDbType.Clob)
param.Direction=ParameterDirection.Input
param.Value=blob.Value

You can (and should) of course add all other variables (status code, complete date, eid) of your query as parameters, too, instead of hard-coding them into your SQL.

---

Varchar2 in sql has a limitations of 4000 characters. This limitation does not apply to the data stored in a varchar column. You need to convert this to pl\sql or specify some other column type. ( I am not a php expert. So I cannot provide any sample, just the reason for your error).

Essentially you need to specify the data type as clob while executing the bind query. Otherwise it will default to varchar2 and the above limitation will apply.