Programmer Defined Subtypes in Oracle.

A programmer can define his/her own subtypes instead of using default subtypes in oracle. Infact these subtypes are defined with the help of default data types only.

User defined subtypes are really easy to use. Suppose we have to declare a varchar of size 3000.
We can declare a varchar of size 3000 as follows.

v_var varchar2(3000);

Now suppose we have to use a varchar of size 3000 in a lot of procedures. Writing this big line again and again in many procedures shall be a pain for us. Also if there is a data flow between different procedures then it is expected that size of the data types in all the procedures should be same. By mistake, a programmer may define different sizes of data types  in different procedures.

In order to solve these problems, we make use of Subtypes.

We can declare the structure of all the variable types,which will be used in our procedures and functions, in a package. Don't worry if you don't know packages. It's just a simple example using package. For the time being , you just take package in oracle as a container.
create or replace package mydatatypes as
subtype mychar is varchar2(20);
subtype mynumber is number(20);
end;


Now we can use mychar,mynumber user defined subtypes in our procedures and functions.These will act in the same way as using varchar2(20),number(20) respectively.

To use these user defined subtype we will write an anonymous block as follows.

 

DECLARE
v_var mydatatypes.mychar;
BEGIN
v_var:='John Smith';
dbms_output.put_line(v_var);
end;

And the output is John Smith.

If you see the block, mydatatypes.mychar is used as a user defined subtype. This is because the mychar subtype is defined inside the package mydatatypes,  and to access it we would have to use mydatatypes.mychar.

We could also have defined subtypes in declaration section of our anonymous block. But that would have defeated the purpose of using subtypes, which are generally used to maintain consistency among data types in different procedures or functions.

For more information visit