aspose file tools*
The moose likes JDBC 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
Bookmark "JDBC Stored Procs and Temp tables" Watch "JDBC Stored Procs and Temp tables" New topic
Author

JDBC Stored Procs and Temp tables

Nagarajan Palaniswamy
Greenhorn

Joined: Apr 30, 2010
Posts: 1
Hi,

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?

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/67b10d74-e94f-4b58-8ee3-59ff390db1cf

Please guide me. Thanks.

Nags
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Welcome to JavaRanch, Nags.


OCUP UML fundamental and ITIL foundation
youtube channel
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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 .

Shailesh

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Stored Procs and Temp tables