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
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 .
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.
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. |