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

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial