Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
1-16.	THEN is a reserved word; the PL/SQL compiler refuses to interpret it as a variable name. SYSDATE, on the other hand, is not a reserved word. Rather, it is a function declared in the STANDARD package, one of the two default packages of PL/SQL. You could write that block in an even more confusing manner, just to drive home the difference between “your” sysdate variable and the STANDARD’s SYSDATE function: DECLARE sysdate DATE; BEGIN sysdate := sysdate; DBMS_OUTPUT.PUT_LINE ('Date is ' || sysdate); sysdate := STANDARD.SYSDATE; DBMS_OUTPUT.PUT_LINE ('Date is ' || sysdate); END; You will see this output: Date is Date is 24-JAN-99 As explained in 1-11, we developers are not allowed to constrain our own SUBTYPEs. In other words, you cannot supply after the IS keyword a datatype declaration that limits the size or length explicitly. Check out $ORACLE_HOME/RdbmsNN/admin/standard.sql (the file that creates the PL/SQL STANDARD package) for examples of constrained SUBTYPEs.
Q:
1-17.	The following block of code raises a VALUE_ERROR exception when executed. It demonstrates the technique of constraining a SUBTYPE: DECLARE primary_key NUMBER(6); SUBTYPE primary_key_t IS primary_key%TYPE; mypky primary_key_t; BEGIN mypky := 11111111; END; What you’ve done is a sleight-of-hand maneuver. You want the SUBTYPE declaration to look like this: SUBTYPE primary_key_t IS NUMBER(6); But that code will be rejected by the compiler. Instead, you must declare a variable with the appropriate constraint and then ...