Re: long fields - create table xyz as select ... doesnt work

From: Mike Langholz <langholz_at_neomagic.com>
Date: Tue, 27 Apr 1999 08:50:29 -0700
Message-ID: <3725DCC5.218EAD0@neomagic.com>

Guten Tag, Josef.

LONG datatypes have caused problems for me in the past. I bet if I did a little research it would tell me it has to do with the physical storage of LONG. Copying tables using the SELECT * method of CREATE TABLE is really nice, but I don't know how to easily get around the LONG datatype problem using it.

1. I would create a quick script to duplicate the table using data dictionary view USER_TAB_COLUMNS that SELECTs all columns except for the LONG column in the CREATE TABLE statement. Spool and run the script.
2. Create a stored procedure that updates the LONG column in your new table based on the prikey relationship. Call the procedure.

<update cursor example>
declare
  cursor mikecur is
    select * from mikelong;
begin
  for mikecur_rec in mikecur loop
    update mikelong2
    set longfield = mikecur_rec.longfield     where prikey = mikecur_rec.prikey;
  end loop;
end;

Depending on how you create the parameters for the stored procedure, it should be low maintenance.

Viel Gluck!

mike.

Josef Graef wrote:
>
> We are facing a problem when we try to create a new table using the 'AS'
> keyword such as :
>
> CREATE TABLE temp_table AS SELECT * FROM original_table
>
> when the table original_table contains a field with the datatype LONG.
>
> If the table doesn't contain a field with this type everything works
> fine, that means the table
> temp_table is created and all the data of table original_table is
> inserted into temp_table.
> We need a possibility to move the whole data of table original_table
> into a temporary table,
> destroy original_table, create a new table with the name original_table
> (maybe with a
> different structure) and as the last point move the data from temp_table
> back to the new
> original_table.
> Is there any solution or a workaround ? (We need this long fields in
> special tables !!!)
>
> Thanks in advance

***Views expressed are not necessarily those of my company...

--
/------------*------------\

| Michael Langholz        |
| DBA/Programmer          |
* NeoMagic Corporation    *
| (408)988-7020 x483      |

| langholz_at_neomagic.com |
\------------*------------/ Received on Tue Apr 27 1999 - 10:50:29 CDT