How to create a user defined datatype for a column

[egavish]

How do I create a column to be user-defined datatype?

restrictions:

1. The existing applications Insert and Update statements cannot change their format

2. I can alter existing table modify column to be new type...


????

[pwoneill]

well, your first requirement really depends on how the existing application's insert statements were designed in the first place. for example, if your applications all have inserts like

insert into table values (value1, value2, value3) -- the bad way

as opposed to

insert into table (col1, col2, col3) values (value1, value2, value3) -- the good way

you won't be able to add a column without changing the inserts, and you won't be able to modify a column without changing the inserts unless the same inserts work with the modified column. Eg, changing a char to a varchar shouldn't affect any insert statements, where the contrary might, if some values were too large for the new char field.

assuming the existing applications are coded correctly, you should just be able to add the column as needed (see oracle docs on user defined types at http://oradoc.photo.net/ora81/DOC/server.815/a67781/c11ordb.htm ).