This week's giveaway is in the Testing forum.
We're giving away four copies of TDD for a Shopping Website LiveProject and have Steven Solomon on-line!
See this thread for details.
Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Oracle JDBC Bug!

 
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
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
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author & internet detective
Posts: 41073
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tom,
You wouldn't happen to be using different users to test between the Java app and SQL plus, would you?
 
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 Jeanne,

Actually they are the same user!



Hisham
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 suggest huckleberry pie. But the only thing on the gluten free menu is this tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic