6 Replies Latest reply: May 13, 2012 11:51 PM by Billy~Verreynne RSS

    What are opaque types (OPAQUE VARYING)?

    925144
      A number of oracle types like XMLType, Anydata and lcr$_row_record are defined in SYS through "AS OPAQUE VARYING (*)
      USING LIBRARY".

      I wonder what is this? The only thing in documentation I've found is "Oracle OPAQUE Types" from SQLJ Developer's Guide. (http://docs.oracle.com/cd/E11882_01/java.112/e10590/objcoll.htm#i1008113)

      But I couldn't grasp how is it related to PL/SQL. How do I make my own opaque type?

      What are the benefits of opaque types compared to normal PL/SQL object types?
        • 1. Re: What are opaque types (OPAQUE VARYING)?
          rp0428
          >
          I wonder what is this?
          >
          The documentation you cited explains what it is? What part of the explanation is giving you trouble?

          The description discusses the similarities to a Java class instance. An OPAQUE type is similar to a class instance where the variables are defined as private and are only accessible via setter/getter methods.

          For example the actual variable used to store temperature might be an int, double, String or byte array but the actual storage implementation is 'opaque'; you don't know what it is. The method could then have 'getFahrenheitTemperature' or 'getCentigradeTemperature' methods to allow access to the temperature data. Again you do not know the physical structure used to store the data nor do you know the logical structure; that is, is the temperature may be stored as Kelvin and converted to Fahrenheit or Centigrade as needed.

          Same with Oracles OPAQUE types - you (the user - not the designerk) don't know the physical structure. As the doc says it is just a series of bytes. The designer has to provide accessor methods to manipulate the type.

          The doc told you one way to create your own types. Create 'a custom class implementing the oracle.sql.ORAData interface'.

          You can also create your own 'opaque' types using CREATE OR REPLACE TYPE or using Oracle's Data Cartridge functionality
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28425.pdf

          Custom types are used to extend Oracle and provide datatypes and/or functionality that isn't provided by Oracle. You can create your own data types, indexes, and methods for gathering statistics on your own datatypes and indexes.
          • 2. Re: What are opaque types (OPAQUE VARYING)?
            925144
            The documentation you cited explains what it is? What part of the explanation is giving you trouble?
            It doesn't go into details how to create them in PL/SQL and how to use "CREATE OR REPLACE TYPE ... AS OPAQUE VARYING".
            You can also create your own 'opaque' types using CREATE OR REPLACE TYPE
            In "PL/SQL Language Reference" book there is nothing about that "AS OPAQUE VARYING", so how do I exactly use CREATE OR REPLACE TYPE to make an opaque type?
            • 3. Re: What are opaque types (OPAQUE VARYING)?
              Billy~Verreynne
              It says nothing as it is not supported for user written code.

              Not all PL/SQL code is equal. There are a number of "internal" language features that are not exposed, and cannot be used, by user written code.
              • 4. Re: What are opaque types (OPAQUE VARYING)?
                925144
                Billy Verreynne, just a little more clarification. Citing the documentation (http://docs.oracle.com/cd/E11882_01/java.112/e10590/objcoll.htm#i1008113):
                Typically an OPAQUE type will be provided by Oracle, not implemented by a customer.
                So this "typically" is in fact "in all cases", right?
                • 5. Re: What are opaque types (OPAQUE VARYING)?
                  rp0428
                  >
                  In "PL/SQL Language Reference" book there is nothing about that "AS OPAQUE VARYING", so how do I exactly use CREATE OR REPLACE TYPE to make an opaque type?
                  >
                  There is no syntax like 'AS OPAQUE VARYING'.

                  When I said
                  >
                  You can also create your own 'opaque' types using CREATE OR REPLACE TYPE
                  >
                  what I mean is that the Oracle types that you create are 'opaque' in the sense that at the instance level the object is just a series of bytes. You can access the individual components using dot notation.
                  >
                  It doesn't go into details how to create them in PL/SQL
                  >
                  The data cartridge doc I cited DOES go into the detail of how to create them; it just doesn't call them OPAQUE types. But functionally they are since to Oracle the internals are hidden from view. You have to implement the cartridge interface functions in order for Oracle to manipulate your cartridge objects and Oracle does not know or record what the internal structure is.

                  The doc you cited also explains how to create them in Java by implementing the proper interface.

                  As a user you need to concentrate on how to implement the functionality you need and not be concerned with whether a class or object you create gets listed as OPAQUE in the dictionary.
                  • 6. Re: What are opaque types (OPAQUE VARYING)?
                    Billy~Verreynne
                    922141 wrote:
                    Citing the documentation (http://docs.oracle.com/cd/E11882_01/java.112/e10590/objcoll.htm#i1008113):
                    Typically an OPAQUE type will be provided by Oracle, not implemented by a customer.
                    So this "typically" is in fact "in all cases", right?
                    As rp0428 responded - no. You can pop the Oracle db engine hood and do custom implementations. Not something 99.99% of us has ever done though. :-)

                    PL/SQL has some interesting behavioural characteristics. For example, you cannot define enumerated data types (like you can in Pascal and Ada) in PL/SQL - except for inside the system package (that contains much of the core definition and data types of the PL/SQL engine).

                    It's only when you get to complex application implementation that you run into the issue where features you would like to use, are not available. But even then, there are often quite usable and effective work arounds. Opaque types can for example be implemented use the AnyData data type or an abstract o-o class (defined as a SQL object type).