integer / number CTAS problem

Hi All,

I'm in the process migrating some data. The migration process will be a one-off task, but there is a fair bit of data to migrate.

The process is basically as follows:

for each partition in the source tables
loop

     for each event type in a list
     loop
          if this is the first event type in this partition
              create a temp table as select /*+ parallel (t, 8) */
f1(integer column) i, blar, blar, blar from source_t t;
          else
              append into the temp table select /*+ parallel (t, 8) */ ...
          end if
     end loop

    create appropriate index(es)
    create new partition in the destination table by splitting the last partition.

    swap temp table with new partition.
end loop

I'm doing a CTAS initially to cut down on the UNDO.

The problem that I can't work around is that the CTAS operation creates a table with column I NUMBER and not I INTEGER. The destination table has this column defined as INTEGER and this causes the swap partition to fail.

I've tried to cast the column to INTEGER but that doesn't work.

Here's a Tom Ktye-like example:

create table SOURCE_T
(
  I INTEGER
);

desc source_t
Name Type Nullable Default Comments
---- ------- -------- ------- --------
I INTEGER Y insert into source_t
select level n from dual connect by level < 10;

commit;

create or replace function add_1 (p_i integer) return *integer * parallel_enable
is
begin
  return p_i + 1;
end;
/

create table dest_t as
select add_1(i) i from source_t;

desc dest_t
Name Type Nullable Default Comments
---- ------ -------- ------- --------
I NUMBER Y I may just have to bite the bullet and pre-create the temp table with the appropriate INTEGER column and always append into it. But I'm not happy with this.

So 2 questions folks:

1. How to convince Oracle that INTEGER and NUMBER are synonomous for the swap
2. How to get my function embedded in the CTAS statement to create a table with an INTEGER column.

Thanks for any pointers
Tony

Env:

64 bit Oracle9i Enterprise Edition Release 9.2.0.6 and 9.2.0.8 on HP

--
http://www.freelists.org/webpage/oracle-l