File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes JDBC Stored Procs and Temp tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC Stored Procs and Temp tables" Watch "JDBC Stored Procs and Temp tables" New topic

JDBC Stored Procs and Temp tables

Nagarajan Palaniswamy

Joined: Apr 30, 2010
Posts: 1

I am using a java program to call a stored procedure using the PreparedStatement.

The Stored Procedure uses a select statement to select a particular column and insert the values into a temp table. I do a query of temp table at the end of the stored procedure. the output of stored procedure is a CURSOR.

The output is coming fine when I run the S tored Procedure on SQL Plus. But in Java I am not getting the output. I use the SYSREFCURSOR to get the cursor in Java.

In one of the articles on net I read that PreparedStatement when used like select count(*) from temp table will not work. Is this the same case for stored procedures also?

Please guide me. Thanks.

Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

Welcome to JavaRanch, Nags.

OCUP UML fundamental and ITIL foundation
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

Hi Nags,

Welcome to JavaRanch.

Using temp table is always tricky with Java.

Temporary table in oracle is always bound to user's connection. remember the connection i am referring to is the physical connection with your oracle server, if your environment is using connection pooling then you can not be certain if you will get the results because if it is same connection which populated the temporary table then you will get result else you will not.

could you please provide us the environment details .


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
I agree. Here's the link:
subject: JDBC Stored Procs and Temp tables
It's not a secret anymore!