Inserting/Updating long strings into a LONG

[olemma]

Whenever I try to insert a long string into a LONG field (in sqlplus or OEM), I am getting this error:
ORA-01704: string literal too long

The string is problaby > 2000 char (I don't have the same problem if I truncate the text). I even tried other field types,like LONG RAW or BLOB, but I get the same or similar errors. Does anyone have a PLSQL script to do this?

Or, any other solution that can be implemented in OEM,sqlplus, sql scratchpad or similar?

Thank you,
Desperate.

 

[Turkbear]

Hi, Look into the DBMS_LOB package..

 

[olemma]

isn't that only for BLOBs/CLOBs?

 

[sem]

Just a guess, have not tried it: split your large string literal by a number of chunks and try to insert concatenation their.

Regards, Dima

 

[JediDan]

I know this is old, but it's a pain-in-the-butt situation. Anyway, the solution is to use [tt]utl_raw.cast_to_raw()[/tt], which accepts a varchar2 as the parameter.

I was very happy to find this, hopefully it helps some others out too.

Dan

 

[SantaMufasa]

Olemma,

Since you are using Oracle 9, you can ALTER your table's LONG column to CLOB with no penalty. Here is the proof of concept for you:

SQL> set linesize 50
SQL> drop table olemma;

Table dropped.

SQL> create table olemma (id number, txt long);

Table created.

SQL> insert into olemma values (1,'Hello');

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 100
SQL> select * from olemma;

        ID TXT
---------- -------------------------------------------------
         1 Hello

1 row selected.

SQL> select substr(txt,4) from olemma;
select substr(txt,4) from olemma
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG


SQL> alter table olemma modify txt CLOB;

Table altered.

SQL> set linesize 50
SQL> desc olemma
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER
 TXT                              CLOB

SQL> set linesize 100
SQL> select * from olemma;

        ID TXT
---------- -------------------------------------------------
         1 Hello

1 row selected.

SQL> select substr(txt,4) from olemma;

SUBSTR(TXT,4)
------------------------------------------------------------
lo

1 row selected.

Notice that 1) LONGs can do virtually nothing, 2) CLOBs can do virtually anything that VARCHAR2, 3) converting to CLOB has no penalties.

Let us know if this helps you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:42 (13Jul04) UTC (aka "GMT" and "Zulu"), 11:42 (13Jul04) Mountain Time)