Database Reference
In-Depth Information
manager_id = m_manager_id
-- Job History Constraint -- , department_id = m_department_id
WHERE employee_id = m_employee_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;
appsec.app_sec_pkg.p_log_error( m_err_no, m_err_txt,
'HR p_update_employees_sensitive' );
END p_update_employees_sensitive;
END hr_sec_pkg;
/
Procedure Variables and Data Decryption
We cannot modify the
IN
parameters like we did with
OUT
parameters in previous example procedures,
but we want to capture the output of our decryption, so we establish a couple procedure variables:
v_salary
and
v_commission_pct
. We also define a numeric procedure variable named
test_emp_ct
:
test_emp_ct NUMBER(6);
v_salary
VARCHAR2(15); -- Plenty of space, eventually a NUMBER
v_commission_pct
VARCHAR2(15);
Our procedure body, under the
BEGIN
header, includes two calls to the
f_get_decrypt_data
Oracle
function which will return a
VARCHAR2
data type representing the
SALARY
and the
COMMISSION_PCT
. Note
again that use of this procedure assumes that you have already done key exchange:
m_err_no := 0;
v_salary := appsec.app_sec_pkg.
f_get_decrypt_data
( crypt_salary );
v_commission_pct :=
appsec.app_sec_pkg.
f_get_decrypt_data
( crypt_commission_pct );
Inserting or Updating
I have found that a multifunction procedure is often the best choice for managing data inserts and
updates. At most, we grant
SELECT
privilege to a view of the data and
EXECUTE
privilege to the
management procedure. We regularly pass a transaction code (usually A, U, or D) that indicates whether
we are going to be inserting (adding), updating, or deleting a record. For a simple, bi-functional
procedure (insert or update), we do not need a transaction code, but can examine the data and 1) update
an existing record or 2) insert a new record if no existing record matches the key columns.
In our management procedure body, we will populate
test_emp_ct
by using
SELECT INTO
syntax with
a count of the number of employees whose
EMPLOYEE_ID
matches the
m_employee_id
that is being passed
in for update. There should never be more than one, so we expect a value of 0 or 1 from the count.
SELECT COUNT(*) INTO test_emp_ct FROM employees WHERE
employee_id = m_employee_id;
We then test to see if
test_emp_ct
is 0 - if so, we do an
INSERT
; if not, an
UPDATE
:
IF test_emp_ct = 0
