oracle – How to create an alias for a data type with a maximum length?

Question:

Is there a way to alias a built-in data type with a maximum length, such as number(3) ?

So that I can declare variables not like this:

myvar number(3);

and so:

myvar mynumber;

And also attributes in type object , like this:

create or replace type myobject as object
(
   myattribute mynumber
);

I know one way – to create a table with a column of the desired type:

create table mytable (mynumber number(3));

Now you can declare variables:

myvar mytable.mynumber%type;

But this is fu, how ugly. Especially, an empty table dangling in the list of normal ones.

Also, this method is not suitable for declaring an attribute type in an object type. The compiler will throw an error:

Error: PLS-00329: Schema-level type has an invalid reference to…

Answer:

You can create a custom data type with a constraint. For example, like this:

create or replace type number3 as object(value number(3)) final;
/
Type NUMBER3 compiled

create or replace type mytype as object (short_id number3);
/
Type MYTYPE compiled

select mytype(number3(999)).short_id.value as short_id from dual;

  SHORT_ID
----------
       999

select mytype(number3(9999)).short_id.value as short_id from dual;

ORA-01438: value larger than specified precision allowed for this column

Since it is defined at the schema level, it will work everywhere:

declare short_id number3 := number3(999); begin dbms_output.put_line(short_id.value); end;  
/ 
999