Re: Copying LONGs between tables
Date: 3 Dec 92 23:35:06 GMT
Message-ID: <1992Dec3.153506.1_at_us.oracle.com>
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
The reason is that your subquery is trying to convert the value in LONG_FIELD into a CHAR before using it in the INSERT. If "long_field" is longer that 255 characters, it gets a value to long to stuff into a CHAR and errors out. But, on pg 6-6 of the RDBMS manual, it says you can't use a LONG in a subquery, anyway.
The only way you can INSERT a LONG into a long column, is to use the VALUES clause. I realize that it is a pain to keep the number of columns dynamic, I avoid writing dynamic SQL in Pro*C whenever possible because it can be so complex. Then again, you could write it in OCI! You could also insert everything except the long, saving the ROWID of the source record and the Primary key of the target record in a temporary table. Then, go back to the temporary table, looking up the primary key, and UPDATEing the LONG field based on the ROWID of the source :
// Here, I am assuming col_a is the primary key. // This is pseudocode, so you'll have to port into your language. // :my_long is a large host variable.
FOR r IN select rowid,col_a,col_b from oldtab LOOP
insert into newtab (col_a,col_b) values (r.col_a,r.col_b);
insert into temptab values(r.rowid,col_a);
END LOOP
FOR r2 IN select rowid,col_a from temptab LOOP
select col_long from oldtab into :my_long where rowid = r2.rowid;
update newtab set col_long = :my_long where col_a = r2.col_a;
END LOOP
// Still, dynamic SQL looks better.
Note also, you can take advantage of the restriction that there will never be
more than 1 long column per table, so you only need to do this once.
-- ================================================================================ Scott Stephens inet: sstephen.us.oracle.com Oracle WorldWide Support Redwood City, CaliforniaReceived on Fri Dec 04 1992 - 00:35:06 CET