Re: Copying LONGs between tables

In article <1992Dec2.000841.3250_at_splunge.uucp> russ_at_splunge.uucp (Russell Bryant) writes:
> 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
>
> I too have been trying to achieve this using something other than COPY
 from
> SQL*Plus. I found, as Scott Stephens from Oracle points out, that it
 must be
> done by retrieving the value into a local variable and then inserting
 this
> variable into the new table. The trouble is that my copy facility must
 be
> generic and I do not want to dynamically allocate storage for a variable
> number of columns. It is, however, almost elegant to retrieve the long
 field
> seperately as with:
>
> Select long_col into :long_field from Oldtab where ...
>
> and then copy the record as with:
>
> Insert into newtab(colA,colB,long_col) select colA,colB,:long_field
> From Oldtab Where ...
>
> Unfortunately, Oracle spews out a 1401 error ("Inserted value too large
 for
> column") when the field is larger than 255 characters. In my mind the
 above
> statements should be legal. I have not found anything in the manuals
 that
> would contradict this. I have contacted central support but if anyone
 can
> spot a valid reason this statement should not work or tell me about
 something
> similar that does work, please let me know. Thanks
> --
> Russ Bryant
> russ_at_splunge.uucp
> or kakwa!atlantis!splunge!russ

I am in the same boat guys. The solution I used, which was by far from elegant but gets the job done was to use sqlplus to spool the data to a file and reload with sql*plus. You will have to turn off the headings and other nice things that sql*plus likes to display and do things like set long 2000 (etc.). Most of the formatting was alright but sql*plus likes to word wrap after 500 lines so I wrote parser that reformatted the data file.

Another solution would be to write a Pro*C procedure that would provide the LONG datatype copying for you since it may access the LONG/RAW datatypes. However, I do not have Pro*C...yet. :-)

If anyone has other solutions, please provide.

Thanks to Andy Finkenstadt for an early solution on this.

# ------ BEGIN CUT HERE
REM
REM Define field separators and set sql*plus environment REM
DEFINE FS = '|'
DEFINE FS2 = '|' set pagesize 0
set feedback off
set echo off
set show off
set long 2000
set arraysize 5
set heading off
set pause on
set wrap off
set linesize 500
set tab off
set verify off
set termout off

spool _INSERT_FILENAME_HERE
select

	field1||'&FS'||field2||'&FS'||field3||'&FS'||
	field4||'&FS'||field5||'&FS'||field6||'&FS'||  
	field7||'&FS'||field8||'&FS'||field9,_LONG_DATA_FIELD_
from _TABLENAME_
	where CLAUSE;

-- 
Neil Greene
President, Kentucky NeXT User Group, Inc.
Email: neil_at_ms.uky.edu [NeXTMail]