Re: Help with Insert Into Select from and Long Column

In article <RPOG4.8603$_Q3.450710_at_typhoon.austin.rr.com>,   "Bruce" <bcallen_at_austin.rr.com> wrote:
> I am trying to execute a SQL statement that does an Insert INTO with a
> Select FROM clause. The problem is one of the columns is a LONG
 column.
> The error message from Oracle is "ORA-00997: illegal use of LONG
 datatype".
>
> The statement looks something like this:
> INSERT INTO xyz (col1, col2) SELECT col1, col2 FROM xxx WHERE ...
> and col2 would be type LONG.
>
> I have been able to get around the problem in the past by use a stored
> procedure and using a cursor to select into and then loop thru the
 records
> and INSERT. In this case I want to do this in code and NOT in a
 stored
> procedure. I also do not want to have to bring all the data back to
 the
> client.
>

Sorry but the only way to do this is procedurally.

PLSQL can do it if your longs are all 32k or less.

C/VB or some other 3gl is the only way to do it for longs >32k.

The sqlplus copy command works as well if you can use sqlplus for longs of any length. See http://osi.oracle.com/~tkyte/Misc/MoveLongs.html.

If you have Oracle8 release 8.0 and up, CLOBs are the way to go over longs. They do not have this limitation.

> Any ideas are appreciated
>
> Bruce
>
> bcallen_at_lgc.com
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.