Skip to Main Content
  • Questions
  • It is possible to declare/create an UDT type inside of a package?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Simone.

Asked: May 31, 2017 - 11:50 pm UTC

Last updated: January 16, 2023 - 3:40 am UTC

Version: Oracle Database 11g Express Edition

Viewed 10K+ times! This question is

You Asked

Hello everyone. I came here with one simple question.

It is possible create an UDT inside of a package?

For example, I create a new package

CREATE OR REPLACE PACKAGE MyPackage AS

TYPE myType AS OBJECT(
NAME VARCHAR(30),
SURNAME VARCHAR(30)
);

FUNCTION printData(NAMEP VARCHAR) RETURN VARCHAR;

END MyPackage;
/

It is possible or not?

How can I have an alternative of UDT inside of a package?

and Connor said...

You can use a RECORD instead

SQL> CREATE OR REPLACE PACKAGE MyPackage AS
  2
  3  TYPE myType AS OBJECT(
  4  NAME VARCHAR(30),
  5  SURNAME VARCHAR(30)
  6  );
  7
  8  FUNCTION printData(NAMEP VARCHAR) RETURN VARCHAR;
  9
 10  END MyPackage;
 11  /

Warning: Package created with compilation errors.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE MyPackage AS
  2
  3  TYPE myType is record(
  4  NAME VARCHAR(30),
  5  SURNAME VARCHAR(30)
  6  );
  7
  8  FUNCTION printData(NAMEP VARCHAR) RETURN VARCHAR;
  9
 10  END MyPackage;
 11  /

Package created.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you. But what about...

Simone, June 01, 2017 - 8:49 am UTC

Thank you for answering my questions.

But what about types like VARRAY, NESTED TABLE AND TYPES UNDER ANOTHER TYPE can be created inside a package?

I was also thinking, can I create a UDT type with a procedure?
Connor McDonald
June 02, 2017 - 3:09 am UTC

You could always *try* it :-)

SQL> CREATE OR REPLACE PACKAGE MyPackage AS
  2
  3      TYPE myType is record(
  4      NAME VARCHAR(30),
  5      SURNAME VARCHAR(30)
  6      );
  7
  8      type vs is varray(10) of number;
  9      type nt is table of date;
 10      type blah is table of mytype;
 11
 12      FUNCTION printData(NAMEP VARCHAR) RETURN VARCHAR;
 13
 14  END MyPackage;
 15  /

Package created.


If you want to run DDL in a proc (normally a bad idea), you can use 'execute immediate' to run any sql statement, eg

SQL> begin
  2    execute immediate 'drop table t';
  3  end;
  4  /

PL/SQL procedure successfully completed.


Thank you again

A reader, June 02, 2017 - 10:25 am UTC

Thank you again for answering my question.

Best regards.

Can we return a RECORD type?

A reader, January 12, 2023 - 5:15 am UTC

Hi,

Can we return a RECORD type using a function inside the package?

I tried it but got an error when created the package specification:

E.g:

SQL>CREATE OR REPLACE PACKAGE pkgtestrec as
  2  TYPE t_testrec IS RECORD (
  3  sometxt VARCHAR2(10),
  4  somenum NUMBER
  5  );
  6
  7  FUNCTION return_t_testrec (p_someparam VARCHAR2) RETURN t_testrec PIPELINED;
  8
  9  END pkgtestrec;
 10  /

Warning: Package created with compilation errors.

Elapsed: 00:00:00.02
SQL>sho err
Errors for PACKAGE PKGTESTREC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/10     PLS-00630: pipelined functions must have a supported collection
         return type


Thanks.


Chris Saxon
January 12, 2023 - 5:27 pm UTC

Pipelined table functions have to return an array.

You could return an array of records:

CREATE OR REPLACE PACKAGE pkgtestrec as
 TYPE t_testrec IS RECORD (
 sometxt VARCHAR2(10),
 somenum NUMBER
 );
 
 TYPE t_rec_arr IS TABLE OF t_testrec;

 FUNCTION return_t_testrec (p_someparam VARCHAR2) RETURN t_rec_arr PIPELINED;

END pkgtestrec;
/

Thank you Chris

A reader, January 13, 2023 - 6:38 am UTC

Thank you Chris. I had figured it out myself and had the package working - DUH moment ;)

Could not find this thread hence was not able to update it.

But good to know I was on the right track.

Cheers!


Connor McDonald
January 16, 2023 - 3:40 am UTC

Thanks for getting back to us.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library