PLS-00642: local collection types not allowed in SQL statement

I got ” PLS-00642: local collection types not allowed in SQL statement ”  error in Oracle database.

PLS-00642: local collection types not allowed in SQL statement

Details of error are as follows.

PLS-00642: Local collection types not allowed in SQL statements

Cause: A locally-defined (i.e. not schema level) collection type was used in a SQL statement.
 The type must be defined in a schema to be accepted in a SQL statement.

Action: Define the collection type in your schema, not inside a PL/SQL subprogram

Local collection types not allowed in SQL statements

This PLS-00642 error is related to the locally-defined (i.e. not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.

Define the collection type in your schema, not inside a PL/SQL subprogram.

This: local collection types not allowed in SQL statements. You can’t use locally declared PL/SQL types in SQL like that.

You need to use SQL types created via a CREATE TYPE statement

Do you want to learn Oracle Database for Beginners, then read the following articles.