ranit B wrote:Post a link.
I was going through an Oracle article where they mentioned something called Subtypes.
Read the documentation but didn't get much. Seems like a specialized case for existing data types.
Also was written - "+Use SUBTYPE to avoid hard-coded variable length declaration+ ."
What does this mean?
Can anybody please explain me this.Let's look at one of the examples from the PL/SQL manual
If you have a lot of different variables like y_axis, you might consider using a subtype like this. If the definition of a pinteger changes, you only have to change it in one place (that is, the line where pinteger is declared). all the declarations of pinteger instances (such as y_axis) will not have to be changed.
DECLARE SUBTYPE pinteger IS PLS_INTEGER RANGE -9 .. 9; y_axis pinteger;
rp0428 wrote:RP your are cooool man! You are absolute correct abt the DBArtisans section.
I'm assuming you are referring to the 'Datatypes' section of 'Data Structures' on page 18 of the PL/SQL Programming Standard by Bill Coulam of DBArtisans?
Using the subtype forces boolean variables to be only true or false:
SQL> declare 2 subtype TBoolean is boolean not null; 3 4 b TBoolean; 5 begin 6 null; 7 end; 8 / b TBoolean; * ERROR at line 4: ORA-06550: line 4, column 4: PLS-00218: a variable declared NOT NULL must have an initialization assignment
The PL/SQL engine's data type checking is not very strict. It supports implicit data conversions. So you can for example pass a date or number data type value, for a string data type parameter.
SQL> declare 2 subtype TBoolean is boolean not null; 3 4 b TBoolean default false; 5 begin 6 null; 7 end; 8 / PL/SQL procedure successfully completed. SQL>
Another use for subtypes is when you want to specify a length for a parameter to a function or procedure. You can specify the above SUBTYPE as the datatype and that will restrict the value to a length of 9No, it won't. Data type, NOT NULL constraints and RANGE constraints are checked but not length constraints, precision or scale I believe.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> DECLARE 2 SUBTYPE subtype_name IS VARCHAR2 (1); 3 4 PROCEDURE procedure_name ( 5 parameter_name IN subtype_name) 6 IS 7 BEGIN 8 NULL; 9 END procedure_name; 10 BEGIN 11 procedure_name ('AAA'); 12 END; 13 / PL/SQL procedure successfully completed. SQL>
rp0428 wrote:Padders example should have thrown a compilation error though - but PL/SQL's compiler works differently than the Pascal and C compilers I'm used to.
Although your example is legal it is NOT an example of passing a SUBTYPE as a parameter.