How can I mimic functionality of SQL Server `TYPE` in Oracle?

Question

I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition.

For instance, I need "money" type in Oracle which is technically NUMBER(18,4) in my system, and each table will have "money" column instead of underlying NUMBER for consistency purpose. There are some other "types" I'd like to have aliases rather than using them directly.

Unfortunately, I didn't find acceptable solution yet except giving up and using standard types with constraints(or size specification) for each column. The only approach that seems to work is to create OBJECT type, but it will add unnecessary complexity to the system which I'm trying to avoid.

A very nice Oracle feature, SUBTYPE, doesn't work in this case . As far as I understand, SUBTYPE (and subtypes from STANDARD) creates PL/SQL datatype which cannot be used in SQL. I understand there are various reasons for that.

I wonder what is the best practice for such a problem.

Thank you.

Update
It seems so far that I've listed all possible approaches, and it I'd rather close the question. Not sure if it may be useful for anyone; in case it is, I don't remove the question for the time being.

Answer 1

This is a very good question and not just because it shows effort on your part to answer the question. Thank you for asking it and please do not remove it.

The answer as you have already discovered is -- You can't do that.

You have correctly identified several similar concepts including...

• Using standard types with constraints.
• Object Types that behave differently than standard types.
• Subtypes which are for PL/SQL not SQL.

Why might SQL Subtypes be useful? You mentioned consistency. Here are some ideas:

• Errors are prevented when inserting a field from one table into another.

• The definition of the field does not have to be rememberd or looked up.

• There is less typing.

• Changing the definition can be done in one place.

• The datatype name provides documentation.

Here are some thoughts addressing these:

• Table definitions are not changed frequently. Yes, they do change, but the amount of change is considerably smaller than that of PL/SQL (where you can use subtypes and %types) or application side code.

• Databases are usually normalized to eliminate duplication of data and limit the requirement for multiple tables to have fields with identical definition. Of course something as generic as a Money type would get used in many places, but some types would get moved to a lookup table and only the key repeated in multiple places.

• Often there are benefits to using distinct sizes for different tables. A discount store might have a products table with a field defined as Number(4,2) since they never sell anything over ten dollars. The definition is itself a constraint on the data a constraint that improves acuracy, documents the system, is fast, cannot be circumvented, and is all around better than any application side constraint. If the business decides to remove this constraint by increasing the field size, it does not necessarily mean it needs to change the size of other fields such as Salary, SalesAmount, etc.

• Denormalized data is often aggregated and may require different field sizes. A Salary column need not be Number(18,4) and even a sum of all salary columns company wide need not be Number(18,4) (just short of 100 trillion). Even if you need to increase the aggregated field containing Salary information due to growth of the company, you still wouldn't necessarily need to increase the Salary field at the same time, but if they are based on the same SQL subtype then you would have no choice. Right sized fields have the benefit of using less storage and memory as well as having faster index and table lookups.

• Remembering or looking up a custom type is similar in difficulty to remembering or looking up a previously used field definition.

• Oracle converts the SQL Server Money type to Number(19,4) when using the 10.2 Transparent Gateway, so even standardizing on Number(18,4) would be wrong when working with the gateway.

• In Oracle, frequently table columns are created that include definition that could be in the datatype name for other systems. For example, Wholesale with a datatype of Money might be WholesaleCost in Oracle so that it is clear the field holds relates to money. Sometimes this disabiguation would be necessary anyway such as if there were UnitCount, UnitVolume, and UnitCosts fields. Other times the name itself gives the type away (such as Salary). Finally, comments can be put on the table columns to provide further clarification if necessary.

I say all this not to say that the concept of an SQL subtype is bad, just that it is not as useful as it may seem.

Answer 2

Another approach (yet on a higher level) might be to use an ER-Modeling tool that supports domains.

Then you can define a domain "money" (or whatever you need) in your model and the tool will create the corresponding "native" type out of the domain when you forward engineer your model.