Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Unanswered: 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:
    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:
    Code:
     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

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by StealthRT View Post
    I'm not sure if there is anything special you have to do in order to update a clob or not.
    Nothing special, just do not hardcode the CLOB value as part of an UPDATE statement (string); pass it as a bind variable (parameter) instead.

    I have never worked with Visual Basic (if I deduced the calling environment right); anyway according to its documentation, it seems to be achievable by passing it as Parameters property. It is described e.g. here: http://msdn.microsoft.com/en-us/libr...arameters.aspx (although the example seems to represent different command, the approach of passing a variable should be same).

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Thanks for the insite there, flyboy.. This is the code i am using to update it:
    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
            strSQL = "Update CSR.CSR_EAI_SOURCE Set STATUS_CODE = 'ERROR', COMPLETE_DATE = '', DATA = :1 WHERE EID = '" & dr(1) & "'"
            myCommand.CommandText = strSQL
    
            Dim param As OracleParameter = myCommand.Parameters.Add("", OracleDbType.Clob)
            param.Direction = ParameterDirection.Input
            param.Value = theData
    
            ra = myCommand.ExecuteNonQuery()
        Catch
            MsgBox("ERROR" & Err.Description & " " & Err.Number)
        End Try
       End While
    
    dr.Close()
    OracleConnection.Close()
    However, now i am getting the error:
    Operation is not valid due to the current state of the object
    Any ideas as to why that is?

    David

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    No idea as I have no experience with VB. Maybe you should set OracleCommand.Parameters variable instead of the local one (or use OracleCommand.CreateParameter). Are you able to at least find out, which command threw this?

    Maybe using reverse approach than obtaining that CLOB, EID, and whatever else from DR could be successful.

    Or, do not pass data from Oracle to VB and back and do it in one SQL statement call. If DR values come from the updated row, it would be as simple as
    Code:
    Update CSR.CSR_EAI_SOURCE
    Set STATUS_CODE = 'ERROR',
        COMPLETE_DATE = '',
        DATA = REPLACE( DATA, '', ' ' )
    WHERE <condition for updated rows>
    You could probably get rid of the whole loop obtaining those DR values (which you did not post) and put the outer SELECT condition to the WHERE clause directly.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What line is that error coming from? Is the data reader dr open?

  6. #6
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    OK i fixed the
    Operation is not valid due to the current state of the object
    problem but now i am up against this error:
    The CLR has been unable to transition from COM context 0x3337fa8 to COM context 0x3338118 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.
    I've tried to uncheck the ContextSwitchDeadlock and run it but it still hangs on the
    Code:
    ra = myCommand.ExecuteNonQuery()
    line.

    My current code looks like this now:
    Code:
    Dim OracleCommand As New OracleCommand()
    Dim myCommand As New OracleCommand()
    Dim ra As Integer
    
    OracleCommand = New OracleCommand("SELECT       * " & _
                                 "FROM         TableNameHere C " & _
                                 "WHERE        STATUS_CODE = 'ERROR'", OracleConnection)
    
    dr = OracleCommand.ExecuteReader
    
    hasRows = dr.HasRows
    
    If hasRows Then
    
    While dr.Read()
        Dim blob As OracleClob = dr.GetOracleClob(9)
        Dim theData As String = ""
        Dim theEID As String = dr(1)
    
        theEID = Regex.Replace(theEID, "[^\d]", "")
    
        theData = blob.Value
        theData = Replace(theData, "…", " ")
    
        Try
            Dim strSQL As String
            isConnected2 = connectToOracleDB2()
    
            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 = REPLACE(DATA, '…', ' ') 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()
    Any suggestions that would help me find out why its doing this?

    David
    Last edited by StealthRT; 03-17-11 at 12:23.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    From VB point view, you should rather search in VB related pages.

    From database point of view, it seems (although TableNameHere does not sound like real table name) that you are just playing ping-pong with it (fetch rows, change and store them back). If so, it would be more efficient to do everything on database side with one command. Based on given code, it should look like this:
    Code:
    Update CSR.CSR_EAI_SOURCE ces
    Set (STATUS_CODE, COMPLETE_DATE, DATA) =
        (SELECT 'ERROR', '', REPLACE( c.<CLOB column from C>, '', ' ' )
         FROM TableNameHere C
         WHERE regexp_replace( c.<first_column>, '[^0-9]', '' ) = ces.EID
           AND c.STATUS_CODE = 'ERROR')
    WHERE EXISTS ( SELECT 1
                   FROM TableNameHere C
                   WHERE regexp_replace( c.<first_column>, '[^0-9]', '' ) = ces.EID
                     AND c.STATUS_CODE = 'ERROR')
    Anyway (from performance reason), you should use parameters instead of concatenation. The only exception would be in case of literals or constants (e.g. string 'ERROR' - it is good to pass it as a string). But, theEID should be passed as parameter.

  8. #8
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by flyboy View Post
    From VB point view, you should rather search in VB related pages.

    From database point of view, it seems (although TableNameHere does not sound like real table name) that you are just playing ping-pong with it (fetch rows, change and store them back). If so, it would be more efficient to do everything on database side with one command. Based on given code, it should look like this:
    Code:
    Update CSR.CSR_EAI_SOURCE ces
    Set (STATUS_CODE, COMPLETE_DATE, DATA) =
        (SELECT 'ERROR', '', REPLACE( c.<CLOB column from C>, '', ' ' )
         FROM TableNameHere C
         WHERE regexp_replace( c.<first_column>, '[^0-9]', '' ) = ces.EID
           AND c.STATUS_CODE = 'ERROR')
    WHERE EXISTS ( SELECT 1
                   FROM TableNameHere C
                   WHERE regexp_replace( c.<first_column>, '[^0-9]', '' ) = ces.EID
                     AND c.STATUS_CODE = 'ERROR')
    Anyway (from performance reason), you should use parameters instead of concatenation. The only exception would be in case of literals or constants (e.g. string 'ERROR' - it is good to pass it as a string). But, theEID should be passed as parameter.
    Ok, i took you advice and i am testing that query out in TOAD. However i get this error:
    ORA-00904: "REGEXP_REPLACE": invalid identifier
    My query looks like this:
    Code:
    Update CSR.CSR_EAI_SOURCE ces 
    Set (STATUS_CODE, COMPLETE_DATE, DATA) =
        (SELECT 'ERROR', '', REPLACE( c.Data, '', ' ' ) 
         FROM CSR.CSR_EAI_SOURCE C
         WHERE REGEXP_REPLACE( c.EID, '[^0-9]', '' ) = ces.EID
           AND c.STATUS_CODE = 'ERROR')
    WHERE EXISTS ( SELECT 1
                   FROM CSR.CSR_EAI_SOURCE C
                   WHERE REGEXP_REPLACE( c.EID, '[^0-9]', '' ) = ces.EID
                     AND c.STATUS_CODE = 'ERROR');
    That error is on the line:
    Code:
    WHERE REGEXP_REPLACE(
    David

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect you are running older, unsupported version
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    I suspect you are running older, unsupported version
    Its Oracle 10g...

    David

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    Regular expressions were introduced in Oracle version 10gR1, so you must have older version. I am not aware of any standard function which would filter numbers from string (are those tables really joined by trimming non-numeric characters from one column? are there any constraints, at least primary keys?), so you would have to create your own and use it instead of REGEXP_REPLACE. Or, redesign the data model so the tables would be joined properly.

  12. #12
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Ok, doing it the SQL way but i seem to be right back to my ContextSwitchDeadlock error:
    Code:
    Dim OracleCommand As New OracleCommand()
     Dim ra As Integer
    
     OracleCommand = New OracleCommand("UPDATE   CSR.CSR_EAI_SOURCE ces " & _
                                          "SET      (STATUS_CODE, COMPLETE_DATE, DATA) = " & _
                                                    "(SELECT    'ERROR', '', REPLACE(REPLACE(c.Data, '…', ' ' ), '’','''') " & _
                                                    "FROM       CSR.CSR_EAI_SOURCE C " & _
                                                    "WHERE      (c.EID = ces.EID) " & _
                                                    "AND        c.STATUS_CODE = 'ERROR') " & _
                                          "WHERE    EXISTS (SELECT 1 " & _
                                          "FROM     CSR.CSR_EAI_SOURCE C " & _
                                          "WHERE    (c.EID = ces.EID) " & _
                                          "AND      c.STATUS_CODE = 'ERROR')", OracleConnection)
    
      Try
          ra = OracleCommand.ExecuteNonQuery()
          OracleConnection.Close()
          MsgBox("done")
      Catch ex As Exception
          MsgBox("ERROR: " & Err.Description & " " & Err.Number)
          OracleConnection.Close()
      End Try
    It stays on the ra = OracleCommand.ExecuteNonQuery() continuously until i get that error...

    David

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Firstly, I noticed that source and destination table is the same one. Very strange design. Anyway, there are two possible reasons for "long" (up to forever) running query:

    performance problem - quite complex task requiring knowledge of structures (tables, constraints, indexes) and data (number of rows, distribution of values); basic steps for detection are described e.g. here: http://forums.oracle.com/forums/thre...812597#1812597; for given case, index on EID could help

    lock problem - somebody else is updating affected row(s); its detection is described e.g. here: http://asktom.oracle.com/pls/apex/f?...D:839412906735

  14. #14
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    It is only checking for "ERRORS" which normally wont be over 10 records anyways.

    David

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by StealthRT View Post
    It is only checking for "ERRORS" which normally wont be over 10 records anyways.
    Out of how many? What does an explain plan say? As you changed the join condition, you can use the first query (without subselects). Without index on that column, it would have to full scan anyway (e.g. in 1000 page book, there is a word fly 10 times; how long will it take to find all its occurrences without having them in index?).

    My guess is although that the UPDATEd row is locked by another database session. It could be identified with SQL in the link I posted...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •