Question: What is the cause of the ORA-01704 error?
Answer: The
oerr lookup tool notes the following 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 Forums, many questions have been raised
concerning ORA-01704.
In one case, 'isaac' asks why he is still receiving ORA-001704 "when
doing an SQL update statement in SQL*Plus and also executing an SQL
query in ColdFusion." using the following SQL statement:
update mkt_page_links
set longdescription = ' {some html text > 4000
char} '
where
menuidno = 310;
The response 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.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
