Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: A.1 ExercisesAppendix A
Appendix: PL/SQL Exercises

A.2 Solutions

This section contains the answers to the exercises shown earlier in this appendix.

A.2.1 Conditional Logic

  1. Rewrite the following IF statements so that you do not use the IF statement to set the value of no_revenue. What is the difference between the two statements?

    The first IF statement can be simplified to:

    no_revenue := NVL (total_sales, 1) <= 0;

    I use NVL to make sure that no_revenue is set to FALSE, as would happen in the original IF statement. Without using NVL, I will set no_revenue to NULL if total_sales is NULL.

    The second statement is a bit more complicated, again due to the complexities of handling NULL values. If total_sales is NULL, the IF statement does not assign a value to no_revenue at all. NULL is never less than or equal to any number. So I still need an IF statement, but not (strictly speaking!) to assign a value to no_revenue:

    IF total_sales IS NOT NULL
       no_revenue := total_sales <= 0;
    END IF;
  2. Rewrite the following IF statement to work as efficiently as possible under all conditions, given the following information: the calc_totals numeric function takes 3 minutes to return its value, while the overdue_balance Boolean function returns TRUE/FALSE in less than a second.

    IF NOT overdue_balance (company_id_in => 1005)
       IF calc_totals (1994, company_id_in => 1005)
          display_sales_figures (1005);
       END IF;
    END IF;
  3. Rewrite the following IF statement to get rid of unnecessary nested IFs:

    IF salary < 10000 
       bonus := 2000;
    ELSIF salary < 20000 
       bonus := 1500;
    ELSIF salary < 40000 
       bonus := 1000;
       bonus := 500;
    END IF;
  4. Which procedure will never be executed in this IF statement?

    The call to queue_order_for_addtl_parts will never run since the previous ELSIF clause will always be true first.

A.2.2 Loops

  1. How many times does the following loop execute?

    Not a single time. The first number in the range scheme must always be the smaller value.

  2. Select the type of loop (FOR, WHILE, simple) appropriate to meet each of the following requirements:

    1. Numeric FOR loop.

    2. Simple or WHILE loop. The main thing is to not use a FOR loop since there is a conditional exit.

    3. Display the name and address of each employee returned by the cursor. Cursor FOR loop.

    4. WHILE loop, since there are conditions under which you do not want the loop body to execute even a single time.

  3. Identify the problems with (or areas for improvement in) the following loops. How would you change the loop to improve it?

    1. Do not use a generic loop index name (i). In addition, the conditional EXIT from the FOR loop should be removed. Instead, use a FOR loop that loops from 1 to 76 (can you see why?).

    2. This loop relies on two diferent FETCH statements. Better off using a simple loop and just a single FETCH inside the loop. In addition, you should not EXIT from inside a WHILE loop. You should instead rely on the loop boundary condition.

    3. Never attempt to change the values used in the range scheme. It will not actually affect the execution of the loop, since the range scheme is evaluated only once, at the time the loop begins. Such an assignment remains, however, a very bad programming practice.

    4. First, this program will not compile, since emp_rec record has not been defined. Second, you don't really need to declare that record because you should instead use instead a cursor FOR loop to reduce code volume.

    5. Do not use EXIT WHEN inside WHILE loop. Should only rely on changes in loop boundary condition.

    6. You should not use a PL/SQL loop at all. Instead employ straight SQL as follows:

      UPDATE monthly_sales 
         SET pct_of_sales = 100
       WHERE company_id = 10006
         AND month_number BETWEEN 1 AND 12;
    7. Never declare the cursor loop index (emp_rec). The reference to emp_rec.salary after the loop references the still-null record declared in the block, not the record filled inside the loop (which has terminated and erased that record). Also, the final CLOSE will attempt to close a closed cursor.

    8. Do not use a PL/SQL loop. Instead, INSERT directly from the SELECT statement.

      INSERT INTO occupancy_history (pet_id, name, checkout_date)
         SELECT pet_id, name, checkout_date 
           FROM occupancy
          WHERE checkout_date IS NOT NULL;
  4. How many times does the following WHILE loop execute?

    An infinite number of times. This is an infinite WHILE loop. The local module called inside the loop never returns NULL for step_out, so next_analysis_step is never NULL, so the loop never terminates.

  5. Rewrite the following loop so that you do not use a loop at all.

    This is a "phony loop." You don't need the loop or the IF statement. Just execute the code sequentially.

    give_bonus (president_id, 2000000);
    give_bonus (ceo_id, 5000000);
  6. What statement would you remove from this block? Why?

    Remove the declaration of the emp_rec record. The cursor FOR loop implicitly declares a record of the right structure for you. In fact, the emp_rec record declared right after the cursor is never used in the block.

A.2.3 Exception Handling

  1. In each of the following PL/SQL blocks, a VALUE_ERROR exception is raised (usually by an attempt to place too large a value into a local variable). Identify which exception handler (if any -- the exception could also go unhandled) will handle the exception by writing down the message that will be displayed by the call to PUT_LINE in the exception handler. Explain your choice.

    1. VALUE_ERROR is raised because "Steven" has six characters and the maximum length of string is five characters.

    2. Exception is unhandled. There is no exception section.

    3. "Inner block" is displayed.

    4. "Inner block" is displayed.

    5. "Outer block" is displayed.

    6. "Outer block" is displayed. The inner NO_DATA_FOUND handler does not come into play.

  2. Write a PL/SQL block that allows all of the following SQL DML statements to execute, even if the any of the others fail:

          UPDATE emp SET empno = 100 WHERE empno > 5000;
          DELETE FROM dept WHERE deptno = 10;
          DELETE FROM emp WHERE deptno = 10;
  3. Write a PL/SQL block that handles by name the following Oracle error:

    ORA-1014: ORACLE shutdown in progress.

    The exception handler should handle the error by propagating the exception by in turn raising a VALUE_ERROR exception. Hint: use the EXCEPTION INIT pragma.

       shutting_down EXCEPTION;
       PRAGRA EXCEPTION INIT (shutting_down, 1014);
       ... code ...
       WHEN shutting_down
          ... handler ...
  4. When the following block is executed, which of these two messages are displayed?

    The ORA-1403 error message is displayed. The exception, in other words, goes unhandled. Since I have defined a local exception with same name as system predefined, that identifier overrides the system exception in this block. So when the SELECT statement raised NO_DATA_FOUND and PL/SQL moves to the exception section, it does not find a match.

    You could make sure that the system exception is handled by changing the handler as follows:


    By qualifying the name of the exception, you tell PL/SQL which one you want to handle.

  5. I create the getval package as shown below. I then call DBMS_OUTPUT.PUT_LINE to display the value returned by the getval.get function. What is displayed on the screen?

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 2

    The error, in other words, goes unhandled. When I first reference the getval.get function, the package data is instantiated for my session. It then attempts to declare the private v variable. The default value for the variable is, unfortunately, too large for the variable, so PL/SQL raises the VALUE_ERROR exception. The exception section of the package only can handle exceptions raised in the initialization section of the package, so this error is unhandled and quite unhandleable from within the package itself.

A.2.4 Cursors

  1. What cursor-related statements are missing from the following block?

    Missing a CLOSE statement and a declaration of the emp_rec record.

  2. What statement should be removed from the following block?

    Remove the declaration of emp_rec.

  3. Name the cursor attribute (along with the cursor name) you would use (if any) for each of the following requirements:

    1. company_cur%NOTFOUND


    3. emp_cur%ISOPEN

    4. sales_cur%FOUND

    5. No cursor attribute can be used. Instead, rely on an exception handler for NO_DATA_FOUND.

  4. What message is displayed in the following block if the SELECT statement does not return a row?

    "No data found." If an implicit cursor does not return any rows, PL/SQL raises the NO_DATA_FOUND exception.

  5. What message is displayed in the following block if there are no employees in department 15?

    "No employees in department!" is displayed. This SELECT statement does not find any rows, but since it is a group operation, SQL returns a single value of 0.

  6. If you fetch past the last record in a cursor's result set, what will happen?

    Nothing. PL/SQL does not raise any errors, nor does it return any data into the record or variable list of the FETCH statement.

  7. How would you change the SELECT statement in the following block's cursor so that the block can display the sum of salaries in each department?

    Add a column alias "total sales" right after the SUM (SAL) expression.

  8. Rewrite the following block to use a cursor parameter. Then rewrite to use a local module, as well as a cursor parameter.

    1. With cursor parameter:

         CURSOR dept_cur (dept_in IN emp.deptno%TYPE) IS 
            SELECT dname, SUM (sal) total_sales  
              FROM emp
             WHERE deptno = dept_in;
         dept_rec dept_cur%ROWTYPE;
         OPEN dept_cur (10);
         FETCH dept_cur INTO dept_rec;
            ('Total for department 10 is: ' || tot_rec.total_sales);
         CLOSE dept_cur;
         OPEN dept_cur;
         FETCH dept_cur INTO dept_rec;
            ('Total for department 20 is: ' || tot_rec.total_sales);
         CLOSE dept_cur;
    2. With local module and cursor parameter:

         CURSOR dept_cur (dept_in IN emp.deptno%TYPE) IS 
            SELECT dname, SUM (sal) total_sales  
              FROM emp
             WHERE deptno = dept_in;
         dept_rec dept_cur%ROWTYPE;
         PROCEDURE display_dept (dept_in IN emp.deptno%TYPE) IS 
            OPEN dept_cur (dept_in);
            FETCH dept_cur INTO dept_rec;
               ('Total for department ' || TO_CHAR (dept_in) ||
                ' is: ' || tot_rec.total_sales);
            CLOSE dept_cur;
         display_dept (10);
         display_dept (20);
  9. Place the following cursor inside a package, declaring the cursor as a public element (in the specification). The SELECT statement contains all of the columns in the emp table, in the same order.

    PACKAGE emp 
       CURSOR emp_cur (dept_in IN INTEGER) RETURN emp%ROWTYPE;
    END emp;
    PACKAGE emp 
       CURSOR emp_cur (dept_in IN INTEGER) RETURN emp%ROWTYPE
          SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
            FROM emp
           WHERE deptno = dept_in;
    END emp;

A.2.5 Builtin Functions

  1. Identify the appropriate builtin to use for each of the following requirements:



    Calculate the number of days until the end of the month.



    Capitalize the first character in a word and lower-case the rest of the word.


    Convert a date to a string.


    Convert a number to a string.


    Convert a string to a date.


    Convert a string to lower case.


    Determine the length of a string.


    Determine the place of a character in the collating sequence of the character set used by the database.


    Extract the last four characters in a string.


    Extract the word found between the first and second _ delimiters in a string.


    Fill out a number in a string with leading zeroes.


    Find the last blank in a string.


    Find the Saturday nearest to the last day in March 1992.


    Find the third S in a string


    Get the first day in the month for a specified date.


    How many months are between date1 and date2?


    I store all my names in uppercase in the database, but want to display them in reports in upper- and lowercase.


    If it is High Noon in New York, what time is it in Calcutta?


    Remove a certain prefix from a string (for example, change std_company_id to company_id).

    LTRIM or (better yet) SUBSTR

    Replace all instances of _ with a #.


    Return the error message associated with a SQL error code.


    Return the largest integer less than a specified value.


    Review all new hires on the first Wednesday after they'd been working for three months.



    Strip all leading numeric digits from a string.


    What is the current date and time?


    What is the date of the last day in the month?


  2. What portion of the string "Curious George deserves what he gets!" (assigned to variable curious_george) is returned by each of the following calls to SUBSTR:

    SUBSTR Usage


    SUBSTR (curious_george, -1)
    SUBSTR (curious_george, 1, 7)
    SUBSTR (curious_george, 9 6)
    SUBSTR (curious_george, -8, 2)
    SUBSTR (curious_george,
            INSTR (curious_george, -1, ' ') + 1)
    SUBSTR (curious_george,
            INSTR (curious_george, -1, ' ', 3) + 1,
            LENGTH ('cute'))
    SUBSTR (curious_george, -1 *
            LENGTH (curious_george))

    entire string

A.2.6 Builtin Packages

  1. What program would you use to calculate the elapsed time of your PL/SQL code execution? To what degree of accuracy can you obtain these timings?

    The DBMS_UTILITY.GET_TIME function returns the number of hundredths of seconds that have elapsed since the last call to DBMS_UTILITY.GET_TIME. So if you compare consecutive calls to this builtin, you have the elapsed time down to the hundredth of a second.

  2. What would you call to make your PL/SQL program pause for a specified number of seconds? What other techniques can you think of which would have this same effect?

    The DBMS_LOCK.SLEEP procedure will put your PL/SQL program to sleep for the number of seconds you pass to it. You can also make a PL/SQL program pause by calling a number of the DBMS_PIPE builtins, such as RECEIVE_MESSAGE and SEND_MESSAGE.

  3. What package can you use to determine if the current session has issued a COMMIT? How would you go about obtaining this information?

    The DBMS_LOCK allows you to determine if a COMMIT has occurred. You use the REQUEST procedure to request a lock specifying TRUE for release_on_commit. Then later in your program you can request this same lock. If you can get the lock, a commit has been performed.

  4. What do you see when you execute the following statements in SQL*Plus (assuming that you have already called SET SERVEROUTPUT ON)?

    I will show the output from each of these calls and then explain them afterwards.

    SQL> execute DBMS_OUTPUT.PUT_LINE (100);
    SQL> execute DBMS_OUTPUT.PUT_LINE ('     Five spaces in');
    Five spaces in
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00307: too many declarations of 'PUT_LINE' match this call
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
    SQL> execute DBMS_OUTPUT.PUT_LINE (TRANSLATE ('abc', 'a', NULL));
    SQL> execute DBMS_OUTPUT.PUT_LINE (RPAD ('abc', 500, 'def'));
    ERROR at line 1:
    ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line

    The first answer of "100" verifies that the DBMS_OUTPUT.PUT_LINE procedure (DOPL for short) puts a line of information to the screen or standard out from inside your PL/SQL program. In the second answer, you notice that the text is not five spaces in. That is because DOPL automatically LTRIMs your text on display in SQL*Plus. When I tried to display NULL, DBMS_OUTPUT could not figure out which of the overloaded versions of PUT_LINE to call because NULL does not have a datatype.

    When I tried to display the value returned by SYSDATE < SYSDATE - 5, DOPL raised an exception because it is not overloaded for Boolean values.

    When I tried to display the output from the TRANSLATE function, nothing happened! This non-event was caused by two factors: first, when you specify NULL for the replacement character set in TRANSLATE, that builtin returns NULL. Second, when you try to display a NULL string (which is different from the NULL literal) or blank line, DOPL simply ignores your request and does nothing.

    When I attempted to display the string "abc" right-padded to a length of 500 with the string "def", I was reminded that DOPL cannot handle pieces of data with more than 255 bytes.

  5. When an error occurs in your program, you want to be able to see which program is currently executing. What builtin packaged function would you call to get this information? If the current program is a procedure named calc_totals in the analysis package, what would you see when you call the builtin function?

    The DBMS_UTILITY.FORMAT_CALL_STACK returns the current execution stack in PL/SQL. If the current program is analysis.calc_totals, however, the string returned by FORMAT_CALL_STACK only tells you that you are executing analysis. It does not know which program inside the package you are running.

  6. You want to build a utility for DBAs that would allow them to create an index from within a PL/SQL program. Which package would you use? Which programs inside that package would be needed?

    To perform SQL DDL inside PL/SQL, you use the DBMS_SQL package. With this package, you dynamically construct the string to create the index and then call the following elements of DBMS_SQL: OPEN_CURSOR to allocate memory for the dynamic SQL; PARSE to parse the statement; and CLOSE_CURSOR to close the cursor. Since you are working with DDL, a parse also executes and commits.

  7. You need to run a stored procedure named update_data every Sunday at 4 AM to perform a set of batch processes. Which builtin package would you use to perform this task?

    You need to pass a string to the submit program to tell it how often to run update_data. What would that string be? The DBMS_JOB package allows you to queue up stored procedures to be executed on a regular or one-time basis. You would call the SUBMIT program of DBMS_JOB. When you call SUBMIT, you pass it a string (which will be executed as dynamic PL/SQL) defining the next time the program will be executed. SYSDATE stands for now and this is the string which means "every Sunday at 4 AM":

    'NEXT_DAY (TRUNC (SYSDATE), ''SUNDAY'') + 4/24'

A.2.7 Modules

  1. In each of the following modules, identify changes you would make to improve their structure, performance or functionality.

    1. Use single RETURN statement at end of function to return value. Put in assertion routine or other form of check to handle situation when invalid (unhandled) status code is passed to routine.

    2. Either remove the OUT parameter or change the function to a procedure.

    3. Do not use a RAISE statement to handle successful completion of function. Consider using explicit cursor rather than implicit cursor.

    4. Do not issue a RETURN from inside a loop. Also, do not issue a RETURN from inside a procedure.

  2. Given the header for calc_profit below, which of the following calls to calc_profit are valid:

    Call to calc_profit

    Good/Bad? Why?

       (1005, profit_level, 1995, 'ALL', 'FINANCE');


       (new_company, profit_level);

    Bad. Must supply value for fiscal year.

       (company_id_in => 32, fiscal_year_in => 1995,
        profit_out => big_number);

    Good. All three mandatory params are present, even if not in right order.

       (company_id_in => 32, division_in => 'ACCTG',
        profit_out => 1000);

    Bad. The actual profit_out argument must be a variable.

  3. Suppose you had to use dispdates to satisfy the following requirement: "Display the list of company start dates stored in the date table without any header." I can think of two ways do this:

    dispdates (company_list, num_companies);


    dispdates (company_list, num_companies, NULL);

    Which of these implementations would you choose and why? Is there any reason to choose one over the other?

    It would be tempting to take the first approach. It is less typing. The second form is, however, the correct solution. The reason is this: you were asked to display a list without a header -- with, in other words, a NULL header. You were not asked to display a list with the default header. If you were asked to use the default value, then you can and should simply rely on the default. If you were asked to skip the header, then you should explicitly request a NULL header when you call dispdates. That way, if the default value ever changes, your code is not affected and the format of the display does not change.

A.2.8 Module Evaluation: Foreign Key Lookup

You were asked to evaluate a function that performs a foreign key lookup. My evaluation follows. You will undoubtedly have found other problems as well.

There are many, many problems with the getkey_clrtyp function:

Did you find any other problems? I would not be the least bit surprised. Every time I have gone over this program in a class, the students have uncovered additional areas for improvement.

A.2.8.1 A rewrite of the getkey_clrtyp function

The following version of getkey_clrtyp incorporates many of the comments in the previous section. Notice that it is no longer even a function; I have changed it to a procedure so that I can take in and return as many values as needed.

PROCEDURE getkey_clrtyp 
   (name_inout IN OUT VARCHAR2, 
     nu_inout IN OUT NUMBER,
     get_status_out OUT INTEGER) 
   CURSOR clrtyp_cur IS 
      SELECT typ_nu, type_ds 
        FROM caller_type
       WHERE type_ds LIKE name_inout || '%';

   clrtyp_rec clrtyp_cur%ROWTYPE;
   next_rec clrtyp_cur%ROWTYPE;
   retval NUMBER := NULL;
   IF name_inout IS NULL
      get_status_out := get.nullname;
      OPEN clrtyp_cur; FETCH ...;
      IF clrtyp_cur%NOTFOUND
         get_status_out := get.notfound;
         FETCH clrtyp_cur INTO next_rec;
         IF clrtyp_cur%NOTFOUND
            get_status_out := get.unique_match;
            get_status_out := get.dup_match;
         END IF;
         nu_inout := clrtyp_rec.cllr_typ_nu;
         name_inout := clrtyp_rec.cllr_typ_ds;
      END IF;
      CLOSE clrtyp_cur;
   END IF;
END getkey_clrtyp;

Previous: A.1 ExercisesAdvanced Oracle PL/SQL Programming with Packages 
A.1 ExercisesBook Index 

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference