Home » SQL & PL/SQL » SQL & PL/SQL » Structure of Table created using CTAS
Structure of Table created using CTAS [message #188970] Tue, 22 August 2006 11:05 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a CTAS statement which queries from a remote DB:

  Create table TabA
  as select E1.x, 
            E1.y, 
            E2.z
       from ExtTab1@db1 E1,
            ExtTab2@db1 E2,
            ExtTab3@db1 E3
      where E1.xx = E2.yy
        and E2.yy = E3.zz;


ExtTab1.x is VARCHAR2(10);

TabA should also have TabA.x as VARCHAR2(10) - but it becomes VARCHAR2(30)!

I tried:

  Create table TabA
  ( x VARCHAR2(10),
    y number,
    z number
  )
  as select E1.x, 
            E1.y, 
            E2.z
       from ExtTab1@db1 E1,
            ExtTab2@db1 E2,
            ExtTab3@db1 E3
      where E1.xx = E2.yy
        and E2.yy = E3.zz;


but it is not working.. Please help.. thanks in advance.

[Updated on: Tue, 22 August 2006 11:06]

Report message to a moderator

Re: Structure of Table created using CTAS [message #188976 is a reply to message #188970] Tue, 22 August 2006 11:17 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Is your database UTF8 and the remote database not UTF8?
Re: Structure of Table created using CTAS [message #189047 is a reply to message #188976] Wed, 23 August 2006 00:25 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I'm not sure - but I am sure they have different character sets. Is there a solution??
Re: Structure of Table created using CTAS [message #189091 is a reply to message #189047] Wed, 23 August 2006 03:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
  Create table TabA
  as select SUBSTR(E1.x, 1, 10) AS x
            E1.y, 
            E2.z
       from ExtTab1@db1 E1,
            ExtTab2@db1 E2,
            ExtTab3@db1 E3
      where E1.xx = E2.yy
        and E2.yy = E3.zz;


Ross Leishman
Re: Structure of Table created using CTAS [message #189145 is a reply to message #189091] Wed, 23 August 2006 06:49 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
it's not working... Sad
Re: Structure of Table created using CTAS [message #189148 is a reply to message #189145] Wed, 23 August 2006 06:54 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
use
alter table ... modify ...varchar2(10);

after doing the CTAS


Jim

Re: Structure of Table created using CTAS [message #189152 is a reply to message #189148] Wed, 23 August 2006 07:14 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks, but that's taking 3 hours.. I have a table of 150M.. any other alternative??
Re: Structure of Table created using CTAS [message #189153 is a reply to message #189152] Wed, 23 August 2006 07:17 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Create table TabA
as select E1.x,
E1.y,
E2.z
from ExtTab1@db1 E1,
ExtTab2@db1 E2,
ExtTab3@db1 E3
where E1.xx = E2.yy
and E2.yy = E3.zz
AND 1=1;

IF the col is still varchar2(30) do the resize then

insert into tab a (select E1.x,
E1.y,
E2.z
from ExtTab1@db1 E1,
ExtTab2@db1 E2,
ExtTab3@db1 E3
where E1.xx = E2.yy
and E2.yy = E3.zz);

Jim
Re: Structure of Table created using CTAS [message #189167 is a reply to message #189145] Wed, 23 August 2006 08:32 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Can you not just do the CREATE TABLE and then do the insert?
Re: Structure of Table created using CTAS [message #189168 is a reply to message #189153] Wed, 23 August 2006 08:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Does the CAST function help you out?
Create table TabA
  as select CAST(E1.x AS VARCHAR2(10)) x, 
            E1.y, 
            E2.z
       from ExtTab1@db1 E1,
            ExtTab2@db1 E2,
            ExtTab3@db1 E3
      where E1.xx = E2.yy
        and E2.yy = E3.zz;


MHE
Re: Structure of Table created using CTAS [message #189182 is a reply to message #189168] Wed, 23 August 2006 09:34 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Yes! It worked!! You're the MAN!!!
Thanks to you and to Todd, Jim, Ross and Joy! .. now I can go home and enjoy my dinner...
Re: Structure of Table created using CTAS [message #189334 is a reply to message #189168] Thu, 24 August 2006 03:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you doing that on 10g by any chance?
I tried that on 9.2.0.7 and get an ORA-00901: Invalid CREATE command.
Re: Structure of Table created using CTAS [message #189341 is a reply to message #189334] Thu, 24 August 2006 03:50 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Yes in 10g..
Re: Structure of Table created using CTAS [message #189379 is a reply to message #189341] Thu, 24 August 2006 06:13 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I tried it in 9.2.0.7.. It's also working.
Re: Structure of Table created using CTAS [message #189396 is a reply to message #189379] Thu, 24 August 2006 08:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So it is - I am presumably incapable of typing the query correctly. Hey ho.
Re: Structure of Table created using CTAS [message #189405 is a reply to message #189396] Thu, 24 August 2006 08:36 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
There's a bug related to the CAST function in older Oracle versions. Our 8.1.7 says that I have a "ORA-12704: character set mismatch".

MHE
Previous Topic: candidate key
Next Topic: Problem in Query
Goto Forum:
  


Current Time: Sat Dec 03 20:36:49 CST 2016

Total time taken to generate the page: 0.14244 seconds