Re: inserting data into "CLOB" field returning ORA-01704: string literal too long

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"DJ DIGI" <gigamix.productions@xxxxxxxxx> wrote in message
news:1139531945.494657.278470@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello Everyone,


I'm attempting to design a small web app to obtain customer feedback.
The issue is when a user inputs >4000 characters in the COMMENTS
section I get the following Err Msg: ORA-01704: string literal too
long.


I am using Visual Studio.Net 2003 (vb as language), SQL+ to create
tables in Oracle 9i, and Access to just link and veiw the tables.


Used SQL+ to create the table


sql> create table tab1
(NAME varchar2(100), COMMENTS CLOB);


My webform has 2 textboxes, 1 button and 1 label


Code looks like so:


Imports System.Data
Imports System.Data.OleDB
_________________________


Private ConnectString as String
...
ConnectString = "Yada, Yada, Yada..."
...
Dim con As New OleDBConnection(ConnectString)


________________________________


The following is my input statement:


Private Sub btnSubmit_Click.........


TRY


Dim insertSQL, Name, Comments As String
Dim Added as Integer


NAME = " ' " & txtName.text & " ' "
COMMENTS = " ' " & txtComments.text & " ' "


insertSQL = "INSERT INTO TAB1(NAME, COMMENTS)" & _
"VALUES (" & NAME & ", " & COMMENTS & ")"


DIM cmd As New OleDBCommand(insertSQL, con)
con,open()
added = cmd.ExecuteNonQuery
con.close()


'gives me some kind of confirmation record was submitted
If added > 0 Then
lblErr.text = "Record Submitted"
End If


Catch ex As Exception
con.close()
lblErr.text = ex.Message
End Try
End Sub
__________________________________________


This is as detailed as I can get. Do I need to declare COMMENTS as
something other than string? Is there some other way I need to write
the insert statement? Am I way off and should hang it up as a beginner
.Net developer? I have read through tons of docs, websites, and groups
and can't come up with any definitive answers. This is my first time
posting. Willing to help anyone and any help would be MUCH appreciated.


The problem is the Oracle SQL parser. From this article:
http://philip.greenspun.com/sql/limits.html

"If you thought that these limitations were bad, you haven't gotten to the
big one: the Oracle SQL parser can only handle string literals up to 4000
characters in length. SQL*Plus is even more restricted. It can handle
strings up to 2500 characters in length. From most Oracle clients, there
will in fact be no way to insert data longer than 4000 characters long into
a table. A statement/system that works perfectly with a 4000-character
string will fail completely with a 4001-character string. You have to
completely redesign the way you're doing things to work with strings that
might be long."

Sorry - you'll have to find a way of writing the string a piece at a time,
unless there's a better method out there...


.

Quantcast