Search code examples
sqloracle-databasetypesoracle19c

Error PLS-00201 while trying to create table type in Oracle

When I run this code in Oracle 19c:

CREATE TYPE t_my_type IS TABLE OF my_table.my_col%TYPE;

I have this error:

Type t_my_type compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
0/0       PL/SQL: Compilation unit analysis terminated
1/23      PLS-00201: identifier 'my_table.my_col' must be declared
Errors: check compiler log

Why does it says that it must be declared?

Solution

  • Found answer here: https://community.oracle.com/tech/developers/discussion/3582858/pls-00201-when-declaring-table-type

    You can't use %TYPE in this case. You must use some predefined Oracle type like NUMBER or create a collection.

    Example:

    CREATE TYPE t_my_type IS TABLE OF NUMBER;