aspose file tools*
The moose likes JDBC and the fly likes Oracle JDBC Bug! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle JDBC Bug!" Watch "Oracle JDBC Bug!" New topic
Author

Oracle JDBC Bug!

Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Yesterday I made a post about JDBC returning different results from SQL+

Today, I found that in addition to the difference in results between JDBC and SQL+, there is a case that SQL+ is returnning an error :
"ORA-01652 Unable to extend temp segment by 128 in tablespace TEMP"

where as JDBC is simply retrning zero row count.

SQL Query: select count(*) from PAY_COSTS_V

Does this make any sence?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I'm guessing PAY_COSTS_V is a view? Views need to be stored in the temp tablespace, and it look like you may be running out of room in there (or possibly you have no rights to extend the allocated room). It could be that from the time you ran the query in SQLPlus and via JDBC something else freed up space in the temp table space?

What happens if you run the test the other way round? Do you get the error in JDBC and a zero coun in SQLPlus?

Either way, the fix is you need to review what space is available. Its also worth remembering that SQLPlus does more than your average SQL client application, so you can't always expect exactly the same behaviour as you would via JDBC. Try using the JDBC backed client Oracle provide instead.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Yes. PAY_COSTS_V is a view. But I would expect Java application would give me an error or something not to show that the view has no records!!!

Even if I repeat the test over and over, still, via SQLPlus it gives Error and Via Java it gives zero records!

Try using the JDBC backed client Oracle provide instead.


I am using ojdbc14.jar downloaded from Oracle website for the same Oracle version. Do you recommend something else?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Yes. PAY_COSTS_V is a view. But I would expect Java application would give me an error or something not to show that the view has no records!!!

I'd expect the same. JDBC is supposed to return the error message the underlying database engine generates wrapped in a SQLException. My only guess (and its very definately a guess) is that SQLPlus does something more than JDBC does. Can you show us your code?


I am using ojdbc14.jar downloaded from Oracle website for the same Oracle version. Do you recommend something else?

Thats the JDBC driver. There are newer versions you can use (always a good idea), but I was talking about the JDBC client oracle provides; SQL Developer I think is called.
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Here is my code:



Anyway, I used Oracle SQL Deveoper, which uses ojdbc5.jar it gives the result as my Java code. However, executing the same query using SQL Plus, gives Error!

[Dave - removed DB credentials]
[ November 12, 2008: Message edited by: David O'Meara ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

Tom,
You wouldn't happen to be using different users to test between the Java app and SQL plus, would you?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Tom Helegera
Greenhorn

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

Actually they are the same user!



Hisham
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
You should get an IOException... but I can't answer the JDBC as well as the others... I do know how to resolve the problem on the database side.

Ora-01652 : Unable to extend temp segment by 128 in tablespace TEMP

This error occurs when their is a failure to allocate an extent of the required number of blocks for a temporary segment within the tablespace indicated. In this case, TEMP.

Your user shouldn't really be assigned to TEMP for the default tablespace.

You can eliminate the problem by doing one of the following: You can add a datafile to the TEMP tablespace, you can resize your TEMP datafile, or you can assign your user to a larger tablespace.

To view the size of your datafiles sizes:



To resize your data file:


To add a data to TEMP:


Or move your user out of temp and into a more appropriate table space.


[ November 12, 2008: Message edited by: Paul Campbell ]
Tom Helegera
Greenhorn

Joined: Nov 08, 2008
Posts: 18
Thanks Everybody,

The problem why the JDBC wan't returning results as the SQL Plus was becuase in the mentioned view the DB guy was using

userenv('LANG')

in his where statment.

so when I was executing the query from SQL Plus it gives :
select userenv('LANG') from dual ----------> result is AR

and from SQL Developer :
select userenv('LANG') from dual ----------> result is EN

IN THE SAME MACHINE.

Our DBA fixed the TEMP problem and replaced the userenv('LANG') to be fixed as 'AR'


Hisham
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle JDBC Bug!
 
Similar Threads
HELP -- Counting rows with oracle & jdbc
my resultset return what????
JDBC
Select query row count (without explicit select query)
JDBC doesn't retrieve all the results available in the MSSQL database