Forum Stats

  • 3,782,457 Users
  • 2,254,650 Discussions
  • 7,880,082 Comments

Discussions

Categories

How to copy a user-defined type from other schema to mine?

user1987306
user1987306 Member Posts: 72
edited Aug 25, 2012 1:33PM in SQL & PL/SQL
Good evening!
I hope you are all fine.

Please help me to solve this doubt:

Through sql*plus, how can I copy a user-defined type from other schema to mine?

I've granted the execute object privilege over the user-defined type to my user, so I can use the type.

I know I can manually write and create the type, but I wanna learn (if exist) a statement to copy the user-defined type, something like:
CREATE TABLE x2 AS
(SELECT *
FROM x1);

I'm testing ORACLE DATABASE 11g R2 over Windows O. S.

Thanks in advance!
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Aug 24, 2012 9:57PM
    >
    Through sql*plus, how can I copy a user-defined type from other schema to mine?
    >
    Copy out the code from ALL_SOURCE and compile it.

    If you really want to get tricky query the source, spool it to a file and then execute the file.
    · Share on TwitterShare on Facebook
  • Biju Das
    Biju Das Member Posts: 393 
    SELECT DBMS_METADATA.GET_DDL('TYPE', 'MY_TYPE') FROM dual
/
    Regards
    Biju
    Biju Das
    · Share on TwitterShare on Facebook
  • user1987306
    user1987306 Member Posts: 72
    I've tested both solutions, both are good.

    Notice that to use ALL_SOURCE the invoker must be granted the "EXECUTE" object privilege on the user-defined type and in order to use DBMS_METADATA.GET_DDL the invoker must be granted the "SELECT_CATALOG_ROLE" role, without that role, you get errors ORA-31603 and ORA-06512.

    thanks by your support!
    · Share on TwitterShare on Facebook
This discussion has been closed.