Database Reference
In-Depth Information
Listing 7-21.
Get a Count of Rows in the Public View of Employees
rset = stmt.executeQuery(
"SELECT * FROM hr.v_employees_public" );
int cnt = 0;
while( rset.next() ) cnt++
;
System.out.println( "Count data in V_EMPLOYEES_PUBLIC: " + cnt );
rset = stmt.executeQuery(
"SELECT COUNT(*) FROM hr.v_employees_public" );
if( rset.next() )
cnt = rset.getInt(1)
;
System.out.println( "Count data in V_EMPLOYEES_PUBLIC: " + cnt );
if( null != stmt ) stmt.close();
Selecting Encrypted Data from EMPLOYEES
Here is our textbook process for selecting encrypted data from Oracle database, Listing 7-22. It
resembles the test procedures we used in the last chapter. We pass the public key modulus and exponent
to Oracle database and receive back the encrypted artifacts of our DES secret password key. In addition,
we have an
OUT
parameter that is of type
OracleTypes.CURSOR
. We will draw on the
OracleTypes.CURSOR
(
ResultSet
in Java) to read our data.
Listing 7-22.
Java Code to Select Sensitive Data from Employees, from
p_select_employees_sensitive
stmt = ( OracleCallableStatement )conn.prepareCall(
"CALL hr.hr_sec_pkg.
p_select_employees_sensitive
(?,?,?,?,?,?,?,?,?)" );
stmt.registerOutParameter( 3, OracleTypes.RAW );
stmt.registerOutParameter( 4, OracleTypes.RAW );
stmt.registerOutParameter( 5, OracleTypes.RAW );
stmt.registerOutParameter( 6, OracleTypes.RAW );
stmt.registerOutParameter( 7,
OracleTypes.CURSOR
);
stmt.registerOutParameter( 8, OracleTypes.NUMBER );
stmt.registerOutParameter( 9, OracleTypes.VARCHAR );
stmt.setString( 1, locModulus );
stmt.setString( 2, locExponent );
stmt.setNull( 3, OracleTypes.RAW );
stmt.setNull( 4, OracleTypes.RAW );
stmt.setNull( 5, OracleTypes.RAW );
stmt.setNull( 6, OracleTypes.RAW );
// This must go without saying - unsupported type for setNull
//stmt.setNull( 7, OracleTypes.CURSOR );
stmt.setInt( 8, 0 );
stmt.setNull( 9, OracleTypes.VARCHAR );
stmt.executeUpdate();
errNo = stmt.getInt( 8 );
if( errNo != 0 ) {
errMsg = stmt.getString( 9 );
System.out.println( "Oracle error 1) " + errNo +
