7 Replies Latest reply: Oct 9, 2012 12:10 AM by Billy~Verreynne RSS

    Ref : Subtype -

    ranit B
      Hi Experts,

      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.

      Thanks & Regards,
      Ranit B.
        • 1. Re: Ref : Subtype -
          Frank Kulash
          ranit B wrote:
          Hi Experts,

          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?
          Post a link.
          Can anybody please explain me this.
          Let's look at one of the examples from the PL/SQL manual
          DECLARE
            SUBTYPE pinteger IS PLS_INTEGER RANGE -9 .. 9;
            y_axis pinteger;
          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.

          That's just a guess as to what your source might have meant. If we could see the actual source, we could interpret it better.
          • 2. Re: Ref : Subtype -
            rp0428
            >
            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.
            >
            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?
            http://www.toadworld.com/Portals/0/stevenf/PLSQL%20Standards%20Developed%20for%20the%20PLSQL%20Starter%20Framework-1.pdf
            >
            Datatypes

            Anchor parameters and variables using %TYPE (or %ROWTYPE for cursor or entire table).

            Use SUBTYPE to avoid hard-coded variable length declarations.

            Do not hard-code VARCHAR2 lengths for your constants and variables.
            Explicit VARCHAR2 lengths have a nasty habit of changing. Use anchoring and subtypes instead so the code isn‟t so fragile.

            Do not use CHAR as a datatype.
            The only exception would be a requirement that a field maintain a fixed length.

            Avoid implicit datatype conversions.
            Use the appropriate CAST or conversion function instead.

            Use CLOB, BLOB or BFILE for unstructured content. Do not use LONG or LONG RAW.
            >
            If you go back and look at page 8 you will see examples where a SUBTYPE is declared and then used in subsequent type definitions.
            >
            DECLARE
            SUBTYPE lt_ssn IS VARCHAR2(9);
            TYPE lt_account_rec IS RECORD (acct_id NUMBER, cust_id INTEGER, cust_ssn lt_ssn);
            TYPE lt_account_recarr IS TABLE OF lt_account_rec INDEX BY PLS_INTEGER;
            l_account_rec lt_account_rec;
            l_account_recarr lt_account_recarr;
            -- OR --
            l_accounts lt_account_recarr; -- Type Code and tab|arr are optional
            BEGIN
            >
            As show above the SUBTYPE specifies the length (VARCHAR2(9)). Then that subtype (lt_ssn) is used in the definition of the 'lt_account_rec' RECORD.

            That first record type is then used in the seconde type definition and then instances of the RECORD and TABLE types are created.

            As Frank suggested if the length of the data has to be changed from '9' to some other value it can be changed in only one place in the code. If you don't use a subtype you would have to change the length in multiple code locations.

            That can lead to hard-to-find errors when a developer changes the value in one location and doesn't realize it needs to be changed in other locations also.

            The code that developer changes could work for days, week, or even months until suddenly it doesn't work right because of the particular data being used. Errors like that that are caused by data values can be very hard to reproduce and fix because they only happen when the right data values are being used.

            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 9.
            • 3. Re: Ref : Subtype -
              ranit B
              rp0428 wrote:
              >
              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?
              http://www.toadworld.com/Portals/0/stevenf/PLSQL%20Standards%20Developed%20for%20the%20PLSQL%20Starter%20Framework-1.pdf
              >
              RP your are cooool man! You are absolute correct abt the DBArtisans section.
              Frank you are always correct. No doubt in that.

              Thanks guys.
              Ranit B.
              • 4. Re: Ref : Subtype -
                Billy~Verreynne
                Unfortunately the PL/SQL engine does not allow the full spectrum of type definition supported by Ada. Some type definitions (like defining enumerated types) are restricted to the standard package only.

                Subtypes is useful in adding restrictions to an existing type - such a numbers to ranges and strings to max lengths. It also allows one to restrict a variable from containing nulls. This can be used to prevent the so-called 3 boolean value problem of boolean variables being null, in addition to true and false.

                E.g.
                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
                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 default false;
                  5  begin
                  6          null;
                  7  end;
                  8  /
                
                PL/SQL procedure successfully completed.
                
                SQL>
                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.

                Subtypes allows a slightly more restrictive data types to be enforced.

                Whether PL/SQL's approach to implicit data type conversions is a good or bad, is debatable. In languages like Pascal and C for example, data type checking is much more restrictive - and one can argue that this results in better and more robust code. But C is even more restrictive than Pascal in that regard - and requires a lot more keystrokes from the programmer to get a clean compile.
                • 5. Re: Ref : Subtype -
                  padders
                  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 9
                  No, 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>
                  • 6. Re: Ref : Subtype -
                    rp0428
                    You misunderstood what I was saying.

                    The idea is to pass an instance of the SUBTYPE as the parameter instead of passing a VARCHAR2.

                    An instance of the SUBTYPE is length restricted and accomplishes the goal of restricted the length of the parameter.

                    Although your example is legal it is NOT an example of passing a SUBTYPE as a parameter.
                    • 7. Re: Ref : Subtype -
                      Billy~Verreynne
                      rp0428 wrote:

                      Although your example is legal it is NOT an example of passing a SUBTYPE as a parameter.
                      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.

                      I wonder how an Ada compiler will treat such an example..

                      Personally, I dislike this aspect of PL/SQL - not enforcing strict type checking. C tends to be a tad too restrictive though. Which is why I like the Pascal approach. Strict. But not as conservative as C.