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:

6-23.

The best way to scan the contents of an index-by table is shown in (d). Start with the first row in the table. If this returns NULL, the table is empty, and you stop immediately. Otherwise, display the present and then move on to the next defined row with the NEXT method. It is efficient, very readable, and doesn’t raise NO_DATA_FOUND errors.

Now what is wrong with each of the other approaches?

  1. Reliance on the numeric FOR loop in this first attempt has a big problem: if the table is empty, FIRST and LAST return NULL. You would like to think that a numeric FOR loop from NULL to NULL—NULL to anything for that matter—would simply not execute. Sadly, this is not the case. PL/SQL will try to run the body of the loop and immediately raise a VALUE_ERROR exception. Very difficult to track down!

  2. You have placed the numeric FOR loop inside a conditional. As a result, you execute the FOR loop only when you know that there is something in the table. So you don’t get the VALUE_ERROR exception. There is still a problem, however; You are assuming that the table is filled sequentially (i.e., there is a present for each year between first and last). This is always a dangerous assumption to make about an index-by table, and in this case it is a false assumption. The result? This loop raises the NO_DATA_FOUND exception.

  3. Here you use the EXISTS operator to make sure you never raise the NO_DATA_FOUND exception. This loop displays correctly each of my son’s favorite presents. The problem with ...

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