Section 11 Lesson 1: Persistent State of Package Variables
1. Since Oracle Application Express automatically commits changes, complete the following
activity as if you were issuing the commands in an installed/local environment with the ability
to use COMMIT and ROLLBACK.
In this question you will use a slightly modified version of the pers_pkg package which you
studied in the lesson. You will need to have two Application Express sessions running
throughout this question, so start by having two browser sessions running, each logged into
Application Express with your normal details. TURN OFF AUTOCOMMIT IN BOTH SESSIONS. The
practice will not work correctly if autocommit is turned on. Also, do NOT leave the SQL
Command Processor during this question.
A. In one of your sessions, create the package specification and body using the following code:
CREATE OR REPLACE PACKAGE pers_pkg IS
g_var NUMBER := 10;
PROCEDURE upd_g_var (p_var IN NUMBER);
FUNCTION show_g_var RETURN number;
END pers_pkg;
CREATE OR REPLACE PACKAGE BODY pers_pkg IS
PROCEDURE upd_g_var (p_var IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Initially g_var is set to: ' || g_var);
g_var := p_var;
DBMS_OUTPUT.PUT_LINE('And now g_var is set to: '|| g_var);
END upd_g_var;
FUNCTION show_g_var RETURN NUMBER IS
BEGIN
RETURN(g_var);
END show_g_var;
END pers_pkg;
Then describe your package in the other session to make sure that you can see it.
DESCRIBE PACKAGE pers_pkg
B. In both sessions, execute a SELECT statement that calls the show_g_var function to see the
starting value of g_var. Verify that value 10 is returned in both sessions.
SELECT pers_pkg.show_g_var()
FROM dual
C. Now in the first session call the upd_g_var procedure with a value of 100 and in the second
session call the upd_g_var procedure with a value of 1. Verify the results are as you expect: 100
in the first session and 1 in the second.
Yes, the first one has 100 as g_var and the second has 1 as g_var.
D. Now in your first session call the upd_g_var procedure again with a value of 50. Then, in both
sessions, call the show_g_var function to display the value of g_var. Explain your results.
The first session has a value of 50 and the second has a value of 1.
2. Package cursor_state:
A. Write a package called cursor_state that declares a global cursor as a join of employees and
departments. The cursor should select every employees first and last name, department name,
and the employees salary. The package should also contain three public procedures: the first
one opens the cursor; the second one has an IN parameter of type NUMBER and fetches and
displays a number of rows as well as the current value of the loop counter. The third procedure
closes the cursor.
Remember to test the state of the cursor before you try to open or close it.
CREATE OR REPLACE PACKAGE BODY cursor_state IS
PROCEDURE open_curs IS
BEGIN
IF NOT emp_curs%ISOPEN THEN OPEN emp_curs; END IF;
END open_curs;
FUNCTION fetch_n_rows(n NUMBER := 1) RETURN BOOLEAN IS
emp_first employees.first_name%TYPE, emp_last employees.last_name%TYPE,
emp_dept employees.department_name%TYPE, emp_salary employees.salary%TYPE,;
BEGIN
FOR count IN 1 .. n LOOP
FETCH emp_curs INTO emp_first, emp_last, emp_dept, emp_salary;
EXIT WHEN emp_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE((emp_first), (emp_last), (emp_dept), (emp_salary));
END LOOP;
RETURN emp_curs%FOUND;
END fetch_n_rows;
PROCEDURE close_curs IS BEGIN
IF emp_curs%ISOPEN THEN CLOSE emp_curs; END IF;
END close_curs;
END cursor_state;
B. Test your code by executing an anonymous block that makes four calls to the package. The
first call opens the cursor, the second fetches 3 rows, the third fetches 7 rows, and the fourth
closes the cursor.
C. In the output, where do the numbers 1,2,3,1,2,3,4 come from?
The 1,2,3 comes from the first 3 rows and then the third call fetched 7 more rows so the
number reset at 1, hence why it is repeating.
