4-15. Implementing a Failure Flag
Problem
You want to create a boolean variable to determine whether one of the subprograms in the package has generated an error. If an error has been generated by one of the subprograms, then the variable will be set to TRUE. This flag will be evaluated in the driver procedure to determine whether the updates performed by the package should be committed or rolled back.
Solution
Declare a global variable at the package level, and it will be accessible to all objects within. You can do this by declaring the variable within the package body. The following package illustrates such a variable, where the variable has been declared within the package body so that it is available for all objects in the package only.
CREATE OR REPLACE PACKAGE synchronize_data
PROCEDURE driver;
END;
CREATE OR REPLACE PACKAGE BODY synchronize_data IS
error_flag BOOLEAN := FALSE;
PROCEDURE query_remote_data is
Cursor remote_db_query is
SELECT *
FROM my_remote_data@remote_db;
remote_db_rec employees%ROWTYPE;
BEGIN
OPEN remote_db_query;
LOOP
FETCH remote_db_query INTO remote_db_rec;
EXIT WHEN remote_db_query%NOTFOUND;
IF remote_db_query%NOTFOUND THEN
error_flag := TRUE;
ELSE
-- PERFORM PROCESSING
DBMS_OUTPUT.PUT_LINE('QUERY REMOTE DATA'),
END IF;
END LOOP;
CLOSE remote_db_query;
END query_remote_data;
PROCEDURE obtain_new_record_list IS
BEGIN
--statements go here
DBMS_OUTPUT.PUT_LINE('NEW RECORD LIST'),
END obtain_new_record_list;
PROCEDURE obtain_updated_record_list IS
BEGIN
--statements go here
DBMS_OUTPUT.PUT_LINE('UPDATED RECORD LIST'),
END obtain_updated_record_list;
PROCEDURE sync_local_data IS
BEGIN
--statements go here
DBMS_OUTPUT.PUT_LINE('SYNC LOCAL DATA'),
END sync_local_data;
PROCEDURE driver IS
BEGIN
query_remote_data;
IF error_flag = TRUE THEN
GOTO error_check;
END IF;
obtain_new_record_list;
IF error_flag = TRUE THEN
GOTO error_check;
END IF;
obtain_updated_record_list;
IF error_flag = TRUE THEN
GOTO error_check;
END IF;
sync_local_data;
-- If any errors were found then roll back all updates
<<error_check>>
DBMS_OUTPUT.PUT_LINE('Checking transaction status'),
IF error_flag = TRUE THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('The transaction has been rolled back.'),
ELSE
COMMIT;
DBMS_OUTPUT.PUT_LINE('The transaction has been processed.'),
END IF;
END driver;
END;
How It Works
Declaring variables in the package body outside any procedures or functions allows them to become accessible to all subprograms within the package. If one or more of the subprograms changes such a variable's value, then the changed value will be seen throughout the entire package.
As depicted in the example, you can see that the variable is referenced several times throughout the package. If you had a requirement to make a variable global to all PL/SQL objects outside the package as well, then you can declare the variable within the package specification. As mentioned in Recipe 4-8, anything declared in the package specification is publically available to any PL/SQL object outside as well as within the package body.