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