Hi Tom,

I was reading DB2 Magazine today and found they have in DB2 V7 SCROLLABLE CURSOR. I serach your site for "SCROLLABLE CURSOR" and found this hit.

I like SCROLLABLE CURSOR approch.Do we have SCROLLABLE CURSOR in oracle. If yes, then how can we use it.
If not, then how we can get that functinality.


Thanks,

what about big tables (like >20000 records)? if I want to go to the last record, the JDBC driver has to read all rows?

Hi tom,

canu you explain me

Connection con = DriverManager.getConnection
            ("jdbc:oracle:thin:@think:1521:THINK1",
             "tkyte", "tkyte");

what is think and THINK1

I am trying to connect but it is giving me error
my info
username = system
password = manager
connectstring = swami

error is 


SQL>  exec scroll;
BEGIN scroll; END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish
the connection
ORA-06512: at "SYSTEM.SCROLL", line 0
ORA-06512: at line 1

Thanks
 

Tom,

Thanks for your reply.

Your java code gave me the effect of scrollble cursor what DB2 is claming.
I rad DB2 documnetation and it very clumsy process if you want to have scrollable cursor.You need to create global temporary table etc.. etc.. and lots of limitation in it.

Your java stored procedure VERY VERY SIMPLE.

Another question.

DB2 magazine was taking about DYNAMIC SCROLLING of cursor.

Can you explain me about that.

Thanks for very informative very site(THE BEST VEB SITE).

Gaurang

Hi Tom,

 I have question regarding memory usage if we use java stored procedure.

 I am trying to create scrollable cursor.

 I have table with 1.5 Million rows.

 i am trying to select first,last and last-1 records through java stored procedure(Excatly what your code is doing but my table has 1.5 Milloin rows).

My query only select one column from table.

I am getting following error.

 SQL> exec scroll_csr
BEGIN scroll_csr; END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError
ORA-06512: at "EDITDEV.SCROLL_CSR", line 0
ORA-06512: at line 1

I think it is memoery realted error(run out of memory).



Mermory statistics for session is

NAME                                VALUE
------------------------------ ----------
session uga memory                  58292
session uga memory max              91212
session pga memory              105941052
session pga memory max          105941052

Why so much memory is being used?


Can you explain?.

2)would JAVA_POOL be used for this process?

Can you explain about that.

Thanks.

Gaurang


  

Hi Tom

Do we get any performance benefit
with updatable cursors against stored procs?

With JDBC 2.0 API, you can build the new row and then insert it into both the result set and the table in one step.
Is this better than calling a stored procedure to insert a row, and then return a result set (ref cursor).

Thanks
Bala.

Tom,
I'm trying to understand the architecture of the 9i scrollable cursor. Is it native, or does it have to materialize the result set. If the latter, where, the PGA? I'm thinking this would be a negative performance characteristic. (I know, I will test.)

Thanks
Dave

Hi Tom,

Ref cursor is never scrollable with ORACLE JDBC driver, yes?

John

Hi tom,

thanks for your example;;

/**********************************/
import java.io.*;
import java.sql.*;

class Scrollable extends Object {


public static void main(String args[]) throws Exception
{
String sqlstr = "select empno from emp";

DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection con = DriverManager.getConnection
("jdbc:oracle:thin:@think:1521:THINK1",
"tkyte", "tkyte");

//Create an scrollable insensitive updatable cursor
Statement stmt =
con.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

ResultSet rset = stmt.executeQuery(sqlstr);

boolean rtn = rset.first();
System.out.println("Boolean value of rset.first: " +
rtn + " row = " + rset.getRow() );
System.out.println(rset.getInt("empno") );

rtn = rset.last();
System.out.println("Boolean value of rset.last: "
+ rtn + " row = " + rset.getRow() );
System.out.println(rset.getInt("empno") );

rtn = rset.relative(-1);
System.out.println(
"Boolean value of rset.relative(-1): " +
rtn + " row = " + rset.getRow() );
System.out.println(rset.getInt("empno") );

rset.close();
stmt.close();
}

}

/***********************************/

I have a function that takes the connection as an input
parameter. I don't know the user name and database name.
I just get an active connection (just conneciton object oracle JDBC driver classes12.zip)

how to find out
1.) what is the useName for this conneciton obj ?
2.) what is the database name for this conneciton obj ?
3.) what is the instance name for this conneciton obj ?

as we give this things to get the connection using oracle jdbc drivers (classes12.zip). I would expect that these are the properties of conneciton. but, I did not see how to retrive that from conneciton

can you please show me ?

TIA

If there aren't any documented properties, then there aren't any properties I guess (more of a java/jdbc question here -- check the sun site, perhaps it is part of the standard)

short of that, 


 
ops$tkyte@ORA9IR2> select user, a.name, b.instance_name
  2  from v$database a, v$instance b;
 
USER                           NAME
------------------------------ ------------------------------
INSTANCE_NAME
----------------
OPS$TKYTE                      ORA9IR2
ora9ir2


that queries does that. 

tom, I am working with oracle JDBC dirves and classes.
so I thought I should Ask oracle ratherthan sun !!
And sun... They ONLY give VERY HIGH LEVEL REFERENCE no easy implementation. Even if you see javadoc and all those Java API documentation, they give all methods only. NO EXAMPLE how to use it that is a big big issue with that. Because of your nature of explaing by exmaple I really find this site
"THE BEST" resource for oracle products.

yep, I know I can query database but If I already have the data in the connection object, I don't want to query the same informatio from database right ?

Thanks, for your reply.

Tom,

Is the scrollable cursor be appllied for ref cursor coming from a stored procedure? I remember reading in Pro*C documentation that ref cursor cannot be used for scrollable cursor. Is this case for java also?

Thanks

The answer is No. As far as I can tell
you cant use CallableStatement to return
a scrollable cursor (as of 10g r1)..

For that you have to use PreparedStatement
to use scrollable cursors ...

You can use the getType() at ResultSet interface to find
this out yourself, at statement level you use getResultSetType() method:

Consider the following package:
-----------------------------
scott@ORA10G> create or replace package pkg
2 as
3 procedure get_details( p_num_of_rows in number,
4 p_cursor in out sys_refcursor );
5 end;
6 /

Package created.

scott@ORA10G> show errors;
No errors.
scott@ORA10G>
scott@ORA10G> create or replace package body pkg
2 as
3 procedure get_details( p_num_of_rows in number,
4 p_cursor in out sys_refcursor )
5 is
6 begin
7 open p_cursor for
8 select x from t1 where x <= p_num_of_rows;
9 end;
10 end;
11 /

Package body created.

scott@ORA10G> show errors;
---------

We will invoke the above from Java (using 10g) program
TestScroll given below...
The method _testScroll tries to invoke the packaged procedure specifying that we want ResultSet of type
ResultSet.TYPE_SCROLL_INSENSITIVE and then
of type ResultSet.TYPE_SCROLL_SENSITIVE.
We then print out the resulting type of the ResultSet
to see if we got what we wanted at statement and
result set level in the method _printRsetTypeAndConcurrencyType() defined in the class
below.
-------------------------
import java.sql.*;
import java.io.IOException;
import oracle.jdbc.OracleTypes;

class TestScroll
{
public static void main(String args[]) throws Exception
{
Connection conn = null;
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection
("jdbc:oracle:thin:@rmenon-lap:1521:ora10g", "scott", "tiger");
_testScroll( conn, ResultSet.TYPE_SCROLL_INSENSITIVE );
_testScroll( conn, ResultSet.TYPE_SCROLL_SENSITIVE );
}
finally
{
// release the JDBC resources in the finally clause.
conn.close();
}
} // end of main()
private static void _testScroll( Connection conn, int resultSetType )
throws SQLWarning, SQLException
{
System.out.println( "Inside _testScroll" );
ResultSet rset = null;
CallableStatement cstmt = null;
try
{
cstmt = conn.prepareCall( "{call pkg.get_details( ?, ? )}",
resultSetType, ResultSet.CONCUR_READ_ONLY);
_printRsetTypeAndConcurrencyType( cstmt );
cstmt.setInt( 1, 10 );
cstmt.registerOutParameter( 2, OracleTypes.CURSOR );
cstmt.execute();
rset = (ResultSet) cstmt.getObject ( 2 );
_printRsetTypeAndConcurrencyType( rset );
}
finally
{
// release the JDBC resources in the finally clause.
rset.close();
cstmt.close();
}
}
private static void _printRsetTypeAndConcurrencyType( Statement stmt )
throws SQLWarning, SQLException
{
int resultSetType = stmt.getResultSetType();
switch( resultSetType )
{
case ResultSet.TYPE_FORWARD_ONLY:
System.out.print( "Forward only" );
break;
case ResultSet.TYPE_SCROLL_INSENSITIVE:
System.out.print( "Scroll insensitive" );
break;
case ResultSet.TYPE_SCROLL_SENSITIVE:
System.out.print( "Scroll sensitive" );
break;
}
int resultSetConcurrency = stmt.getResultSetConcurrency();
switch( resultSetConcurrency )
{
case ResultSet.CONCUR_READ_ONLY:
System.out.println( ", Read only" );
break;
case ResultSet.CONCUR_UPDATABLE:
System.out.println( ", Updatable" );
break;
}
}
private static void _printRsetTypeAndConcurrencyType( ResultSet rset)
throws SQLException
{
int resultSetType = rset.getType();
switch( resultSetType )
{
case ResultSet.TYPE_FORWARD_ONLY:
System.out.print( "Forward only" );
break;
case ResultSet.TYPE_SCROLL_INSENSITIVE:
System.out.print( "Scroll insensitive" );
break;
case ResultSet.TYPE_SCROLL_SENSITIVE:
System.out.print( "Scroll sensitive" );
break;
}
int resultSetConcurrency = rset.getConcurrency();
switch( resultSetConcurrency )
{
case ResultSet.CONCUR_READ_ONLY:
System.out.println( ", Read only" );
break;
case ResultSet.CONCUR_UPDATABLE:
System.out.println( ", Updatable" );
break;
}
}
} // end of program

-------------

When I run it I find the following output:

B:\>java TestScroll
Inside _testScroll
Scroll insensitive, Read only <---- This is what we asked
for (at statement level)
Forward only, Read only <----- what we got (at result
set level
Inside _testScroll
Scroll sensitive, Read only
Forward only, Read only <-- same here

In both cases the result set was silently "downgraded"
by the JDBC driver to "Forward Only" type - meaning
you cant scroll...
You can read about downgrade rules etc in the
section "ResultSet Limitations and Downgrade Rules"
of chapter "ResultSet Enhancements"...

And I agree with Tom - scrollable cursors do more harm
than good and their functionality can be implemented
much better using the normal "Forward Only" cursor.

I really appreciate your time and effort for the example provided.

Part of my code is

String query = "{call SP_MI_ORD_SRCH.SRCH_DATE(?,?,?,?,?,?)}";
oraCallStmt = con.prepareCall(query ,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
oraCallStmt.setString(1, "IN ('ZTCP')");
oraCallStmt.setString(2,"'2005-4-5'");
oraCallStmt.setString(3,"'2005-4-5'");
oraCallStmt.setString(4,"1000");
oraCallStmt.setString(5,"CWC");
oraCallStmt.registerOutParameter(6, OracleTypes.CURSOR);
oraCallStmt.execute();
rs = (ResultSet)oraCallStmt.getObject(6);
System.out.println("result set type=="+rs.getType());

Now how do i implement scrollable resultset here.becuase i get the TYPE_FORWARD_ONLY result set

As of 10gr1, Scrollability, positioning, sensitivity and updatability – none of these features are implemented for CallableStatement objects in Oracle JDBC drivers.
I mean they are implemented but only for executing
direct statements (such as insert etc.) through
CallableStatement but not for invoking stored procedures
which is where CallableStatement is useful :)

If you need to use these features you have to use PreparedStatement. In short, in your case you cant use scrollability. However,what exactly are you trying to achieve using the scrollability feature? If it is to paginate through result set rows (e.g. seeing a window of
10 rows out of 100 rows) then you should consider
using the much simpler and scalable alternative
discussed in </code> http://asktom.oracle.com/~tkyte/paginate.html

Make sure you use bind variables though if the query is
being genreated dynamically. For a solution that shows
you how to use bind variables in such cases, see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

Hi Tom,

i would like to call a stored Function which returns a Ref-Cursor and also takes some other IN/OUT parameters.

But i would like to use the new Named Parameter passing mechanism available in JDBC 3.0 , as illustrated in this Example:
</code> http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/oracle10g/namedparam/Readme.html <code>

Example:

String sqlCall = "BEGIN createProduct(?,?,?,?,?); END;";
cstmt = (CallableStatement)conn.prepareCall(sqlCall);

// CallableStatement interface is enhanced by adding new setter
// methods by name like below:
cstmt.setString("productname",name); // Set Product Name.
cstmt.setInt("minprice",minprice); // Set Product

// Register out parameter
// CallableStatement interface is enhanced to support
// registering output parameters by name.

cstmt.registerOutParameter("prod_id",Types.INTEGER);

//Execute the call.
cstmt.execute();


// Get the value of the id from the OUT parameter: prod_id
int id = cstmt.getInt("prod_id");


Now if i have :

String sqlCall =
"BEGIN ? := createProduct(?,?,?,?,?); END;";

where the 1st Ordinal is a Ref-Cursor(sys_refcursor) ,how
would i register it and get it's value using the New Feature as illustrated above?


Thanks in advance.

"Now if i have :

String sqlCall =
"BEGIN ? := createProduct(?,?,?,?,?); END;";

where the 1st Ordinal is a Ref-Cursor(sys_refcursor) ,how
would i register it and get it's value using the New Feature as illustrated
above?"
As of 10g Release 1, there’s no support for named parameters for a PL/SQL function, because
there’s no “name” of the returned parameter, and you can’t mix and match binding by name and binding by
ordinal parameter index.
Thus you have to use the standard ordinal parameters for this functionality
Last I heard, there were talks of possibly supporting
this functionality in future.

Hope this helps...

Thanx Tom!
I was unsure about plugging my book here - was wary
of readers raising issues with it being a self-promoting plug...:)

While we are at it, I would like to point out that
the description at amazon and some other sites
are outdated...

</code> http://www.amazon.com/exec/obidos/tg/detail/-/159059407X/104-8776675-6105530 <code>

It begins with
"With Oracle in the process of de-supporting SQLJ..."

Oracle did de-support SQLJ but reverted the decision
later "on popular demand" (the book states this). I have been trying for
ages to get them to change the description but
they are terribly slow when it comes to get this
done (unlike their customer service for customers
who buy things from their site.) Similarly, I stopped writing
articles on my internal Oracle web site for quite
some time (as given in the bio at amazon) since I
was quite busy with the book.

The link you gave above at apress has the correct
description.

PS: Just came back from vacation in India - I see that your
blog is becoming more and more interesting!

I am not employed with Oracle any more - decided to move
on to a startup.

Hi Tom and Menon,

thanks for your follow-ups.

Menon wrote:
"
As of 10g Release 1, there’s no support for named parameters for a PL/SQL function, because there’s no “name” of the returned parameter, and you can’t mix and match binding
by name and binding by ordinal parameter index."
>>>>
i saw nowhere in Oracle Docs where this is explicitly said.

and Menon said that there’s no “name” of the returned parameter( ref-Cursor ) ,

what if i have a ref-cursor parameter as OUT value in procedure header ?

like this:

String sqlCall =
"BEGIN createProduct(?,?,?,?,? , ?); END;";

supposing the 6th ordinal is an OUT ref-cursor parameter.


All in all, i well never get a chance to use this feature because all of my Functions/Procedures have IN paramaters and also give back ref-cursors

"i saw nowhere in Oracle Docs where this is explicitly said."

I don't think it is documented in Oracle documentation -
when I was saying that the book states it explicitly
I was referring to my book...

"and Menon said that there’s no “name” of the returned parameter( ref-Cursor ) ,

what if i have a ref-cursor parameter as OUT value in procedure header ?

like this:

String sqlCall =
"BEGIN createProduct(?,?,?,?,? , ?); END;";

supposing the 6th ordinal is an OUT ref-cursor parameter."

Well, the problem is true only for "functions" - NOT
procedures. For all procedures you do have the formal parameter names. For example, consider the
package defined as follows (straight from my book)...

--------- package definition---
create or replace package callable_stmt_demo
as
function get_emp_details_func( p_empno in number )
return sys_refcursor;
procedure get_emp_details_proc( p_empno in number,
p_emp_details_cursor out sys_refcursor );
procedure get_emps_with_high_sal( p_deptno in number,
p_sal_limit in number default 2000 ,
p_emp_details_cursor out sys_refcursor );
procedure give_raise( p_deptno in number );
end;
/
show errors;

create or replace package body callable_stmt_demo
as
function get_emp_details_func( p_empno in number )
return sys_refcursor
is
l_emp_details_cursor sys_refcursor;
begin
open l_emp_details_cursor for
select empno, ename, job
from emp
where empno = p_empno;

return l_emp_details_cursor;
end;
procedure get_emp_details_proc( p_empno in number,
p_emp_details_cursor out sys_refcursor )
is
begin
p_emp_details_cursor := get_emp_details_func(
p_empno => p_empno );
end;
procedure get_emps_with_high_sal( p_deptno in number,
p_sal_limit in number default 2000 ,
p_emp_details_cursor out sys_refcursor )
is
begin
open p_emp_details_cursor for
select empno, ename, job, sal
from emp
where deptno = p_deptno
and sal > p_sal_limit;
end;
procedure give_raise( p_deptno in number )
is
begin
update emp
set sal = sal * 1.5
where deptno = p_deptno;
end;
end;
/
show errors;
---------
In the above case in the function get_emp_details_func(),
the return parameter is of type sys_refcursor but has
no name. This is true, of course regardless of the type of the return parameter - does not have to be a sys_refcursor. At the same time, in the procedure
get_emp_details_proc, the "OUT" parameter is named
p_emp_details_cursor which can be used in your Java code.

For example the following Java function demonstrates
named parameter usage (part of a class from the book that demonstrates various other functionalities for invoking
functions/procedures) for the procedure get_emp_details_proc() in the above package
-------------
private static void _demoOracleSyntaxProcedureBindByName( Connection conn )
throws SQLException
{
System.out.println( "\nExample 3, Oracle syntax, calling a procedure, bind by name" );
int inputEmpNo = 7369;
ResultSet rset = null;
CallableStatement cstmt = null;
try
{
// The procedure invoked below has a signature of:
// procedure get_emp_details_proc( p_empno in number,
// p_emp_details_cursor out sys_refcursor )

// formulate a callable statement string using Oracle style
// syntax
String oracleStyle =
"begin callable_stmt_demo.get_emp_details_proc(?, ?); end;";
// create the CallableStatement object
cstmt = conn.prepareCall( oracleStyle );
// bind the input value by name
cstmt.setInt("p_empno", inputEmpNo );
// register the output value
cstmt.registerOutParameter( "p_emp_details_cursor", OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( "p_emp_details_cursor" );
// print the result
while (rset.next())
{
int empNo = rset.getInt ( 1 );
String empName = rset.getString ( 2 );
String empJob = rset.getString ( 3 );
System.out.println( empNo + " " + empName + " " + empJob );
}
}
finally
{
// release JDBC resources in finally clause.
JDBCUtil.close( rset );
JDBCUtil.close( cstmt );
}
}
-------

In particular, the following code line shows how to
register the out parameter by name:
-------
cstmt.registerOutParameter( "p_emp_details_cursor", OracleTypes.CURSOR );
-------

Hope that answers your question. Just for completeness,
I am giving the listing of the class from the book, whose method I showed above. It demonstrates the following:
1. Using named parameter and ordinal parameter binding
2. Using named parameter when default values exist and
you dont want to specify the value in your Java code.
3. Oracle syntax (the one you and I were using) and
the SQL92 Syntax ( a recommended alternative that
enhances the portability of your Java code across
databases without having any downsides.
The class is given below.
----
/** This program demonstrates how to use CallableStatement.
* It demonstrates how to use
* 1. SQL 92 syntax for calling stored procedures.
* 2. Oracle syntax for calling stored procedures.
* 3. Using bind by parameter index, bind by parameter index
* using parameter names and bind by parameter name.
* COMPATIBLITY NOTE:
* runs successfully against 10.1.0.2.0.
* Against 9.2.0.1.0, you have to comment out the
* code using binding by name feature to compile and
* run this as bind by name is not supported in 9i.
*/
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import book.util.JDBCUtil;
import book.util.Util;
class DemoCallableStatement
{
public static void main(String args[])
{
Util.checkProgramUsage( args );
ResultSet rset = null;
Connection conn = null;
CallableStatement cstmt = null;
try
{
// get connection - make sure you modify this call and
// the JDBCUtil.getConnection() method to point to
// your database, user and password.
conn = JDBCUtil.getConnection("scott", "tiger", args[0]);
//////////// Example 1 ///////////////
//////////// SQL 92 Syntax, procedure ///////////////
//////////// parameter bound by index ///////////////
_demoSql92SyntaxProcedureBindByIndex( conn );
//////////// Example 2 ///////////////
//////////// Oracle Syntax, function ///////////////
//////////// parameter bound by index ///////////////
_demoOracleSyntaxFunctionBindByIndex( conn );
//////////// Example 3 ///////////////
//////////// Oracle Syntax, procedure ///////////////
//////////// parameter bound by name ///////////////
_demoOracleSyntaxProcedureBindByName( conn );
//////////// Example 4 ///////////////
//////////// Oracle Syntax, procedure ///////////////
//////////// named parameter ///////////////
//////////// procedure has parameter ///////////////
//////////// with default value. ///////////////
_demoOracleSyntaxProcedureBindByNameWithDefault( conn );
//////////// Example 5 ///////////////
//////////// Oracle Syntax, procedure ///////////////
//////////// update example ///////////////
_demoOracleSyntaxProcedureBindByNameUpdate( conn );
}
catch (SQLException e)
{
// print stack trace.
JDBCUtil.printException( e );
}
finally
{
// close the connection in finally clause
JDBCUtil.close( conn );
}
}
//////////// PRIVATE SECTION ////////////////
private static void _demoSql92SyntaxProcedureBindByIndex( Connection conn )
throws SQLException
{
System.out.println( "Example 1, SQL 92 syntax, calling a procedure, binding by index" );
int inputEmpNo = 7369;
CallableStatement cstmt = null;
ResultSet rset = null;
try
{
// The procedure invoked below has a signature of:
// procedure get_emp_details_proc( p_empno in number,
// p_emp_details_cursor out sys_refcursor )

// formulate a callable statement string using sql 92
// syntax
String sql92Style =
"{ call callable_stmt_demo.get_emp_details_proc(?,?) }";
// create the CallableStatement object
cstmt = conn.prepareCall( sql92Style );
// bind the input value
cstmt.setInt(1, inputEmpNo );
// register the output value
cstmt.registerOutParameter( 2, OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( 2 );
// print the result
while (rset.next())
{
int empNo = rset.getInt ( 1 );
String empName = rset.getString ( 2 );
String empJob = rset.getString ( 3 );
System.out.println( empNo + " " + empName + " " + empJob );
}
}
finally
{
// release JDBC resources in finally clause.
JDBCUtil.close( rset );
JDBCUtil.close( cstmt );
}
}
private static void _demoOracleSyntaxFunctionBindByIndex( Connection conn )
throws SQLException
{
System.out.println( "\nExample 2, Oracle syntax, calling a function, binding by index" );
int inputEmpNo = 7369;
ResultSet rset = null;
CallableStatement cstmt = null;
try
{
// The function invoked below has a signature of:
// function get_emp_details_func( p_empno in number )
// return sys_refcursor

// formulate a callable statement string using Oracle style
// syntax
String oracleStyle =
"begin ? := callable_stmt_demo.get_emp_details_func(?); end;";
// create the CallableStatement object
cstmt = conn.prepareCall( oracleStyle );
// bind the input value
cstmt.setInt(2, inputEmpNo );
// register the output value
cstmt.registerOutParameter( 1, OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( 1 );
// print the result
while (rset.next())
{
int empNo = rset.getInt ( 1 );
String empName = rset.getString ( 2 );
String empJob = rset.getString ( 3 );
System.out.println( empNo + " " + empName + " " + empJob );
}
}
finally
{
// release JDBC resources in finally clause.
JDBCUtil.close( rset );
JDBCUtil.close( cstmt );
}
}
private static void _demoOracleSyntaxProcedureBindByName( Connection conn )
throws SQLException
{
System.out.println( "\nExample 3, Oracle syntax, calling a procedure, bind by name" );
int inputEmpNo = 7369;
ResultSet rset = null;
CallableStatement cstmt = null;
try
{
// The procedure invoked below has a signature of:
// procedure get_emp_details_proc( p_empno in number,
// p_emp_details_cursor out sys_refcursor )

// formulate a callable statement string using Oracle style
// syntax
String oracleStyle =
"begin callable_stmt_demo.get_emp_details_proc(?, ?); end;";
// create the CallableStatement object
cstmt = conn.prepareCall( oracleStyle );
// bind the input value by name
cstmt.setInt("p_empno", inputEmpNo );
// register the output value
cstmt.registerOutParameter( "p_emp_details_cursor", OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( "p_emp_details_cursor" );
// print the result
while (rset.next())
{
int empNo = rset.getInt ( 1 );
String empName = rset.getString ( 2 );
String empJob = rset.getString ( 3 );
System.out.println( empNo + " " + empName + " " + empJob );
}
}
finally
{
// release JDBC resources in finally clause.
JDBCUtil.close( rset );
JDBCUtil.close( cstmt );
}
}
private static void _demoOracleSyntaxProcedureBindByNameWithDefault( Connection conn )
throws SQLException
{
System.out.println( "\nExample 4, Oracle syntax, calling a procedure, named parameter (with default value)" );
int inputEmpNo = 7369;
ResultSet rset = null;
CallableStatement cstmt = null;
try
{
// The procedure invoked below has a signature of:
// procedure get_emps_with_high_sal( p_deptno in number,
// p_sal_limit in number default 2000 ,
// p_emp_details_cursor out sys_refcursor )

// formulate a callable statement string using Oracle style
// syntax
String oracleStyle =
"begin callable_stmt_demo.get_emps_with_high_sal(?, ?); end;";
// create the CallableStatement object
cstmt = conn.prepareCall( oracleStyle );
// bind the input value by name
cstmt.setInt("p_deptno", 10 );
// no need to pass the second parameter "p_sal_limit"
// which gets a default value of 2000
// register the output value
cstmt.registerOutParameter( "p_emp_details_cursor",
OracleTypes.CURSOR );
// execute the query
cstmt.execute();
rset = (ResultSet) cstmt.getObject( "p_emp_details_cursor" );
// print the result
while (rset.next())
{
int empNo = rset.getInt ( 1 );
String empName = rset.getString ( 2 );
String empJob = rset.getString ( 3 );
int empSal = rset.getInt ( 4 );
System.out.println( empNo + " " + empName + " " + empJob + " " +
empSal );
}
}
finally
{
// release JDBC resources in finally clause.
JDBCUtil.close( rset );
JDBCUtil.close( cstmt );
}
}
private static void _demoOracleSyntaxProcedureBindByNameUpdate( Connection conn )
throws SQLException
{
System.out.println( "\nExample 5, Oracle syntax, calling a procedure, update example" );
CallableStatement cstmt = null;
try
{
// The procedure invoked below has a signature of:
// procedure give_raise( p_deptno in number )

// formulate a callable statement string using Oracle style
// syntax
String oracleStyle =
"begin callable_stmt_demo.give_raise( ? ); end;";
// create the CallableStatement object
cstmt = conn.prepareCall( oracleStyle );
// bind the input value by name
cstmt.setInt("p_deptno", 10 );
// execute
cstmt.execute();
conn.commit();
}
catch (SQLException e)
{
// print a message and rollback.
JDBCUtil.printExceptionAndRollback( conn, e );
}
finally
{
// release JDBC resources in finally clause.
JDBCUtil.close( cstmt );
}
}
}

----

Hi all,

When making calls from Java to PL/SQL - is it better to use Refcursors as the out parameters from PL/SQL, or is it better to use types (the types are not local to a PL/SQL package, but are created in SQL, with execute privilege granted to PUBLIC)?

What are the advantages/disadvantages of one over the other?

Thank you

The examples above explain how to pass a ref cursor back to a Java client, but is there a way to pass this to a PL/SQL proc?

I'm trying to implement progress feedback, so I open a ref cursor in PL/SQL and pass it back to the Java client. The Java client tries to pass the ref cursor to a second PL/SQL proc, which runs through 500 rows and returns some status. The Java client then updates its own status (say, for display on a web page), and calls the second PL/SQL again.

But I can't pass the ref cursor as a parameter, nor can I store it in a package global variable. The general steps are these:

1. Get the ref cursor (CallableStatement, registerOutParameter, execute, getObject)
2. Bind parameters to the stored proc (CallableStatement, registerOutParameters, setObject--which fails)
3. Call the stored proc (execute, getXXX() for the status)
4. Update the status on the Java side
5. Do 3 and 4 until no rows are left.

Am I totally off on my design? I had performance issues doing the logic on the Java side, I figured the round trips were costing me time and stuck it into a PL/SQL procedure. The logic is simple, but I still need the status update, and I need to persist this silly ref cursor if my method is the only viable way of doing this.

In your example above, you use "ResultSet.CONCUR_UPDATABLE" as a parameter. If I do not intend to make any updates to the result set, would I be better off just using "ResultSet.CONCUR_READ_ONLY"?

//Create an scrollable insensitive updatable cursor
Statement stmt =
con.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);


Also you mentioned that scrollable cursor is an evil thing. Can you please elaborate a little bit on that?

thanks

There's also the issue that many sql statements are not "concur-updatable", and are silently downgraded by the JDBC driver to concur_read_only - but in order to discover it, extra processing (a PARSE ERROR) is needed.

Eg inject a "distinct" in Tom's code, trace it, and you'll find

=====================
PARSE ERROR #4:len=44 dep=0 uid=59 oct=3 lid=59 tim=14362434704 err=936
select rowid, distinct empno from scott.emp
WAIT #4: nam='SQL*Net break/reset to client' ela= 5 p1=1952673792 p2=1 p3=0
WAIT #4: nam='SQL*Net break/reset to client' ela= 8196 p1=1952673792 p2=0 p3=0
WAIT #4: nam='SQL*Net message to client' ela= 5 p1=1952673792 p2=1 p3=0
WAIT #4: nam='SQL*Net message from client' ela= 2405 p1=1952673792 p2=1 p3=0
=====================
PARSING IN CURSOR #4 len=36 dep=0 uid=59 oct=3 lid=59 tim=14362459812 hv=1005143425 ad='6ce96904'
select distinct empno from scott.emp
END OF STMT
PARSE #4:c=0,e=636,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=14362459804

So - an extra network roundtrip, extra parsing cpu, etc.

Please note that the code works, the error is totally hidden from the application.

This is particularly an issue if this is done "just in case" for every statement ... as I've seen done by some teams (that's the reason I remember it - investigated a couple of statspacks with a lot of waits on 'SQL*Net break/reset to client', etc).

I have a function which returns 5 cursors, couple of them having 1 Million rows. The execution of those cursor (queries) doesnt take much time(10-15 secs) when run in sql plus.

But once the data is sent to java(thats the front end), it takes lot of time, as they have rs.next statments being run to fetch the data (somewhere around 10 minutes)

My questions now. where does oracle/java keep those rrows when they are in transit. is it kept in oracle memory or java memory or some middleware ?

how can we tune this kind of situations...

Thanks for ur time.