Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle/SQL LONG field limited to 2048 chars????

Re: Oracle/SQL LONG field limited to 2048 chars????

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 30 Sep 1998 16:59:25 GMT
Message-ID: <3619631a.18986911@192.86.155.100>


A copy of this was sent to Martijn Spronk <spronk_at_columbus-group.com> (if that email address didn't require changing) On Wed, 30 Sep 1998 09:16:13 -0700, you wrote:

>I ran into a probem using the LONG datatype in Oracle.
>I occurs with a table in Oracle 7.3.2 for Solaris that looks
>like this:
>
>report_id NUMBER(9) PRIMARY KEY,
>report_type NUMBER(3) NOT NULL,
>survey_type NUMBER(3) NOT NULL,
>team_id VARCHAR2(30),
>report_date DATE,
>business_unit VARCHAR2(30),
>prime_centre VARCHAR2(30),
>report_text LONG
>
>Whenever I try to INSERT something in this table with a
>report_text field being bigger than 2048 characters, it
>gives me the error message:
>
>Error from Server: ORA-01704: string literal too long
>
>Now am I crazy or shouldn't I be able to enter field sizes up
>to 2Gb in that field actually??? I ran into the problem using
>LiveWire (Netscape Server), but the same problem occurs
>trying it with SQL*PLUS.
>
>Regards, Martijn.

what language are you using to get stuff in? you need to use bind variables for larger strings instead of character string constants. SQL limits the length of a CHARACTER STRING CONSTANT in a sql statement -- to exceed that limit you must use bind variables...

One method in SQL plus that works upto 32k is as such:

drop table demo;
create table demo ( x long );

declare

    myvar long default '

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
(lots of stuff here)......
';
begin

   insert into demo values ( myvar );
   dbms_output.put_line( length( myvar ) ); end;

The pl/sql variable can be initialized to upto 32k of static text. It can then be inserted into the table (it is a bind variable, not a constant at that point).

For example, I just ran it and it showed me:

Table dropped.

Table created.

24726

PL/SQL procedure successfully completed.

So, that put a 24,726 character string into the table...

to deal with >32k you must use some other interface in v7.x (not so in 8.x with LOBs).  

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 Wed Sep 30 1998 - 11:59:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US