Common Data Type in Oracle for reusability

I have data type with fixed column data type and size. For example,

CREATE TABLE TEST1
(
........,
CREATE_USER char(36)
)

CREATE TABLE TEST2
(
........,
CREATE_USER char(36)
)

CREATE TABLE TEST3
(
........,
CREATE_USER char(36)
)

If you see above I have CREATE_USER char(36) in almost all tables. I want to make it replaceable. So that in future I can change the type/size. For example, I call it CREATE_USER myDataType, so that in future I need to replace it only one place. Is it possible in Oracle?

Answer

Have a look at the following example; that’s how I understood the question.

Type first:

SQL> create or replace type my_type as object (name varchar2(10));
  2  /

Type created.

Table whose column’s datatype is my_type

SQL> create table test (name my_type);

Table created.

Let’s insert some rows:

SQL> insert into test values (my_type('Littlefoot'));

1 row created.

SQL> insert into test values (my_type('Littlefoot is Big'));
insert into test values (my_type('Littlefoot is Big'))
            *
ERROR at line 1:
ORA-22814: attribute or element value is larger than specified in type

OK, the 2nd one doesn’t fit. Let’s make it larger (the type, I mean):

SQL> alter type my_type modify attribute (name varchar2(20)) cascade;

Type altered.

How about now?

SQL> insert into test values (my_type('Littlefoot is Big'));

1 row created.

SQL> select * From test;

NAME(NAME)
--------------------------------------------------------------------------------
MY_TYPE('Littlefoot')
MY_TYPE('Littlefoot is Big')

SQL>

Seems to be OK.