GeeCON Prague 2014*
The moose likes JDBC and the fly likes Different restults from Oracle and JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Different restults from Oracle and JDBC" Watch "Different restults from Oracle and JDBC" New topic
Author

Different restults from Oracle and JDBC

Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
I am using Oracle 10 , runing a query , select * from view_name , from SQL Plus and query is returning data.
From Java (OCI and also Thin) The query is returning no data, and NO ERRORS also.
The alias is not the issue as I am able to get specific data from another table for the same DB.

PLEASE HELP what the problem might be!
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Hi Tom,

Welcome to javaranch!

Are you sure you are connected to right database when using JDBC.

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Hi Shailesh,

Actually yes, I am connected to right DB, I am even able to get Production data from different tables and views throws JDBC (which are identical to what I get from PL/SQL), however, when coming to this specific (and kind of slow and complex) view, there are no records returned from JDBC and the query execution time is 50 seconds with no Exceptions thrown. But if I query the view from PL/SQL the query returns 181 rows with execution time = 900 seconds.


Tom
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

can you paste your jdbc code ?

Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
String sql = "select count(*) from PAY_COSTS_V pcv";
try {

OracleOCIConnectionPool pool = new OracleOCIConnectionPool("username", "password", "jdbcracleci:@(description=(address=(host=172.16.1.191)(protocol=tcp)(port=1521))(CONNECT_DATA = (SERVICE_NAME = prod1)(FAILOVER_MODE = (TYPE=SELECT)(METHOD=BASIC))))", null);

Connection conn = pool.getConnection();
Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
if (rs.next()) {
System.out.println(rs.getInt(1));
}

} catch (Exception e) {
e.printStackTrace();
}


output: 0
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

only thing I can think of is the user from which you are connecting with oracle. I am assuming that you are connecting with correct user.

Also I would suggest that use standard way of executing queries, I mean do not use database vendor specific class. This tight couples your code with a database.




Shailesh
[ November 09, 2008: Message edited by: Shailesh Chandra ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Can you tell me how many rows this query returns ?

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'

Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
I also used the Thin driver to try the code! with the same result, now I returned to Thin as your last post.

I am sure about the username/password thing, they are the same of what I am using in the PL/SQL.

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'

this would return no records.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Tom Helegera:

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'



Now this surprise me as it should return at least one row, as you told us you have a view, so it shoulkd result in one row having object_name PAY_COSTS_V and object_type as view.

Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Actually this surprises everybody here!

The view is there, and it returns data from PL/SQL and from SQL+

Oracle people says as long as it returns data from PL/SQL it is working fine.

Overview, that doesn't return data from ALL_OBJECTS , returns data in both PL/SQL and Java.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

OK, can you create a new view (just for testing) with same definitions and try your code.

Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Sorry Shailesh

Actualy the last query return one row, one letter was lower case.

it retuens APPS, my current username


Tom
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

OK now execute same query from jdbc to check if it returns same result from jdbc

Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'
From JDBC: return one record APPS
From PL\SQL : return one record APPS (same as JDBC)

SELECT count(*) FROM PAY_COSTS_V
From JDBC: returns 0 after 44 second
From PL\SQL: return 181 rows after 800 second

Please note that from JDBC no errors are returned.


Tom
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Just one more query, what do you mean by "return one record APPS" , I think it will return one row with multiple columns. and what is object_type.

Can you print output of the query

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'


Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
I meant the owner is APPS

The output is:
APPS XXMOI_DB2_TSOE_V VIEW
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Tom Helegera:


The output is:
APPS XXMOI_DB2_TSOE_V VIEW



How can your query give object_name as XXMOI_DB2_TSOE_V
as we have where clause <<WHERE OBJECT_NAME='PAY_COSTS_V'>>

Shailesh
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
XXMOI_DB2_TSOE_V is the name of the same view that you said to create a new one as for test, it is the same view.
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Again :

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='XXMOI_DB2_TSOE_V'
From JDBC: return one record APPS
From PL\SQL : return one record APPS (same as JDBC)

SELECT count(*) FROM XXMOI_DB2_TSOE_V
From JDBC: returns 0 after 44 second
From PL\SQL: return 181 rows after 800 second
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Tom Helegera:

From PL\SQL: return 181 rows after 800 second



I can not think of anything, but 800 seconds for 180 row is too much, as of now I can suggest to optimize your query written in view to reduce the timings.

Shailesh
 
GeeCON Prague 2014
 
subject: Different restults from Oracle and JDBC