Oracle – Possible to create Oracle Database object types inside of PL/SQL

oracleplsql

Is it possible to create an object type inside of a package in Oracle Database 10g? Something like:

create or replace package my_package as 
    type my_type as object (
        id number(15) 
     ); 
end;

Gives:

Error(3,9): PLS-00540: object not supported in this context.

What I'm ultimately looking to be able to do is use polymorphism but also allow the objects to access tables and use PL/SQL, which isn't allowed in types defined outside of packages.

Thanks,
Jeff

Best Answer

From the Oracle 10g documentation:

Currently, you cannot define object types in a PL/SQL block, subprogram, or package.

So, unfortunately, no.

Related Solutions

Oracle sql types over dblink

I've had similar problems in the past. Then I came to the conclusion that fundamentally Oracle's db links are "broken" for anything but simple SQL types (especially UDT's, CLOBS may have problems, XMLType may as well). If you can get the OID solution working then good luck to you.

The solution I resorted to was to use a Java Stored procedure, instead of the DB Link.

Characteristics of the Java Stored Procedure:

Can return a "rich set of types", just about all of the complex types (UDT's, tables/arrays/varrays) see Oracle online documentation for details. Oracle does a much better job of marshalling complex (or rich) types from java, than from a DBLink.
Stored Java can acquire the "default connection" (runs in the same session as the SQL connection to the db - no authentication issues).
Stored Java calls the PL/SQL proc on the remote DB, and the java JDBC layer does the marshaling from the remote DB.
Stored Java packages up the result and returns the results to the SQL or PL/SQL layer.

It's a bit of work, but if you have a bit of java, you should be able to "cut and paste" a solution together from the Oracle documentation and sample.

I hope this helps.

Oracle – Is it possible to create Oracle associative array type outside of a package/procedure

The answer is no, you cannot do what you're trying to do, any more than you can create a type to add a BOOLEAN typed variable to an object. The items in an object must contain Oracle types, not PL/SQL types. A bit clunky alternative could be:

CREATE TYPE t_aa AS VARRAY(10) OF VARCHAR2(10);

CREATE OR REPLACE TYPE t_ua AS OBJECT (ID NUMBER(15)
                                     , MEMBER PROCEDURE initialize(p_aa t_aa)
                                     , MEMBER PROCEDURE initialize(p_aa_i t_aa))
                               NOT INSTANTIABLE NOT FINAL;

Store your associated pairs of variables in the two VARRAYs. You will have to know the largest possible size of your arrays.

Best Answer

Related Solutions

Oracle sql types over dblink

Oracle – Is it possible to create Oracle associative array type outside of a package/procedure

Related Topic