| 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: Long Column Inserts via PL/SQL
A copy of this was sent to "John Becker" <jbecker2_at_ix.netcom.com>
(if that email address didn't require changing)
On Thu, 20 May 1999 17:12:13 -0400, you wrote:
>Hello,
>
>I need to populate several long columns with text data > 2,000 characters.
>I must import this via a script that is run in Worksheet, and am unable to
>do so since I am breaking the 2,000 character limit.
>
>Would anyone have any samples of how this may done in Oracle?
>
>PS. I don't have the luxury of using code, or any other means to import this
>data. My only option is via this script.
>
>Thanks,
>
>John Becker
>
>
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...
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Fri May 21 1999 - 11:01:28 CDT
 |
 |