Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle/SQL LONG field limited to 2048 chars????
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)......
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
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