Skip to Main Content
  • Questions

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

Breadcrumb

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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