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

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

20.1.1 Answers

a)	Explain the script ch20_1a.sql shown above.
A1:	Answer: The declaration portion of the script contains a declaration of the string that contains the dynamic SQL statement, and three variables to hold student's ID, first, and last names respectively. The executable portion of the script contains a dynamic SQL statement with one bind argument that is used to pass the value of student ID to the SELECT statement at run-time. The dynamic SQL statement is executed via the EXECUTE IMMEDIATE statement with two options, INTO and USING. The INTO clause contains two variables, `v_first_name` and `v_last_name` . These variables contain results returned by the SELECT statement. The USING clause contains the variable `v_student_id` that is used to pass a value to the SELECT statement at run-time. Finally, two DBMS_OUTPUT.PUT_LINE statements are used to display the results of the SELECT statement on the screen. When run, the script produces the following output: Enter value for sv_student_id: 105 old 3: v_student_id NUMBER := &sv_student_id; new 3: v_student_id NUMBER := 105; First Name : Angel Last Name: Moskowitz PL/SQL procedure successfully completed.
b)	Modify the script so that the student's address (street, city, state, and zip code) is displayed on the screen as well.
A2:	Answer: Your script should look similar to the script shown below. Changes are shown in bold letters . -- ch20_1b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200); v_student_id NUMBER := &sv_student_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address' ' ,b.city, b.state, b.zip' ' FROM student a, zipcode b' ' WHERE a.zip = b.zip' ' AND student_id = :1' ; EXECUTE IMMEDIATE sql_stmt INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip USING v_student_id; DBMS_OUTPUT.PUT_LINE ('First Name: 'v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: 'v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: 'v_street); DBMS_OUTPUT.PUT_LINE ('City: 'v_city); DBMS_OUTPUT.PUT_LINE ('State: 'v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: 'v_zip); END;

In the script above, you declare four new variables, v_street , v_city , v_state , and v_zip . Next, you modify the dynamic SQL statement so that it can return the student's address. As a result, you modify the INTO clause by adding the new variables to it. Next, you add DBMS_OUTPUT.PUT_LINE statements to display the student's address on the screen.

When run, the script produces the output shown below:

  Enter value for sv_student_id: 105   old 3: v_student_id NUMBER := &sv_student_id;   new 3: v_student_id NUMBER := 105;   First Name: Angel   Last Name: Moskowitz   Street: 320 John St.   City: Ft. Lee   State: NJ   Zip Code: 07024   PL/SQL procedure successfully completed.

It is important to remember that the order of variables listed in the INTO clause must follow the order of columns listed in the SELECT statement. In other words, if the INTO clause listed variables so that v_zip and v_state were misplaced while the SELECT statement remains unchanged, the scripts would generate an error as demonstrated below.

 SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200); v_student_id NUMBER := &sv_student_id; v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address' ' ,b.city, b.state, b.zip'  ' FROM student a, zipcode b'  ' WHERE a.zip = b.zip'  ' AND student_id = :1'; EXECUTE IMMEDIATE sql_stmt  -- variables v_state and v_zip are misplaced   INTO v_first_name, v_last_name, v_street, v_city, v_zip, v_state  USING v_student_id; DBMS_OUTPUT.PUT_LINE ('First Name: 'v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: 'v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: 'v_street); DBMS_OUTPUT.PUT_LINE ('City: 'v_city); DBMS_OUTPUT.PUT_LINE ('State: 'v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: 'v_zip); END;  Enter value for sv_student_id: 105   old 3: v_student_id NUMBER := &sv_student_id;   new 3: v_student_id NUMBER := 105;   DECLARE   *   ERROR at line 1:   ORA-06502: PL/SQL: numeric or value error   ORA-06512: at line 16

This error is generated because variable v_state can hold up to two characters. However, you are trying to store in it a value of zip code that contains 5 characters .

Modify the script created in the previous exercise (ch20_1b.sql) so that the SELECT statement can be run against either the STUDENT or INSTRUCTOR table. In other words, a user can specify table name used in the SELECT statement at run-time.

A3:

Answer: Your script should look similar to the script shown below. Changes are shown in bold letters.

 -- ch20_1c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE sql_stmt VARCHAR2(200);  v_table_name VARCHAR2(20) := '&sv_table_name';   v_id NUMBER := &sv_id;  v_first_name VARCHAR2(25); v_last_name VARCHAR2(25); v_street VARCHAR2(50); v_city VARCHAR2(25); v_state VARCHAR2(2); v_zip VARCHAR2(5); BEGIN sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address' ' ,b.city, b.state, b.zip'  ' FROM 'v_table_name' a, zipcode b'  ' WHERE a.zip = b.zip'  ' AND 'v_table_name'_id = :1';  EXECUTE IMMEDIATE sql_stmt INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip USING v_id; DBMS_OUTPUT.PUT_LINE ('First Name: 'v_first_name); DBMS_OUTPUT.PUT_LINE ('Last Name: 'v_last_name); DBMS_OUTPUT.PUT_LINE ('Street: 'v_street); DBMS_OUTPUT.PUT_LINE ('City: 'v_city); DBMS_OUTPUT.PUT_LINE ('State: 'v_state); DBMS_OUTPUT.PUT_LINE ('Zip Code: 'v_zip); END;

The declaration portion of the script contains a new variable, v_table_name that holds the name of a table provided at run-time by a user. In addition, the variable v_student_id has been replaced by the variable v_id since it is not known in advance what table, STUDENT or INSTRCTOR, will be accessed at run-time.

The executable portion of the script contains a modified dynamic SQL statement. Notice that the statement does not contain any information specific to the STUDENT or INSTRCUTOR tables. In other words, the dynamic SQL statement used by the previous version (ch20_1b.sql)

  sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'   ' ,b.city, b.state, b.zip'   ' FROM student a, zipcode b'   ' WHERE a.zip = b.zip'   ' AND student_id = :1';

has been replaced by

  sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'   ' ,b.city, b.state, b.zip'   ' FROM 'v_table_name' a, zipcode b'   ' WHERE a.zip = b.zip'   ' AND 'v_table_name'_id = :1';

The table name (student) has been replaced by the variable v_table_name in the FROM and the WHERE clauses.

Note that for the last two versions of the script you have used generic table aliases, 'a' and 'b', instead of 's' and 'z' or 'i' and 'z', that are more descriptive. This technique allows you to create generic SQL statements that are not based on a specific table since you do not always know it in advance.

This version of the script produces output shown below. First run is against the STUDENT table, and second run is against the INSTRUCTOR table:

  Enter value for sv_table_name: student   old 3: v_table_name VARCHAR2(20) := '&sv_table_name';   new 3: v_table_name VARCHAR2(20) := 'student';   Enter value for sv_id: 105   old 4: v_id NUMBER := &sv_id;   new 4: v_id NUMBER := 105;   First Name: Angel   Last Name: Moskowitz   Street: 320 John St.   City: Ft. Lee   State: NJ   Zip Code: 07024   PL/SQL procedure successfully completed.   Enter value for sv_table_name: instructor   old 3: v_table_name VARCHAR2(20) := '&sv_table_name';   new 3: v_table_name VARCHAR2(20) := 'instructor';   Enter value for sv_id: 105   old 4: v_id NUMBER := &sv_id;   new 4: v_id NUMBER := 105;   First Name: Anita   Last Name: Morris   Street: 34 Maiden Lane   City: New York   State: NY   Zip Code: 10015   PL/SQL procedure successfully completed.