How do you use a packaged type as a column type in a table?

Question

I'm currently trying to understand how to use a type I've created in a package as a column type in my table.

Here is the code for my package...

create or replace PACKAGE  MY_TYPES
IS 
--Associative Array Types 
TYPE permutation_array IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER; 

--VARRAY Types
TYPE code_array IS VARRAY(4) OF VARCHAR2(9); 

Then for my table...

CREATE TABLE mastermind.match_table (match_id NUMBER(4), code_breaker_id NUMBER(4) , 
code_breaker_name VARCHAR2(200),
code_master_id NUMBER(4),
code_master_name VARCHAR2(200),
winner_id NUMBER(4),
game_code MY_TYPES.code_array );

I'm getting the error...

Error starting at line : 3 in command - CREATE TABLE mastermind.match_table (match_id NUMBER(4), code_breaker_id NUMBER(4) , code_breaker_name VARCHAR2(200), code_master_id NUMBER(4), code_master_name VARCHAR2(200), winner_id NUMBER(4), game_code MASTERMIND_COLLECTION_TYPES.code_array ) Error report - ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause:
*Action:

Can anyone assist with this? It would be greatly appreciated.

Answer 1

We can't use PL/SQL types in table definitions. It has to be a SQL type.

create or replace type code_array IS VARRAY(4) OF VARCHAR2(9); 

Then you can create a table:

CREATE TABLE mastermind.match_table (match_id NUMBER(4), 
    code_breaker_id NUMBER(4) , 
    code_breaker_name VARCHAR2(200),
    code_master_id NUMBER(4),
    code_master_name VARCHAR2(200),
    winner_id NUMBER(4),
    game_code code_array );