| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help!: Oracle problem with Cursors and Stored Procedures
A copy of this was sent to jeffb_at_halcyon.com (Jeffrey Mark Braun)
(if that email address didn't require changing)
On 26 Apr 1999 15:47:01 -0700, you wrote:
[snip]
> The text below is from my colleage he, he's been doing most of the JDBC
> development.
> -----------------------
>
> It appears that the way result sets are returned in Oracle stored
> procedures
> is by declaring a cursor:
>
> CREATE OR REPLACE PROCEDURE ep_get_foo(
> oReturnCursor OUT REFCURSOR,
> iID IN INTEGER)
> AS
> BEGIN
> OPEN
> oReturnCursor
> FOR
> SELECT Name
> FROM NameTab
> WHERE ID = iID;
> END;
> /
>
> This works by calling it via a JDBC CallableStatement object:
> CallableStatement cs =
> conn.prepareCall("{ call ep_get_foo(?, ?) }");
> cs.registerOutParameter(1, OracleTypes.CURSOR);
> cs.execute();
> ResultSet rs = (ResultSet)cs.getObject(1);
>
> Not only does this seem a bit wacky to me (why can't I get the result set
> from the execute() call just like I can if I'm calling SQL
> directly?)
because stored procedures can return 0, 1, 2, ... N result sets from a single call. They are named formal parameters. In Oracle stored procedures, the result set isn't stuffed onto a stack somewhere but rather scrolled through via a cursor. It allows you to write a sp that returns 2 result sets for example and then fetch alternatively from each one (without having to empty one all of the way before fetching the second). Its just the way we are architected.
> but it
> also leaves an open cursor hanging about. I've tried closing the
> CallableStatement object after using the result set but I
> eventually use up
> all the available cursors so that isn't working. HELP! There must be a way
> to either:
>
> 1) Create the cursor explicitly and pass it to the SP
No, the cursor is created by the SP, not the other way around.
> 2) Find another way to close the cursor
cstmt.close() does it.
> 2) Avoid this cruft and do it the Java way.
>
whats the java way?
> Any explanations/help?
>
>
>
What you might be seeing is the effect of the init.ora parameter:
close_cached_open_cursors boolean FALSE
Pl/sql by default will keep cursors open in the hopes you reuse these cursors. It massively speeds up the parse time. I recommend you look at your init.ora parameter:
open_cursors integer 1000
and make sure its not 50 (the very tiny default). Make it a couple of hundred and performance will improve overall.
I ran the following test program:
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
class curvar
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";
String query = "begin curvar_demo.get_enames( :1 ); end;";
Connection conn;
int rsetCount;
Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "scott", "tiger");
Statement stmt = conn.createStatement ();
for( int loopCount = 1; ; loopCount++ )
{
CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
for (rsetCount = 0; rset.next (); rsetCount++ );
cstmt.close();
System.out.println( "Looping " + loopCount +
" Fetched " + rsetCount + " rows..." );
}
}
}
It ran for over 10,000 iterations before I killed it. I had a total of 3 open cursors in my database at any point in time -- one for the SP call itself, one for the QUERY "select ..." that was returned. One for the "select * from v$open_cursor" i did in another session to see whats open.
I then commented out the "cstmt.close();" call. It ran for 998 iterations before hitting max cursors. This demonstrates that cstmt.close() is in fact how to close a SQL cursor.
Give it a try on your system. I used 8.0.4 on Solaris 2.5.1 for a testbed. Watch the dynamic performance table v$open_cursors on your system from another session whilst your program is running to see what cursors happen to be 'leaking'.
The plsql code was:
create or replace package curvar_demo
as
type refcur is ref cursor;
procedure get_enames( p_cursor in out refcur );
end;
/
create or replace package body curvar_demo
as
procedure get_enames( p_cursor in out refcur )
is
begin
open p_cursor for select ename from emp; end;
end;
/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |