Microsoft KB Archive/233515

= PRB: Error "String Literal Too Long" using Update or Insert on Oracle Table =

Article ID: 233515

Article Last Modified on 5/7/2001

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual C++ 5.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Enterprise Edition

-



This article was previously published under Q233515



SYMPTOMS
When using the "Microsoft OLE DB Provider for Oracle" to Execute an action query (Update or Insert) on a connection opened to an Oracle 7 or 8 server and a table that has LONG datatype field, you might receive the following error message:

ORA-01704: String literal too long

If using the "Microsoft OLE DB Provider for ODBC Drivers," the error message might look like:

[Microsoft][ODBC driver for Oracle][Oracle] ORA-01704: String literal too long.



CAUSE
There is an Oracle limitation of 2000 characters for literal strings on all flavors of Oracle 7 and 4000 characters on Oracle 8.



RESOLUTION
Use a string literal of up to 2000 or 4000 characters only. Longer values might only be entered using straight code to edit or add new records.



STATUS
This behavior is by design.



MORE INFORMATION
This limitation is an Oracle limitation and the error might occur using any Data Access method including DAO/RDO and ADO. Also, it is independent of the type of Provider or the ODBC driver used.

For more information on this error, please refer to Oracle Documentation.

The following sample code demonstrates how to reproduce this error and how to avoid it using Visual Basic and ADO using the OLEDB provider for Oracle. It assumes that the user is familiar with Oracle servers and has fair knowledge of ADO.

Steps to Reproduce Behavior
  Create an Oracle table using the following script: CREATE TABLE Account (              AccountNo    NUMBER(3) PRIMARY KEY,               TestStr      LONG);  Start a new project in Visual Basic and add a Reference to the "Microsoft ActiveX Data Objects library" 1.5, 2.0, or 2.1, depending on the installed version.  Place two CommandButtons on the form:  Control       Name       Caption --

Button1     cmdAction   Action Query Button2     cmdCode     ADO Code   Paste the following code into your code window: Private Sub cmdAction_Click Dim cn As New ADODB.Connection Dim str As String Dim i As integer

cn.CursorLocation = adUseClient cn.Open "Provider=MSDAORA.1;Password=demo;User ID=demo;Data Source=dseoracle;Persist Security Info=True"

str = "" For i = 1 To 2001 str = str + "A" Next

' Uncomment the next line to use Update and comment the one after ' cn.Execute "Update Account set TestStr = '" & str & "' where AccountNo=1" cn.Execute "Insert into Account(AccountNo, TestStr) values(5,'" & str & "')" MsgBox "successful Operation" cn.close set cn = Nothing End Sub

Private Sub cmdCode_Click Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim str As String

cn.CursorLocation = adUseClient cn.Open "Provider=MSDAORA.1;Password=demo;User ID=demo;Data Source=dseoracle;Persist Security Info=True"

str = "" For i = 1 To 2001 str = str + "A" Next

rs.Open "select * from Account", cn, adOpenStatic, adLockBatchOptimistic

rs.AddNew rs("AccountNo") = 8 rs("TestStr") = str

rs.UpdateBatch

MsgBox "successful Operation" cn.close set cn = Nothing End Sub  Run the project and click on any CommandButton. If you click on "Action Query" button, you will get the error message. However, if you click on "ADO Code" no error occurs.</li></ol>

<div class="references_section">