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

Intermediate

Q:

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;

Q:

1-8.

The statements about the DATE datatype are:

  1. Both true and false. Prior to Oracle 7.3, the maximum date was January 1, 4712. In later versions of Oracle, the maximum date has now been set to December 31, 9999.

  2. True

  3. True. The Oracle DATE is really a date-time data structure.

  4. False. A date variable records the time down only to the nearest second.

  5. False. No matter how you specify the date value, the internal format always uses a four-digit year.

Q:

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;

Q:

1-10.

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

Q:

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 .

Q:

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 ...

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