integer / number CTAS problem

From: Tony Adolph <>
Date: Tue, 14 Oct 2008 10:25:33 +1300
Message-ID: <>

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

     for each event type in a list
          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;
              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

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


create or replace function add_1 (p_i integer) return *integer * parallel_enable
  return p_i + 1;

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


64 bit Oracle9i Enterprise Edition Release and on HP

Received on Mon Oct 13 2008 - 16:25:33 CDT

Original text of this message