Re: Oracle 7.3 LONG Varchar limitation to 2000 characters (ORA-01704); string literal too long

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/11/19
Message-ID: <365c3d6a.96038105@192.86.155.100>#1/1

A copy of this was sent to kevinwc_at_tidecom.com (Kevin) (if that email address didn't require changing) On Wed, 18 Nov 1998 22:54:40 GMT, you wrote:

[snip]

>This part appears to be working fine. The string variable ls_RetVal
>does contain all the text it should (even when there are more than
>2000 charactes). Shortly after this function completes we end up with
>a statement that looks like the following:
>
>ls_sqlsyntax = "UPDATE <ls_table> SET <ls_column> = <ls_RetVal> WHERE
>ls_primary"
>
>Up until this point things are still looking fine. We then execute
>the SQL statement from within our application with the Powerbuilder
>statement
>
>EXECUTE IMMEDIATE :ls_SqlSyntax USING sqlca;
>
>At this point we get a SQL error code ORA-01704: String Literal Too
>Long.
>

a character string LITERAL in sql must be 2000 or less (4000 in O8). You can fix this in one of 2 ways

• use a bind variable in the UPDATE statement. Bind variables have no limits on the length.
• use an anonymous pl/sql block which will work upto 32k. Here is an example:

drop table demo;
create table demo ( x long );
insert into demo ( 'some data' );

declare

    myvar long default '

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

(lots of stuff here, 32k of x's)......
';
begin

   update demo set x = MYVAR;
end;

The pl/sql variable can be initialized to upto 32k of static text. It can then be used in the update (it is a bind variable, not a constant at that point). This forces the use of a bind variable in the update statement but lets you continue to use a character string.

I might point out that a sql update with a bind variable with be infinitely more efficient then parsing and executing distinct updates/blocks of pl/sql over and over again.... Option 1 above is most efficient, option 2 is probably the easiest for you to do right now.

>From what I have read this is because quoted strings may not contain
>more than 2000 characters. However a Long Varchar datatype can
>support upto 2GB worth of text. I have taken the SQL statement out of
>our application and tried to run it SQL Plus 3.3 and the same error is
>happening. At this point changing the database structure is out of
>the question (maintenance nightmare).
>
>I have tried the following thinking that perhaps this would work
>
>UPDATE ls_table SET ls_column = ls_column || ' ' || ls_RetVal;
>
>But this also did not end in a desireable result.
>
>Has anyone found a way around this either with a PowerBuilder 5.0.3 or
>an Oracle 7.3 solution?
>
>
>Any help would be greatly appreciated.
>
>Thank you in advance.
>
>Kevin Chamberlain
>kevinwc_at_tidecom.com
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Thu Nov 19 1998 - 00:00:00 CST