• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Different restults from Oracle and JDBC

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tom,

Welcome to javaranch!

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

Shailesh
 
Tom Helegera
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can you paste your jdbc code ?

Shailesh
 
Tom Helegera
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
String sql = "select count(*) from PAY_COSTS_V pcv";
try {

OracleOCIConnectionPool pool = new OracleOCIConnectionPool("username", "password", "jdbc:oracle:oci:@(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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you tell me how many rows this query returns ?

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='PAY_COSTS_V'

Shailesh
 
Tom Helegera
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, can you create a new view (just for testing) with same definitions and try your code.

Shailesh
 
Tom Helegera
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK now execute same query from jdbc to check if it returns same result from jdbc

Shailesh
 
Tom Helegera
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I meant the owner is APPS

The output is:
APPS XXMOI_DB2_TSOE_V VIEW
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic