Data Encryption in Transit - Expert Oracle and Java Security - page 116

functions in app_sec_pkg will remain and have not been changed. Look at the file, Chapter7/AppSec.sql to
see the full listing. We have one new procedure that we introduce in this chapter: p_log_error .
Creating an Error Logging Procedure
The p_log_error procedure takes a NUMBER and one or two VARCHAR2 (text) parameters. The err_txt field
is limited to 2,000 characters, but a VARCHAR2 column may have up to 4000 characters; so we truncate the
m_err_txt parameter to 2,000 characters, if needed, to fit our err_txt column.
Note that this procedure (the package), and the table being updated are in the appsec schema, but
the one calling this procedure may be an application in another schema (like HR ). We have already
granted execute on app_sec_pkg package to the HR user, and we need to grant execute to any other
application user who needs our Application Security processes.
If you will, think back to when we defined the t_appsec_errors table. Recall that we set the msg_txt
and update_ts columns to be nullable, and to have default values ( NULL and SYSDATE ). That allowed us to
do data inserts by just providing data elements for the first two columns. We could insert data without
even mentioning the last two columns. In fact, we said that we don't want to insert a value for update_ts ;
but rather, allow the Oracle database to assign the current default value of SYSDATE .
Well, now we are creating a procedure (shown in Listing 7-6) for various applications to call in order
to insert error records into our table, and the procedure accounts for those default values. First of all, the
procedure doesn't accept a value for update_ts ; rather, the default SYSDATE will be used. Second, the
value for msg_txt has a default value of NULL so that application users can call this procedure with or
without a value for msg_txt .
Listing 7-6. Procedure to Insert Log Entries, p_log_error
PROCEDURE p_log_error( m_err_no NUMBER, m_err_txt VARCHAR2,
m_msg_txt VARCHAR2 DEFAULT NULL )
IS
l_err_txt VARCHAR2(2000);
BEGIN
l_err_txt := RTRIM( SUBSTR( m_err_txt, 1, 2000 ) ) ;
INSERT INTO v_appsec_errors ( err_no, err_txt, msg_txt )
VALUES ( m_err_no, l_err_txt, m_msg_txt );
COMMIT;
END p_log_error;
We use the substring function, SUBSTR , to get only the first 2,000 characters of the error text. Then
we use the right trim function, RTRIM to remove any spaces at the right end of the remaining text. If
m_err_txt is NULL , SUBSTR returns a NULL , and RTRIM returns a NULL .
At the end of the p_log_error procedure, we simply insert the error data into our error log table and
COMMIT .
Executing Package Specification and Body
Execute the two blocks in the file named Chapter7/AppSec.sql to replace the app_sec_pkg package
specification and body. You can see that both those blocks begin with the command CREATE OR REPLACE .
Because we already have a package named app_sec_pkg , this command will replace it. The absolute best
thing about this command is that we can execute it on a running Oracle database, and the applications
that use the package will not fail. That is, if the package specification does not need to change. Consider
another option: if we had to DROP and then separately CREATE these structures, we would have to wait