I have data type with fixed column data type and size. For example,
x
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?
Advertisement
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.