Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page