1 Reply Latest reply on Feb 2, 2014 9:56 PM by rp0428

    Object identifier in oracle types

    Tina

      Hi Gurus,

      I have some types in  database_1.

      I am trying to create some packages in database_2, that references the types in database_1.

       

      As far as I know, all the objects should have one unique identifier (oid).But how do I get that value, are they stored somewhere?

      And shouldn't I create those types with OID's in Database_1, in Database_2 ?

        • 1. Re: Object identifier in oracle types
          I have some types in  database_1.

          I am trying to create some packages in database_2, that references the types in database_1.

           

          As far as I know, all the objects should have one unique identifier (oid).But how do I get that value, are they stored somewhere?

          And shouldn't I create those types with OID's in Database_1, in Database_2 ?

          Wrong forum! This forum, as the title says, is for Sql Developer questions - not for SQL or PL/SQL questions.

          Mark the question ANSWERED and repost it in the correct forum.

          https://community.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

           

          Since it isn't really going to do any good to post your question there I will answer it here now for convenience. If you aren't happy with the answer then you will need to repost. If you do repost I suggest that you tell us what PROBLEM you are trying to solve rather than focus on the solution you want to use.

           

           

          I am trying to create some packages in database_2, that references the types in database_1.

          And the answer is: DON'T DO THAT!

           

          Oracle types are only useful within a single database. The xxx_OJBECTS views have the OBJECT_ID for data dictionary objects.

          http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1145.htm#i1583352

           

          And when you create a type you can specify the object id value that you want to use. But that isn't going to help  you across a database link.

           

          See the Object-Relational Dev Guide section 'Restriction on Using User-Defined Types with a Remote Database

          http://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjbas.htm#CIHBIBEA

          Objects or user-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. Oracle Database restricts use of a database link as follows:

          •   You cannot connect to a remote database to select, insert, or update a user-defined type or an object REFon a remote table.

            You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.

          •   You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
          •   You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.