Home » SQL & PL/SQL » SQL & PL/SQL » Can I create user defined TYPE from another schema?
Can I create user defined TYPE from another schema? [message #265742] Fri, 07 September 2007 04:13 Go to next message
maiti_akm
Messages: 1
Registered: August 2005
Location: KOLKATA, INDIA
Junior Member
Hi All,

Can I create user defined TYPE from an existing TYPE of another schema? Like "CREATE TABLE table_name AS SELECT * FROM another_schema.table_name". Where Oracle stores definition of user defined TYPEs?
Re: Can I create user defined TYPE from another schema? [message #265746 is a reply to message #265742] Fri, 07 September 2007 04:27 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

Like "CREATE TABLE table_name AS SELECT * FROM another_schema.table_name".


You can but you have privilege "SELECT ANY TABLE"
Re: Can I create user defined TYPE from another schema? [message #265786 is a reply to message #265746] Fri, 07 September 2007 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
muzahidul, how your answer is related to the question:
Quote:
Can I create user defined TYPE from an existing TYPE of another schema?


maiti_akm, use dbms_metadata.get_ddl procedure to get the DDL and change the owner name.

Regards
Michel
Re: Can I create user defined TYPE from another schema? [message #265865 is a reply to message #265786] Fri, 07 September 2007 12:40 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Michel Cadot wrote on Fri, 07 September 2007 07:01
muzahidul, how your answer is related to the question:
Quote:
Can I create user defined TYPE from an existing TYPE of another schema?




Michel, maiti_akm wanted to CTAS from another schema. So he need to have select any table privilege. I had to try to told that.
Re: Can I create user defined TYPE from another schema? [message #265869 is a reply to message #265865] Fri, 07 September 2007 12:59 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No he didn't ask for that.
He asked for a CREATE TYPE AS statement and he gave an example as there is a CREATE TABLE AS, he don't care about CTAS, he wants a CREATE TYPE AS.
You quoted my answer, where do you see the word TABLE in the question?

Regards
Michel
Previous Topic: Ora error ORA-00932
Next Topic: How to close cursor
Goto Forum:
  


Current Time: Fri Dec 09 23:24:18 CST 2016

Total time taken to generate the page: 0.17866 seconds