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 THEN    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) THEN    IF calc_totals (1994, company_id_in => 1005)    THEN       display_sales_figures (1005);    ELSE       contact_vendor    END IF; ELSE    contact_vendor; END IF;
   
   

3. Rewrite the following IF statement to get rid of unnecessary nested IFs:

   IF salary < 10000  THEN     bonus := 2000; ELSIF salary < 20000  THEN     bonus := 1500; ELSIF salary < 40000  THEN     bonus := 1000; ELSE    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:

   BEGIN    BEGIN       UPDATE emp SET empno = 100 WHERE empno > 5000;    EXCEPTION       WHEN OTHERS THEN NULL;    END;    BEGIN       DELETE FROM dept WHERE deptno = 10;    EXCEPTION       WHEN OTHERS THEN NULL;    END;    BEGIN       DELETE FROM emp WHERE deptno = 10;    EXCEPTION       WHEN OTHERS THEN NULL;    END; END;

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.

   DECLARE    shutting_down EXCEPTION;    PRAGRA EXCEPTION INIT (shutting_down, 1014);

   BEGIN

      ... code ... EXCEPTION    WHEN shutting_down    THEN       ... handler ... END;

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:

   WHEN SYSTEM.NO_DATA_FOUND THEN    ...

   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

   2. SQL%ROWCOUNT

   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:

      DECLARE    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; BEGIN    OPEN dept_cur (10);    FETCH dept_cur INTO dept_rec;    DBMS_OUTPUT.PUT_LINE        ('Total for department 10 is: ' || tot_rec.total_sales);    CLOSE dept_cur;    OPEN dept_cur;    FETCH dept_cur INTO dept_rec;    DBMS_OUTPUT.PUT_LINE        ('Total for department 20 is: ' || tot_rec.total_sales);    CLOSE dept_cur; END;

   2. With local module and cursor parameter:

      DECLARE    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     BEGIN       OPEN dept_cur (dept_in);       FETCH dept_cur INTO dept_rec;       DBMS_OUTPUT.PUT_LINE           ('Total for department ' || TO_CHAR (dept_in) ||           ' is: ' || tot_rec.total_sales);       CLOSE dept_cur;    END; BEGIN    display_dept (10);    display_dept (20); END;

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  IS    CURSOR emp_cur (dept_in IN INTEGER) RETURN emp%ROWTYPE; END emp; PACKAGE emp  IS    CURSOR emp_cur (dept_in IN INTEGER) RETURN emp%ROWTYPE    IS       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:

   Requirement	Builtin
   Calculate the number of days until the end of the month.	LAST_DAY SYSDATE
   Capitalize the first character in a word and lower-case the rest of the word.	INITCAP
   Convert a date to a string.	TO_CHAR
   Convert a number to a string.	TO_CHAR
   Convert a string to a date.	TO_DATE
   Convert a string to lower case.	LOWER
   Determine the length of a string.	LENGTH
   Determine the place of a character in the collating sequence of the character set used by the database.	ASCII
   Extract the last four characters in a string.	SUBSTR
   Extract the word found between the first and second _ delimiters in a string.	INSTR and SUBSTR
   Fill out a number in a string with leading zeroes.	LPAD
   Find the last blank in a string.	INSTR
   Find the Saturday nearest to the last day in March 1992.	ROUND
   Find the third S in a string	UPPER and INSTR
   Get the first day in the month for a specified date.	TRUNC
   How many months are between date1 and date2 ?	MONTHS_BETWEEN
   I store all my names in uppercase in the database, but want to display them in reports in upper- and lowercase.	INITCAP
   If it is High Noon in New York, what time is it in Calcutta?	NEW_TIME
   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 #.	REPLACE
   Return the error message associated with a SQL error code.	SQLERRM
   Return the largest integer less than a specified value.	FLOOR or (TRUNC and DECODE)
   Review all new hires on the first Wednesday after they'd been working for three months.	NEXT_DAY ADD_MONTHS
   Strip all leading numeric digits from a string.	LTRIM
   What is the current date and time?	SYSDATE
   What is the date of the last day in the month?	LAST_DAY

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	Returns
   SUBSTR (curious_george, -1)	!
   SUBSTR (curious_george, 1, 7)	Curious
   SUBSTR (curious_george, 9 6)	George
   SUBSTR (curious_george, -8, 2)	he
   SUBSTR (curious_george, INSTR (curious_george, -1, ' ') + 1)	gets!
   SUBSTR (curious_george, INSTR (curious_george, -1, ' ', 3) + 1, LENGTH ('cute'))	what
   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); 100 SQL> execute DBMS_OUTPUT.PUT_LINE ('     Five spaces in'); Five spaces in SQL> execute DBMS_OUTPUT.PUT_LINE (NULL); ERROR at line 1: ORA-06550: line 1, column 7: PLS-00307: too many declarations of 'PUT_LINE' match this call SQL> execute DBMS_OUTPUT.PUT_LINE (SYSDATE < SYSDATE - 5); 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?
   calc_profit (1005, profit_level, 1995, 'ALL', 'FINANCE');	Great
   calc_profit (new_company, profit_level);	Bad. Must supply value for fiscal year.
   calc_profit (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.
   calc_profit (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);

   and

   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:

• It is ugly. Everything is in uppercase, which makes the code hard to read. Indentation in the body of the function is inconsistent. All the code between the BEGIN and END statements should be indented. Then within an IF statement, all code should be indented another level. It indents unevenly and also uses different formats for IF statement indentation within this single program. This poor formatting makes it even more difficult to understand the logical flow of the program.

• There are side-effects with IN OUT parameters. A function should never have OUT or IN OUT parameters. The point of a function is to return a value through its RETURN clause. If you need to return multiple values, you can either return a composite data structure (a PL/SQL table or record) or change the function to a procedure.

• The nu_inout argument does not need to be IN OUT, only OUT. The nu_inout argument is only referenced on the left side of an assignment operator. It is, therefore, simply an OUT parameter.

• No value is returned if name_inout is NULL. If the name_inout argument is NULL, then this function never executes a RETURN statement. This raises a runtime error and is a fatal flaw in a function's design. You should instead use an approach in which the last line of your function issues the single RETURN for that function.

• The cursor is not closed if no record is found. This is a stylistic as opposed to functional weakness. The cursor will be closed when the function terminates, since it is declared and opened locally. If the cursor were based in a package, on the other hand, it would stay open until closed explicitly when the session ends. Always closing cursors is a good habit to develop -- and you can't go wrong.

• "Magic values" of 0, 1, and 2 are used as return values of the function. The return values of this function are obscure and poorly designed. There is no way to know by simply glancing at the function what these return values signify. It would be even more difficult for a user of the function to use getkey_clrtyp properly simply by looking at the header of the function. You should always avoid these kinds of "magic values" and literals in your code. If specific values have special meanings, you are much better off defining these as constants in a package and then referencing those constants both inside and outside the function (see the recoding of the function below for an example of this approach).

• The function name does not describe the value returned. The name getkey_clrtyp describes in the most general terms the objective of the function, but it in no way indicates what kind of value is being returned by the function. Since a function encapsulates a returned value, the name of the function should describe the value.

• There are multiple RETURN statements. A very fundamental rule for structured programming is that there should be one way in to a program and one way out of the program. In PL/SQL terms, this means that you should only have one RETURN statement in the executable section of the function. When you have more than one RETURN, the code is more difficult to understand, debug and enhance.[ 1 ]

  [1] You should also have a RETURN statement for each exception handler, but that is a separate issue and in no way contradicts this structured programming rule.

• There is an unused variable. I declare the typ_nu variable, but then never use it in the program. You are much better off without such clutter.

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.