number CTAS problem - oracle-l / integer

integer / number CTAS problem

From: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 14 Oct 2008 10:25:33 +1300

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

integer / number CTAS problem

Other related posts: