Java – Return ROWID Parameter from insert statement using JDBC connection to oracle

javajdbcoracle

I can't seem to get the right magic combination to make this work:


OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc:oracle:thin:app_user/pass@server:1521:sid");
DefaultContext conn = ods.getConnection();
CallableStatement st = conn.prepareCall("INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO :rowid0");
st.registerReturnParameter(1, OracleTypes.ROWID);
st.execute();

The error I get is "Protocol Violation". If I change to registerOutParameter(), I get notified that I haven't registered all return variables. If I wrap the statement in a PL/SQL begin; end; block then I get the parameter just fine using a regular registerOutParameter() call. I would really prefer to avoid wrapping all my inserts statements in PL/SQL – so what is missing from above?

Best Answer

Usually you don't want to make code database dependent. Instead of OraclePreparedStatement, you should use CallableStatement.

CallableStatement statement = connection.prepareCall("{call INSERT INTO tableA (some_id) VALUES (1) RETURNING ROWID INTO ? }");
statement.registerOutParameter( 1, Types.VARCHAR );

int updateCount = statement.executeUpdate();
if (updateCount > 0) {
   return statement.getString(1);
}

Related Solutions

Sql – Are anonymous blocks stored parsed in the SGA

Anonymous blocks are cached as well. You can check it by querying V$SQLAREA.

SQL> declare abcabc number; begin null; end;
2 /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> select sql_text, executions from v$sqlarea where sql_text like '%abcabc%';

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
declare abcabc number; begin null; end;
2

select sql_text, executions from v$sqlarea where sql_text like '%abcabc%'
1

EDIT:

You'll always have what is called SOFT PARSE. It's needed for syntax and semantic check of a query. After that, if exactly the same query exists in library cache, HARD PARSE will be skipped. (See this Ask Tom question for a good explanation).

Here is excerpt from tkprofed 10046 trace file:

declare abcabc number; begin null; end;

call     count       cpu    elapsed       disk      query    current        rows  
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           2
Fetch        0      0.00       0.00          0          0          0           0  
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           2

Misses in library cache during parse: 1

The last line shows the point.

Java – Oracle JDBC intermittent Connection Issue

There is a solution provided to this problem in some of the OTN forums (https://kr.forums.oracle.com/forums/thread.jspa?messageID=3699989). But, the root cause of the problem is not explained. Following is my attempt to explain the root cause of the problem.

The Oracle JDBC drivers communicate with the Oracle server in a secure way. The drivers use the java.security.SecureRandom class to gather entropy for securing the communication. This class relies on the native platform support for gathering the entropy.

Entropy is the randomness collected/generated by an operating system or application for use in cryptography or other uses that require random data. This randomness is often collected from hardware sources, either from the hardware noises, audio data, mouse movements or specially provided randomness generators. The kernel gathers the entropy and stores it is an entropy pool and makes the random character data available to the operating system processes or applications through the special files /dev/random and /dev/urandom.

Reading from /dev/random drains the entropy pool with requested amount of bits/bytes, providing a high degree of randomness often desired in cryptographic operations. In case, if the entropy pool is completely drained and sufficient entropy is not available, the read operation on /dev/random blocks until additional entropy is gathered. Due to this, applications reading from /dev/random may block for some random period of time.

In contrast to the above, reading from the /dev/urandom does not block. Reading from /dev/urandom, too, drains the entropy pool but when short of sufficient entropy, it does not block but reuses the bits from the partially read random data. This is said to be susceptible to cryptanalytical attacks. This is a theorotical possibility and hence it is discouraged to read from /dev/urandom to gather randomness in cryptographic operations.

The java.security.SecureRandom class, by default, reads from the /dev/random file and hence sometimes blocks for random period of time. Now, if the read operation does not return for a required amount of time, the Oracle server times out the client (the jdbc drivers, in this case) and drops the communication by closing the socket from its end. The client when tries to resume the communication after returning from the blocking call encounters the IO exception. This problem may occur randomly on any platform, especially, where the entropy is gathered from hardware noises.

As suggested in the OTN forum, the solution to this problem is to override the default behaviour of java.security.SecureRandom class to use the non-blocking read from /dev/urandom instead of the blocking read from /dev/random. This can be done by adding the following system property -Djava.security.egd=file:///dev/urandom to the JVM. Though this is a good solution for the applications like the JDBC drivers, it is discouraged for applications that perform core cryptographic operations like crytographic key generation.

Other solutions could be to use different random seeder implementations available for the platform that do not rely on hardware noises for gathering entropy. With this, you may still require to override the default behaviour of java.security.SecureRandom.

Increasing the socket timeout on the Oracle server side can also be a solution but the side effects should be assessed from the server point of view before attempting this.

Best Answer

Related Solutions

Sql – Are anonymous blocks stored parsed in the SGA

Java – Oracle JDBC intermittent Connection Issue

Related Topic