Q233515: PRB: Error "String Literal Too Long" w/ Update/Insert on Oracle
Article: Q233515
Product(s): Microsoft Visual Basic for Windows
Version(s): 5.0,6.0
Operating System(s):
Keyword(s): kbATM kbDriver kbODBC kbOLEDB kbOracle kbVBp500 kbVBp600 kbVC500 kbVC600 kbGrpDSVBDB kb
Last Modified: 07-MAY-2001
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0
- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
- Microsoft Visual C++, 32-bit Enterprise Edition, versions 5.0, 6.0
-------------------------------------------------------------------------------
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
---------------------------
1. Create an Oracle table using the following script:
CREATE TABLE Account (
AccountNo NUMBER(3) PRIMARY KEY,
TestStr LONG);
2. 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.
3. Place two CommandButtons on the form:
Control Name Caption
--------------------------------------
Button1 cmdAction Action Query
Button2 cmdCode ADO Code
4. 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
5. 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.
REFERENCES
==========
For additional information about LONG character datatypes and how to deal with
them using Visual Basic, please see the following article in the Microsoft
Knowledge Base:
Q178875 FILE: ORALONG.EXE: Use RDO with Oracle LONG/LONG RAW Datatypes
For additional information about on how to use ADO with Oracle, please see the
following article in the Microsoft Knowledge Base:
Q176936 INFO: Visual Basic 5.0 Accessing an Oracle Database Using ADO
(c) Microsoft Corporation 1999, All Rights Reserved.
Contributions by Hussein Abuthuraya, Microsoft Corporation
Additional query words: oracle long raw
======================================================================
Keywords : kbATM kbDriver kbODBC kbOLEDB kbOracle kbVBp500 kbVBp600 kbVC500 kbVC600 kbGrpDSVBDB kbGrpDSMDAC kbDSupport
Technology : kbVCsearch kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600 kbVC500 kbVC600 kbVC32bitSearch kbVC500Search
Version : :5.0,6.0
Issue type : kbprb
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.