Re: problem: insert long fields into table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 25 May 1999 13:03:27 GMT
Message-ID: <374e9f93.89391207@newshost.us.oracle.com>

A copy of this was sent to "Rüdiger J. Schulz" <r.schulz_at_berlin.de> (if that email address didn't require changing) On Tue, 25 May 1999 13:07:49 +0100, you wrote:

>how can I insert long fields into oracle tables
>with sql*loader or perl or pl/sql
>
>thanx in advance

how long is long? 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)......
';
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...

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 Tue May 25 1999 - 08:03:27 CDT