Lab 2.2 Exercise Answers

This section gives you some suggested answers to the questions in Lab 2.2, 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.

2.2.1 Answers

a)	If the value of v_num is equal to 10, what output is printed on the screen?
A1:	Answer: Your output should look like the following: Enter value for v_num: 10 old 2: v_num NUMBER := &sv_num; new 2: v_num NUMBER := 10; The value of v_result is: 100 PL/SQL procedure successfully completed.

The first line of the output asks you to provide a value for the substitution variable sv_num . Then the actual substitution is shown to you in lines 2 and 3. In the second line, you can see the original statement from the PL/SQL block. In the third line, you can see the same statement with the substitution value. The next line shows the output produced by the DBMS_OUTPUT.PUT_LINE statement. Finally, the last line informs you that your PL/SQL block was executed successfully.

b)	What is the purpose of using a substitution variable?
A2:	Answer: A substitution variable allows the PL/SQL block to accept information provided by the user at the time of execution. Substitution variables are used for input purposes only. They cannot be used to output values for a user.
c)	Why is it considered a good practice to enclose substitution variables with single quotes for string datatypes?
A3:	Answer: A program cannot depend wholly on a user to provide text information in single quotes. Enclosing a substitution variable with single quotes allows a program to be less error-prone .

2.2.2 Answers

a)	What was printed on the screen?
A1:	Answer: Your output should look like the following: Today is Friday PL/SQL procedure successfully completed.

In this example, SQL*Plus does not ask you to enter the value of the v_day variable because no substitution variable is used. The value of v_day is computed with the help of TO_CHAR and SYSDATE functions. Then it is displayed on the screen with the help of the DBMS_OUTPUT.PUT_LINE statement.

b)	What will be printed on the screen if the statement SET SERVEROUTPUT OFF is issued? Why?
A2:	Answer: If the statement SET SERVEROUTPUT OFF is issued prior to the execution of the PL/SQL block, no output will be printed on the screen. The output will look like following: PL/SQL procedure successfully completed.

It is important to note that when substitution variables are used, the user is prompted to enter the value for the variable regardless of the SERVEROUTPUT setting. The prompt for the user is provided by SQL*Plus and does not depend on the option chosen for the SERVEROUTPUT.

c)	How would you change the script to display the time of the day as well?
A3:	Answer: Your script should look similar to this script. Changes are shown in bold letters . -- ch02_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_day VARCHAR2(20); BEGIN v_day := TO_CHAR(SYSDATE, 'Day, HH24:MI'); DBMS_OUTPUT.PUT_LINE ('Today is ' v_day); END;

The statement shown in bold has been changed in order to display time of the day as well. The output produced by this PL/SQL block is as follows :

  Today is Friday , 23:09   PL/SQL procedure successfully completed.