 |
|
ORA-01704: string literal too long tips
Oracle Error Tips by Burleson Consulting (S. Karam)
|
The Oracle docs note this on the
ora-01704 error:
ORA-01704: string literal too
long
-
Cause: The
string literal is longer than 4000 characters.
-
Action: Use a string literal of
at most 4000 characters. Longer values may only be entered
using bind variables.
On
DBA
Support.com Forums, many questions have been raised concerning ORA-01704. In
this case, 'isaac' askes why he is still receiving ORA001704 "when doing an
SQL update statement in SQL*Plus and also executing an SQL
query in ColdFusion." using this SQL statement:
Here is the sql statement being executed:
update mkt_page_links
set longdescription = ' {some html text > 4000 char} '
where menuidno = 310;
The replier informed 'isaac' that ORA-01704 was
occurring as he executed the SQL statement:
In SQL the max of varchar2 is 4000 bytes
and hence the error. Modify your code snippet to something like the
following and it should work. (it becomes a pl/sql program).
<cfquery name="upd" datasource="#request.site.MainDSN#">
UPDATE MKT_Page_Links
SET LongDescription = <CFQUERYPARAM VALUE="#DataString#" CFSQLTYPE="CF_SQL_CLOB">
WHERE MenuIDNO = #form.ID#
</cfquery>
Furthermore, when running ColdFusion, a query should be run the following to
avoid ORA-01704:
<cfquery name="upd" datasource="#request.site.MainDSN#">
UPDATE MKT_Page_Links
SET LongDescription = <CFQUERYPARAM VALUE="#DataString#" CFSQLTYPE="CF_SQL_CLOB">
WHERE MenuIDNO = #form.ID#
</cfquery>
In this example, 'isaac' resolution lay in the
parameter that ColdFusion has which can convert variables to CLOB
datatype.
