Database Reference
In-Depth Information
"{ ? = call app_sec_pkg. f_show_algorithm }" );
stmt. registerOutParameter( 1 , OracleTypes.VARCHAR );
stmt.executeUpdate();
System.out.println( stmt.getString(1) ) ;
if( null != stmt ) stmt.close();
Of note here is the syntax for calling an Oracle function, rather than a procedure. The function
always returns a value. The leading “? =” represents the return value, and our statement parameter (1) is
that value. Each question mark in a prepared callable statement is a parameter, whether it comes before
or after the procedure or function name. The parameters are always numbered from left to right with 1
(one) being the first.
The format used in this function call, with the open and close curly brackets, is referred to as SQL92
syntax. That is a reference to the international standard for SQL that was adopted in 1992. Another form
of syntax that can be used for calling stored procedures and functions is the PL/SQL Block syntax (with
begin and end statements). I have found that with much older versions (e.g., jdbc14.jar) of the Oracle
drivers (although not as old as SQL92) the PL/SQL Block syntax will work when the SQL92 syntax will
not. Here is the same prepareCall() method call from Listing 6-22 in PL/SQL Block syntax:
stmt = ( OracleCallableStatement )conn.prepareCall(
"begin ? = call app_sec_pkg.f_show_algorithm; end;" );
Calling Oracle Database to get Encrypted Data
Next we want to demonstrate getting encrypted data back from Oracle database and decrypting it on the
client with a local copy of the DES secret password key. In Listing 6-23, you can see we call the
procedure, p_get_des_crypt_test_data . Again, we pass our public key artifacts and retrieve the
encrypted secret password key artifacts. Because this process is something we will see repeated
throughout the remainder of this topic, I will just call this “key exchange”. We just returned from another
procedure that retrieved the secret password key artifacts, so we don't bother to set our method
members again—those lines are commented. Note that all these calls are occurring in the same Oracle
session, so existing keys are used—there is no additional key generation.
Listing 6-23. Get DES Crypt Test Data, from main()
stmt = ( OracleCallableStatement )conn.prepareCall(
"CALL app_sec_pkg. p_get_des_crypt_test_data (?,?,?,?,?,?,?,?,?,?)" );
stmt.registerOutParameter( 3, OracleTypes.RAW );
stmt.registerOutParameter( 4, OracleTypes.RAW );
stmt.registerOutParameter( 5, OracleTypes.RAW );
stmt.registerOutParameter( 6, OracleTypes.RAW );
stmt.registerOutParameter( 7, OracleTypes.NUMBER );
stmt.registerOutParameter( 8, OracleTypes.VARCHAR );
stmt.registerOutParameter( 10, OracleTypes.RAW );
stmt.setString( 1, clientPubModulus );
stmt.setString( 2, clientPubExponent );
stmt.setString( 9, "Tuesday" );
stmt.setNull( 3, OracleTypes.RAW );
stmt.setNull( 4, OracleTypes.RAW );
stmt.setNull( 5, OracleTypes.RAW );
stmt.setNull( 6, OracleTypes.RAW );
stmt.setInt( 7, 0 );
stmt.setNull( 8, OracleTypes.VARCHAR );
 
Search WWH ::




Custom Search