Oracle and CLOBs: string literal too long

We're currently writing a VB app that uses Oracle in the backend.

And, in attempting to insert data into a table with a CLOB column, we're getting the following error:

ORA-01704     string literal too long


Now, I understand that there is a 2000 character limitation when using a straight INSERT pass-through statement. But, what are the other options?

Unfortunately, we cannot use stored procedures, and we cannot use OO4O (strictly ADO).

How does this affect me on UPDATES, also?


TIA,
-Jason
jlalexanderAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Change your provider from the "Microsoft OLE DB Provider for Oracle" to the Oracle Provider for OLE DB.
0
 
Anthony PerkinsCommented:
By the way I may have spoken to soon.  I have been using an Insert statement with the Oracle native provider.  However I just noticed that the maximum length is 1982.

There does seem to be a workaround, take a look at the following article on MSDN:

PRB: Error "String Literal Too Long" using Update or Insert on Oracle Table Q233515
0
 
Anthony PerkinsCommented:
I meant to say that the longest field is currently 1982, so I have not bridged yet the 2000 barrier.
0
 
DanRollinsCommented:
Hi jlalexander,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept acperkins@devx's comment(s) as an answer.

jlalexander, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
==========
DanRollins -- EE database cleanup volunteer
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0
All Courses

From novice to tech pro — start learning today.