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.
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 );