Public Key Encryption - Expert Oracle and Java Security - page 74

Note that the NAME keyword must point to a single line of text. Here we have word wrap, but the
definition of the Java method must all appear on a single line. Additionally, both the input and output
parameters must be fully qualified—they must have the entire path prepended to the Class name. That
includes classes that are naturally part of the Java language like java.lang.String .
Recall that Oracle functions can take parameters, do work and return a single value. In this case, we
are going to encrypt a string and return it as a RAW data type. RAW is an immutable byte array that can be
transferred between Oracle Database and Java without any interpretation or translation (casting). RAW
data retains fidelity between Oracle and Java, server and client. The limit of a RAW data element is either
32767 bytes in PL/SQL code (our case) or 2000 bytes when stored in the database.
Creating a Procedure to get SYSDATE in Encrypted Form
We will add a procedure that does not call Java, so it is not a Java stored procedure, but it calls our Java
stored function, f_get_rsa_crypt . Execute the command in Listing 5-12 as the appsec user.
Listing 5-12. Oracle Procedure to Encrypt Date and Time String, p_get_rsa_crypt_sysdate
CREATE OR REPLACE PROCEDURE p_get_rsa_crypt_sysdate(
ext_rsa_mod IN VARCHAR2,
ext_rsa_exp IN VARCHAR2,
crypt_sysdate OUT RAW,
m_err_no OUT NUMBER,
m_err_txt OUT VARCHAR2 )
IS BEGIN
m_err_no := 0;
crypt_sysdate := f_get_rsa_crypt( ext_rsa_mod, ext_rsa_exp,
TO_CHAR( CURRENT_TIMESTAMP, 'DY MON DD HH24:MI:SS TZD YYYY' ) );
EXCEPTION
WHEN OTHERS THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;
END p_get_rsa_crypt_sysdate;
/
This is fairly simple procedure. It gets the CURRENT_TIMESTAMP from the Oracle Database, converts it
to a string using the TO_CHAR SQL function, uses f_get_rsa_crypt (that we defined previously) to encrypt
it, and then returns it to the caller.
So why have I complicated it with what appears to be error handling? Well now is as good a time as
any to introduce a template for procedures of this type. We don't want to burden the Oracle database
with the task of dealing with most of the errors our procedures may generate, and we also want to be
able to deal with the errors back at our application. We need Oracle database to tell us what the error is,
and we need to be able to deal with it in a logical fashion.
For this reason, we have declared several of our parameters as OUT parameters. The first OUT
parameter is a RAW that contains our encrypted timestamp string. The second OUT parameter is the error
number that is generated by Oracle Database - every Oracle error type has a distinct error number that is
indexed in the Oracle documentation. And the third OUT parameter is the error message.
In our template, when an exception occurs, we don't throw it back to the calling application; rather,
we allow the procedure to complete in an orderly way and return the error number ( SQLCODE ) and
message ( SQLERRM ) to the caller in the OUT parameters. Back in the calling code, before we handle any
other of the returned data, we will check to see if the error number is anything besides 0, and if it is, we