Re: Help on inserting strings...

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/19
Message-ID: <8dkcn9$fk0$1@nnrp1.deja.com>#1/1

In article <38FD7B28.C22E712F_at_uhb.fr>,
  Joel SEGUILLON <Joel.Seguillon_at_uhb.fr> wrote:
> Hi,
> Here is my problem : I have to generate SQL scripts wich insert
> strings in LONG fields...
>
> When i try to insert very long strings, SQLplus tells me :
> "ORA-01704: string literal too long"
> Does anyone have a solution. I can't use bind variables because it
> must generate a script and i set maxdata to 60000.
> If someone have a solution, please help :).
>
> Thanks.
>

You have to use bind variables. See
http://osi.oracle.com/~tkyte/Misc/LargeStrings.html it'll show how you can in sqlplus.

Bear in mind that SQLPlus in turn has its own limits on the length of a line it'll take without a break. If your text does not have line breaks in it, you'll need to use string concatention. for example:

ops$tkyte_at_8i> create table t ( x long );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> declare
  2 bind_var long default

3          'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||

... lots of lines just like the above chopped out ....

162          'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
163  begin
164          insert into t values ( bind_var );
165          dbms_output.put_line( 'inserted long with ' || length

(bind_var) || ' bytes and no line feeds' ); 166 end;
167 /
inserted long with 18400 bytes and no line feeds

PL/SQL procedure successfully completed.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Apr 19 2000 - 00:00:00 CDT