1-1.

The following variables are valid or invalid for these reasons:

1-2.

Oddly enough, (a) compiles, while (b) fails with the following error message:

PLS-00371: at most one declaration for 'LASTDATE' is permitted

PL/SQL is a case-insensitive language (except for the contents of literal strings). Therefore, in both cases you are trying to declare two variables with the same name, which is not allowed. It turns out, however, that the compiler will not reject the duplicate declarations unless you actually try to use one of the variables!

1-3.

Here is an example that demonstrates these two formats and shows that the same value is assigned:

DECLARE
  myDate DATE := SYSDATE;
  yourDate DATE DEFAULT SYSDATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE
     ('The date value in myDate is '||mydate);
  DBMS_OUTPUT.PUT_LINE
     ('The date value in yourDate is '||yourdate);
END;
/
The date value in myDate is 29-OCT-99
The date value in yourDate is 29-OCT-99

1-4.

DECLARE
    my_name employee.last_name%TYPE;

1-5.

These declarations are invalid for the following reasons:

INTEGER year_count;

year_count INTEGER;

VARCHAR2(100)
company_name,
employee_name;

company_name
VARCHAR2(100);
employee_name
VARCHAR2(100);

delimiters VARCHAR2;

delimiters
VARCHAR2(100);

curr_year CONSTANT
INTEGER;

curr_year CONSTANT
INTEGER
    := TO_NUMBER
    (TO_CHAR
    (SYSDATE, 'YYYY'));

invalid_date
EXCEPTION
   := VALUE_ERROR;

invalid_date EXCEPTION;

1-6.

These are the most appropriate datatypes for these data items:

1-7.

Use the %TYPE anchoring attribute against a PL/SQL variable, just as you would anchor to the column of a table:

CREATE OR REPLACE PACKAGE types
IS
 dollar_amount NUMBER(20,2);
END;
/
DECLARE
   my_dollars types.dollar_amount%TYPE;
BEGIN
   ...
END;

1-8.

The statements about the DATE datatype are:

1-9.

The thing to remember when using a SUBTYPE is that you do not include a %TYPE anchoring attribute. A subtype already is a type. Here is the solution:

CREATE OR REPLACE PACKAGE types
IS
   SUBTYPE dollar_amount_t IS NUMBER;
END;
/
DECLARE
   my_dollars types.dollar_amount_t;
BEGIN
   ...
END;

1-10.

You can’t anchor (use %TYPE) against a CONSTANT; it must be a variable.

1-11.

Unlike the folks who wrote the PL/SQL language, we developers are not allowed to “constrain” our own SUBTYPEs. In other words, after the IS keyword you cannot supply a datatype declaration that limits the size or length explicitly. Note that this restriction is relaxed in Oracle8i .

1-12.

You receive this error:

ORA-06502: PL/SQL: numeric or value error

because a variable assigned the type SIGNTYPE can have only one of three values: –1, 1, or NULL.

1-13.

Statements (b) and (c) both describe the value of anchoring.

1-14.

You can anchor to a table, a view, a column in a table or view, a cursor, or a scalar PL/SQL variable.

1-15.

One would hope that this function returns a BOOLEAN, as in:

FUNCTION is_value_in_list (list IN VARCHAR2, value IN VARCHAR2)
   RETURN BOOLEAN;

Oracle Forms documentation for the GET_GROUP_CHAR_CELL function unfortunately offers an example program named is_value_in_list that returns a number. If you name programs inaccurately, developers will have a much harder time understanding and using those programs.

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.

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 reference that variable with a %TYPE in your SUBTYPE statement. The subtype then inherits the constraint.

1-18.

Here are three rules to keep in mind when working with NULLs:

1-19.

These operators allow you to work with NULLs in a structured way: