Oracle User defined type inside package definition

Question

is it possible to have a User Defined Type inside Oracle package definition? When I try following

CREATE OR REPLACE PACKAGE AF_CONTRACT AS  -- spec
   -- PROCEDURE my_rpcedure (emp_id NUMBER);
   TYPE DTO_GRID AS OBJECT
   (
     ROWKEY    NVARCHAR2(200),
     COLUMNKEY NVARCHAR2(200),
     CELLVALUE NVARCHAR2(200),
     OLDVALUE  NVARCHAR2(200),
     TAG       NVARCHAR2(200)
   );
END AF_CONTRACT;
/
CREATE OR REPLACE PACKAGE BODY AF_CONTRACT AS  -- body

--   PROCEDURE my_procedure (emp_id NUMBER) IS
--   BEGIN
--      
--   END my_procedure;

END AF_CONTRACT;

I always get an error

Error: PLS-00540: object not supported in this context.

in the type definition.

Answer 1

No, it's not permitted:

SQL> CREATE OR REPLACE PACKAGE AF_CONTRACT AS  -- spec
   -- PROCEDURE my_rpcedure (emp_id NUMBER);
   TYPE DTO_GRID AS OBJECT
   (
     ROWKEY    NVARCHAR2(200),
     COLUMNKEY NVARCHAR2(200),
     CELLVALUE NVARCHAR2(200),
     OLDVALUE  NVARCHAR2(200),
     TAG       NVARCHAR2(200)
   );
END AF_CONTRACT;
/
  2    3    4    5    6    7    8    9   10   11   12

Warning: Package created with compilation errors.

SQL> SQL> sho err
Errors for PACKAGE AF_CONTRACT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4      PLS-00540: object not supported in this context.
SQL>

If you want to create a type which is just passing data between PL/SQL procedures then use the PL/SQL RECORD syntax:

SQL> CREATE OR REPLACE PACKAGE AF_CONTRACT AS  -- spec
   -- PROCEDURE my_rpcedure (emp_id NUMBER);
   TYPE DTO_GRID IS RECORD
   (
     ROWKEY    NVARCHAR2(200),
     COLUMNKEY NVARCHAR2(200),
     CELLVALUE NVARCHAR2(200),
     OLDVALUE  NVARCHAR2(200),
     TAG       NVARCHAR2(200)
   );
END AF_CONTRACT;
/
  2    3    4    5    6    7    8    9   10   11   12
Package created.

SQL>

However, if you want a type which you can use in a SQL statement - that is, as the input to a TABLE() function - you will need to create it as a SQL type. SQL and PL/SQL use two different engines, and only SQL types are visible to the SQL engine.

---

My advice about the necessity of SQL Types is no longer true for later versions of Oracle. Certainly in 11gR2 and 12c the SQL engine will support SQL in PL/SQL packages which uses PL/SQL tables in a TABLE() clause. The Types have to be declared in the package spec, hence public and visible to the SQL engine. Under the covers Oracle generates SQL types for each declaration. You can spot these Types because their names start SYS_PLSQL_ followed by numeric identifiers.

Answer 2

Object types have to be declared at the database level. You could declare a collection type in a package specification.

Possible duplicate of: Possible to create Oracle Database object types inside of PL/SQL?