| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: using the long datatype in Oracle 7.2
A copy of this was sent to Nancy Capitanio <nlcapit_at_mailbox.ucdavis.edu>
(if that email address didn't require changing)
On Wed, 02 Dec 1998 15:15:03 -0800, you wrote:
>Hello,
>
>I am trying to insert more than 2000 characters into a column defined as
>long.
>
>Create table test (
> field1 long
>)
>
>When I do, I get the following message:
>
>01704, 00000, "string literal too long"
>// *Cause: The string literal is longer than 2000 characters.
>// *Action: Use a string literal of at most 2000 characters.
>// Longer values may only be entered using bind variables.
>
>
>The only reference I could find to "bind variables" was regarding PL/SQL
>variables, so I tried creating a procedure that concatenates two 2000
>character variables and then inserts them:
>
>declare
> var11 varchar2(2000);
> var12 varchar2(2000);
>begin
> var11 := '<a string of 1,999 ones>';
> var12 := '<a string of 1,999 ones>';
> insert into test (field1) values (var11||var12);
> commit;
>end;
>
>This causes the error:
>
>01489, 00000, "result of string concatenation is too long"
>// *Cause: String concatenation result is more than the maximum size.
>// *Action: Make sure that the result is less than the maximum size.
>
>Can someone please tell me how one inserts > 2000 characters in a long
>column?
>
>Thanks very much,
>
>Nancy Capitanio
>Programmer Analsyt
>UC Davis
you need to use bind variables for
larger strings instead of character string constants.
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...
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 Dec 02 1998 - 20:32:44 CST
 |
 |