OracleStatement.defineColumnType(). example

[asgardar]

have a query which involves multilpe tables and joins and function calls and inline views in the from clause. If I run the query using TOAD the query is fast takes about 50 secs to 1 min. If I run the same query in java program by JDBC using a parametric query the query takes longet time (3 to 4 times longer). I'm doing insert using select statement.I read somewhere that SELECT statement makes two round trips to the database, the first for metadata, the second for data. Use OracleStatement.defineColumnType(). does someone have an example how it can be done.My sample query

insert into TESTAD
select td.id as ID,
td.std_bill_rate as Standard_Bill_Rate,
td.overtime_bill_rate as OverTimeBillRate,
td.doubletime_bill_rate as DoubleBillRate,
td.effective_date as effective_billRate

from THIRDDEV td
where td.id = ? and td.effective_date = ?

I need some java code example using defineColumnType(). My sql is very complicated I dont want to post it in the forum. if I have some example I can then modify my query.

[Tolls]

If this is a PreparedStatement (which seems to be the case since it's got '?' in it) then there will be the single round trip to the db.

That SQL will be executed by Oracle.

How are you setting the parameters? Are you setting them using the correct setters?

How are you timing the Java side of this?

Have you timed it several times (to take into account cacheing on Oracle)?