Lab 5.3 Exercise Answers | Oracle PL[s]SQL by Example

This section gives you some suggested answers to the questions in Lab 5.3, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

5.3.1 Answers

What output is printed on the screen if the value of 100 is entered for the temperature, and the letter "C" is entered for the scale?

A1:

Answer: Your output should look like the following:

  Enter value for sv_temp_in: 100   old 2: v_temp_in NUMBER := &sv_temp_in;   new 2: v_temp_in NUMBER := 100;   Enter value for sv_scale_in: C   old 3: v_scale_in CHAR := '&sv_scale_in';   new 3: v_scale_in CHAR := 'C';   New scale is: F   New temperature is: 212   PL/SQL procedure successfully completed.

Once the values for v_temp_in and v_scale_in have been entered, the condition

  v_scale_in != 'C' AND v_scale_in != 'F'

of the outer IF statement evaluates to FALSE, and control is passed to the ELSE part of the outer IF statement. Next, the condition

  v_scale_in = 'C'

of the inner IF statement evaluates to TRUE, and the values of the variables v_temp_out and v_scale_out are calculated. Control is then passed back to the outer IF statement, and the new value for the temperature and the scale are displayed on the screen.

b)	Try to run this script without providing a value for the temperature. What message will be displayed on the screen? Why?
A2:	Answer: If the value for the temperature is not entered, the script will not compile at all.

The compiler will try to assign a value to v_temp_in with the help of the substitution variable. Because the value for v_temp_in has not been entered, the assignment statement will fail, and the following error message will be displayed.

  Enter value for sv_temp_in:   old 2: v_temp_in NUMBER := &sv_temp_in;   new 2: v_temp_in NUMBER := ;   Enter value for sv_scale_in: C   old 3: v_scale_in CHAR := '&sv_scale_in';   new 3: v_scale_in CHAR := 'C';   v_temp_in NUMBER := ;   *   ERROR at line 2:   ORA-06550: line 2, column 27:   PLS-00103: Encountered the symbol ";" when expecting one of the following:   ( - + mod not null <an identifier>   <a double-quoted delimited-identifier> <a bind variable> avg   count current exists max min prior sql stddev sum variance   cast <a string literal with character set specification>   <a number> <a single-quoted SQL string>   The symbol "null" was substituted for ";" to continue.

You have probably noticed that even though the mistake seems small and insignificant, the error message is fairly long and confusing.

c)	Try to run this script providing an invalid letter for the temperature scale, for example, letter "V." What message will be displayed on the screen? Why?
A3:	Answer: If an invalid letter is entered for the scale, the message "This is not a valid scale" will be displayed on the screen.

The condition of the outer IF statement will evaluate to TRUE. As a result, the inner IF statement will not be executed at all, and the message "This is not a valid scale" will be displayed on the screen.

Assume that letter "V" was typed by mistake. This example will produce the following output:

  Enter value for sv_temp_in: 45   old 2: v_temp_in NUMBER := &sv_temp_in;   new 2: v_temp_in NUMBER := 45;   Enter value for sv_scale_in: V   old 3: v_scale_in CHAR := '&sv_scale_in';   new 3: v_scale_in CHAR := 'V';   This is not a valid scale   PL/SQL procedure successfully completed.

Rewrite this script so that if an invalid letter is entered for the scale, v_temp_out is initialized to zero and v_scale_out is initialized to C.

A4:

Answer: Your script should look similar to the following script. Changes are shown in bold letters . Notice that the two last DBMS_OUTPUT.PUT_LINE statements have been moved from the body of the outer IF statement.

 -- ch05_4b.sql, version 2.0 DECLARE v_temp_in NUMBER := &sv_temp_in; v_scale_in CHAR := '&sv_scale_in'; v_temp_out NUMBER; v_scale_out CHAR; BEGIN IF v_scale_in != 'C' AND v_scale_in != 'F' THEN DBMS_OUTPUT.PUT_LINE ('This is not a valid scale');  v_temp_out := 0;   v_scale_out := 'C';  ELSE IF v_scale_in = 'C' THEN v_temp_out := ( (9 * v_temp_in) / 5 ) + 32; v_scale_out := 'F'; ELSE v_temp_out := ( (v_temp_in - 32) * 5 ) / 9; v_scale_out := 'C'; END IF; END IF;  DBMS_OUTPUT.PUT_LINE ('New scale is: 'v_scale_out);   DBMS_OUTPUT.PUT_LINE ('New temperature is: '   v_temp_out);  END;

The preceding script produces the following output:

  Enter value for sv_temp_in: 100   old 2: v_temp_in NUMBER := &sv_temp_in;   new 2: v_temp_in NUMBER := 100;   Enter value for sv_scale_in: V   old 3: v_scale_in CHAR := '&sv_scale_in';   new 3: v_scale_in CHAR := 'V';   This is not a valid scale.   New scale is: C   New temperature is: 0   PL/SQL procedure successfully completed.