I’ve noticed in my web logs that the item i wrote about ref
cursors gets a lot of hits from google. As this item is only a link to
a dutch article, i thought it might be usefull to provide a translation:
There are different methods to get
data out of your Oracle database when using java. The most common
method is to use JDBC. Other options include Oracle business components
for java (BC4J) and Toplink. Database communication can also be
handled by a j2ee container, when you use container managed persistence
for entity beans. A disadvantage of using jdbc is that the programmer
needs java and sql knowledge.
The example below shows how jdbc is
usually used. You instantiate a statement object for a query, you
specify the parameter values, execute the query and then you loop
through the rows in the resultset.
String usersSql = "select username, user_id, created from all_users";
PreparedStatement stmt = _connection.prepareStatement(usersSql);
ResultSet rset = stmt.executeQuery();
while (rset.next())
{
String username = rset.getString(1);
BigDecimal userId = rset.getBigDecimal(2);
Date created = rset.getDate(3);
}
rset.close();
stmt.close();
As mentioned, a disadvantage of this
approach is that the programmer needs to know both sql and java. And to
write performing queries, the programmer needs database specific
knowledge, for example about oracle hints which can be using in queries.
By using pl/sql ref cursors you can avoid
that the java programmer needs to have a lot of sql knowledge. All
queries can be defined in oracle package in the database, and the java
programmer can use these queries by calling pl/sql stored procedures.
The stored procedure will return a ref cursor. The result
of the ref cursor can be read by normally looping of a resultset.
The example below shows how a ref cursor
is defined in a pl/sql package. The result of calling the pl/sql
function getusers is a pointer to a cursor.
CREATE OR REPLACE PACKAGE cursors_pkg
IS
TYPE refcursortype IS REF CURSOR;
FUNCTION getusers RETURN refcursortype;
END;
/
CREATE OR REPLACE PACKAGE BODY cursors_pkg IS
FUNCTION getusers RETURN refcursortype IS
alluserscursor refcursortype;
BEGIN
OPEN alluserscursor FOR
SELECT username
, user_id
, created
FROM all_users;
RETURN alluserscursor;
END;
END;
After the ref cursor has been defined, it
can easily be used by java, as the following example shows:
String usersSql = "{ ? := cursors_pkg.getUsers; }";
CallableStatement stmt = _connection.prepareCall(usersSql);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
ResultSet rset =((OracleCallableStatement) stmt).getCursor(1);
while (rset.next())
{
String username = rset.getString(1);
BigDecimal userId = rset.getBigDecimal(2);
Date created = rset.getDate(3);
}
rset.close();
stmt.close();
As you can see, the difference with the
previous example isn’t very big. We are still using a resultset to loop
over all the records. The only difference is that the query has been
defined in the pl/sql pacakge. The only sql code you are left with is
the function call. This is a lot easier to write than sql queries (no
hints needed, no wrong joins possible), and a lot more secure, as you
don’t have to hand out direct access to tables, only access to the
pl/sql code.
The example uses oracle specific java classes, but you can also
rewrite it, so that it doesn’t use anything oracle specific.
Postgresql, for example, also supports ref cursors, so if you want
portable java code, be sure not to use the oracle specific classes.
In the example above, a weakly
typed cursor was used. You can also use strongly typed cursors, e.g.,
by specifying a record type in the package specification. This is shown
in the following example:
CREATE OR REPLACE PACKAGE cursors_pkg IS
TYPE userstype IS RECORD
( username VARCHAR2(30)
, userid NUMBER
, created DATE );
TYPE userscursortype IS REF CURSOR RETURN userstype;
FUNCTION getusers RETURN userscursortype;
END;
I’ve done some tests, to see if
using ref cursors has a negative impact on performance. This doesn’t
seem to be the case. So using ref cursors seems to be usefull when you
have a team with java specific and oracle specific programmers.
I might use it on a next project. The problem with unix shell scripts is that there are a lot of pl/sql developers who are not familiar with unix and shell programming. Another advantage of utplsql is that it may become a standard for writing unit tests, which will make it easier to explain developers what to deliver when i want them to produce unit tests.
Now can anyone recommend me a thriller which includes steve jobs or bill gates?