Re: Copying LONGs Between Tables

In article <By89D6.BpI_at_well.sf.ca.us>, mharper_at_well.sf.ca.us (Michael J. Harper) writes:
> I need to copy the contents of a table into another table. Sounds easy,
> right? Well, my problem is that one of the columns in the source table
> is bound to be a LONG or LONG RAW, and the following syntax:
>
> INSERT INTO NEWTAB
> SELECT COL_A, COL_B, COL_LONG
> FROM OLDTAB
>
> fails with the error message:
>
> ORA-00997: illegal use of LONG datatype
>
> Since I need to use SQL to do this, does anyone know of any workaround?
> (COPY won't work because it seems to be a SQL*Plus thing.)
>
> --
> Michael J. Harper
> mharper_at_well.sf.ca.us

-- 

You are getting the above message because the subquery in your statement is
trying to do a RAWTOHEX on your LONG column before inserting it into your
table.  Since you can not perform functions, including RAWTOHEX, on LONG
functions, it complains.  I think that this restriction may be around because
LONGS may be "chained" over several blocks, and are therefore more complex to
perform functions on.

The only workaround is to capture the entire LONG value to some large storage
are before re-inserting it into a table ...

<pardon my pseudocode>

VARCHAR col_a;
VARCHAR col_b;
LONG col_long;
CURSOR c IS select col_a,col_b,col_long from oldtab;

OPEN c;
while not (NOT_FOUND) loop
   FETCH c INTO :col_a,:col_b,:col_long;
   INSERT INTO NEWTAB VALUES(:col_a,:col_b,:col_long);
end loop;
CLOSE c;

You can use Pro*C (or whatever language), or OCI to do this.
If you really don't want to program, you could export the table to a file,
import into another user account, rename the table, export to a file again 
and import back into the original account...but I personally prefer the
program.

================================================================================
Scott Stephens				inet:	sstephen.us.oracle.com
Oracle WorldWide Support                Redwood City, California