Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Insert a long field
A copy of this was sent to "michael t hudacko" <hudacko_at_bbdo.com>
(if that email address didn't require changing)
On Thu, 25 Jun 1998 21:02:38 GMT, you wrote:
>I'm working on the same problem in building a document database.
>Apparently quoted 'string literals' cannot exceed 2000 characters? (But
>then that the reason I defined the fields as LONG rather than VARCHAR2!?)
>
>
>John Ma <john_ma_at_hp.com> wrote in article <359294A0.769E_at_hp.com>...
>ORA-01704: string literal too long
>>As I know Oracle 7.3 support "long" up to 2GB, does anyone know what I
>> should do/set to break the limitation? and What is the limitation? Does
>> there has some special setting I need to do if I use sqlloader to do
>> initial loading?
>>
>>
>> Thank you very much for the help.
>>
>>
>>
>> John
>>
But, if the documents are coming in from somewhere, they will not be in character constants will they? I mean, your authors are not going to write:
insert into table values ( 'this is my document, it''s a very good one' );
They are going to write the document and then you will load it using a 3/4gl right?
Bind variables support much more then 2000 characters (4000 in o8). For example, using PL/SQL we can do 32k at a time 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...
For larger then 32k, you need a 3/4gl such as C, Forms, VB, etc.
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 Fri Jun 26 1998 - 07:50:00 CDT