Oracle JDBC Bug!

[Tom Helegera]

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]

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]

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]

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]

Here is my code:

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

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn  =  DriverManager.getConnection("jdbc:oracle:thin:@server:1521:sid","user","password");

Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1));
}

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]

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

[Tom Helegera]

Hi Jeanne,

Actually they are the same user!



Hisham

[Paul Campbell]

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:

  
SELECT * 
  FROM DBA_DATA_FILES

To resize your data file:

 
ALTER DATABASE DATAFILE �the file file path from your query\TEMP.DBF� RESIZE 500M; 
-- increase TEMP data file 500 meg

To add a data to TEMP:

ALTER TABLESPACE TEMP 
ADD DATAFILE �the file file path from your query\TEMP2.DBF� SIZE 200M; 
-- add 200 meg datafile to TEMP

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

SELECT * 
  FROM DBA_USERS; -- List your usernames and their default tablespaces
-- to move your user to the users tablespace.
ALTER USER username DEFAULT TABLESPACE users;
-- query to see users after moving
SELECT * 
  FROM DBA_USERS; -- List your usernames and their default tablespaces

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

[Tom Helegera]

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