Different restults from Oracle and JDBC

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!

Hi Tom,

Welcome to javaranch!

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

Shailesh

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

can you paste your jdbc code ?

Shailesh

String sql = "select count(*) from PAY_COSTS_V pcv";
try {

OracleOCIConnectionPool pool = new OracleOCIConnectionPool("username", "password", "jdbc

racle

ci:@(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

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 ]

Can you tell me how many rows this query returns ?

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'

Shailesh

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.

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

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.

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

Shailesh

Sorry Shailesh

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

it retuens APPS, my current username

Tom

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

Shailesh

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

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

I meant the owner is APPS

The output is:
APPS XXMOI_DB2_TSOE_V VIEW

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

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.

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

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