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

10-6.

Which of the following strings are valid cursor attributes (which means they are “attached” as a suffix to the name of the cursor)?

  1. %ROWNUM

  2. %FOUND

  3. %TOO_MANY_ROWS

  4. %NO_DATA_FOUND

  5. %NOTFOUND

  6. %ROWCOUNT

  7. %ISCLOSED

  8. %ISOPENED

  9. %ISOPEN

10-7.

Which of the following uses of cursor attributes are valid? If they are not valid, what is the problem? Assume that the following cursor is declared in the same block when referenced in the problems:

CURSOR possibly_in_danger_cur IS
   SELECT status
     FROM genetically_modified_foods
    WHERE product = 'CORN'
      AND animal = 'MONARCH BUTTERFLY';
  1. BEGIN
       IF possibly_in_danger_cur%FOUND
       THEN
          OPEN possibly_in_danger_cur;
  2. BEGIN
       UPDATE jobs_to_mexico
          SET total_count = 10000000
        WHERE nafta_status = 'IN FORCE';
       DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);
  3. BEGIN
       FOR big_loss IN possibly_in_danger_cur
       LOOP
          IF possibly_in_danger_cur%ROWCOUNT
          THEN
             ...
  4. BEGIN
       OPEN possibly_in_danger_cur;
       FETCH possibly_in_danger_cur INTO I_am_sad;
       IF I_am_sad%FOUND
       THEN
  5. FOR indx IN 1 .. 12
    LOOP
       DELETE FROM genetically_modified_foods
        WHERE product = 'CORN';
       DBMS_OUTPUT.PUT_LINE (
          'All gone:  ' || genetically_modified_foods%ROWCOUNT);
    END LOOP;

10-8.

What is wrong with the following code? Will you eventually run out of open cursors in the session if you execute this program, say, 100,000 times?

CREATE OR REPLACE FUNCTION totalsales (year_in IN INTEGER) RETURN NUMBER IS CURSOR sales_cur IS SELECT SUM (amt) FROM ...; total NUMBER; BEGIN OPEN sales_cur; FETCH sales_cur INTO total; RETURN total; CLOSE sales_cur; ...
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