Andrej Koelewijn

10/29/2003

Using oracle ref cursors in java

Filed under: — andrejk @ 9:31 pm

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.

30 Responses to “Using oracle ref cursors in java”

  1. Mondal K Says:
    November 28th, 2003 at 10:19 am

    Hi Andrej,

    I am invoking one Java SP from my business object residing in middle tier. How to pass a vector object to SP from the calling program.

    Thanks in advance.

    Mondal

  2. Andrej Says:
    December 3rd, 2003 at 8:13 pm

    I think you need to convert the vector to an array and pass the array to to the stored procedure.

  3. Hari Says:
    January 16th, 2004 at 1:53 am

    In WSAD 5.1 I am trying to use the above but I get a class cast exception saying
    [1/15/04 19:48:16:854 EST] 7204aca ExceptionUtil E CNTR0020E: Non-application exception occurred while processing method “runWithNoTransaction” on bean “BeanId(AlpineOasys#EJBCommon.jar#TransactionHelper, null)”. Exception data: java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement on the line where we try to cast statement to oraclecallablestatment. If I use (ResultSet) stmt.getObject(1) then the performance is poor. Is there any other way? Please reply

  4. Andrej Says:
    January 16th, 2004 at 8:45 am

    Are you using the Oracle jdbc drivers?

  5. Nicolas P Says:
    January 26th, 2004 at 11:38 am

    Hi,

    I have made a package with a record type as follow :

    CREATE OR REPLACE PACKAGE PKG_BOMC_OK AS

    TYPE TYP_REC_BK IS RECORD;
    FUNCTION GET_OK_FED(l_TYP_REC_BK IN TYP_REC_BK, p_isprimary IN BOOLEAN) RETURN VARCHAR2;
    END PKG_BOMC_OK;
    /

    CREATE OR REPLACE PACKAGE BODY PKG_BOMC_OK AS
    FUNCTION GET_OK_FED(l_TYP_REC_BK IN TYP_REC_BK, p_isprimary IN BOOLEAN) RETURN VARCHAR2 IS p_idt VARCHAR2;

    BEGIN
    IF l_TYP_REC_BK.col2 IS NULL THEN
    /*
    */
    ELSE
    SELECTcol1
    INTOp_idt
    FROMtab1
    WHEREcol2 = l_TYP_REC_BK.col2;
    RETURN p_idt;
    END IF;
    END GET_OK_FED;
    END PKG_BOMC_OK;
    /

    I wrote PL/SQL code to use it :

    DECLARE
    struct PKG_BOMC_OK.TYP_REC_BK;
    BEGIN

    struct.col2 := ‘BOITE—JB’;
    dbms_output.put_line(PKG_BOMC_OK.GET_OK_FED(struct, false));
    END;
    /

    and it works.

    I would like to use this function in Java as the PL/SQL code above, but I don’t know how to do. Maybe using prepareCall and CallableStatement, but I can’t find the OracleType to use to match a RECORD type.

    Can you help me ? Thanks in advance.

    Nicolas

  6. Andrej Says:
    January 26th, 2004 at 2:17 pm

    You cannot directly use pl/sql RECORD types in jdbc calls: PL/SQL TABLE, BOOLEAN, and RECORD Types. You’ll have to create a sql type for the record, then you can use it using a jdbc struct: Using the Default STRUCT Class for Oracle Objects

  7. Andrej Says:
    January 26th, 2004 at 2:19 pm

    Ah, i disabled links in comments to avoid spam comments, so here are the links:

    #1: http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/ref.htm#1005988

    #2: http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/oraoot.htm#1039477

  8. Amol Says:
    January 30th, 2004 at 10:05 am

    Is there any resource which contains a sample code for passing record or cursor to a oracle stored procedure from a java program.

    Thanks in Advance,
    Amol

  9. Alka Says:
    February 25th, 2004 at 4:02 pm

    Hi Amol,
    Could u get a response to ur above query?
    ‘Is there any resource which contains a sample code for passing record or cursor to a oracle stored procedure from a java program.’

    I need it urgently.

    Thanks, – Alka

  10. Surendra Babu Says:
    March 6th, 2004 at 4:38 pm

    Hi Alka and Amol,

    I don’t know a way to pass Record/Cursor to a Oracle stored procedure.
    But, there is an alternate way using XML.

    You can convert your record into an XMl string and pass it Oracle Proc as a String parameter.
    For Ex.
    <EMPREC>
    <EMPNO>10023</EMPNO>
    <EMPNAME>Bharat</EMPNAME>
    </EMPREC>

    On the Oracle side, you can convert this XML string into pl/SQL variables.
    Code:
    NOTE: I don’t have Oracle on my system at this moment. please bare with me if there are any syntax errors. If you have nay trouble with this code, let me know, I can send you tested code on tomorrow.

    PROCEDURE (sEmpRec VARCHAR2) IS

    sEmpNo VARCHAR2;
    sEName VARCHAR2;

    BEGIN

    —XML Tags are case sensitive, Please becarefull with this.

    sEmpno := XMLType.Extract(sEmprec, ’/EMPREC/EMPNO/text()’.getStringVal() ;
    sEname := XMLType.Extract(sEmprec, ’/EMPREC/EMPNAME/text()’.getStringVal();

    END

    Regards
    Surendra.

  11. Rajesh jha Says:
    March 13th, 2004 at 3:28 pm

    Hi
    i know how to use cursor in java. but i want to use collection return by procedure so tell me how thsi is implemented in java.

    regards
    rajesh jha
    Mumbai

  12. Levi Says:
    April 6th, 2004 at 7:05 pm

    You mention…

    “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.”

    How exactly do you go about doing that?

  13. Boris Says:
    May 3rd, 2004 at 10:29 am

    Hello Andrej
    All of your samples (and OTN too) show howto
    get ref cursor from stored procedure that is actually a function.
    Is there way to get ref cursor (out or in out) from a procedure ?

    Thank you in advance

  14. Andrej Says:
    May 4th, 2004 at 3:43 pm

    Using a procedure is not much different. In the example above (String usersSql = ”{ ? := cursors_pkg.getUsers; }”;), the question mark will be in a different place: { cursors_pkg.getUsers(?); }, the rest of the java code should be the same.

    The oracle pl/sql documentation has an example how to return a ref cursor from a stored procedure:

    CREATE PACKAGE BODY emp_data AS

    PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
    BEGIN
    OPEN emp_cv FOR SELECT * FROM employees;
    END open_emp_cv;
    END emp_data;
    /

    (See: http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10807/06_ora.htm#sthref807)

    Hope this helps

  15. Joe Marlow Says:
    December 8th, 2004 at 4:51 pm

    Hello,

    Has anyone been able to get this working with apache.commons.dbutils?

    Thanks

  16. John Harris Says:
    June 30th, 2005 at 3:38 pm

    Hi Andrej, I have inherited a system which returns ref cursors from a database to a Java layer. This works fine but they now want to add a second database layer inbetween the existing Java & database for extra security (so we will have Java layer, ‘stepping stone’ database layer & original database where data is stored). The two databases will be joined by a database link. I believe that ref cursors will not work over database links – can you confirm this &, if this is the case, can you suggest any alternative approaches.

    Many Thanks
    John

  17. andrejk Says:
    July 1st, 2005 at 6:53 am

    The oracle documentation seems to indicate that there are indeed restrictions using ref cursors in combination with database links, see: http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/06_ora.htm#sthref852

    Quote: You cannot pass cursor variables to a procedure that is called through a database link.

    Improving security by adding an extra database doesn’t seem the right approach to me. Did you already put the http server and the java web container on separate machines with a firewall in between?

  18. John Harris Says:
    July 1st, 2005 at 9:36 am

    Andrej, my knowledge of Oracle is poor as I am primarily a Java developer. The infrastructure is already in place & I have no control over this. I am simply trying to find a solution/alternative to the limitation of using ref cursors over database links. Any suggestions would be welcome.

    Regards
    John

  19. andrejk Says:
    July 4th, 2005 at 10:28 pm

    I haven’t tried using ref cursors with database links, but the restriction might not apply to your situation.

    If i understand you correctly, you have a real database A and a ‘stepping stone’ database B. You call functions in database B from your java code. These plsql functions create ref cursors selecting data from objects on database A accessed through a database link.

    For example, you might have the following function in database B:

    FUNCTION getusers RETURN refcursortype IS
    alluserscursor refcursortype;
    BEGIN
    OPEN alluserscursor FOR
    SELECT username
    , user_id
    , created
    FROM all_users@A;
    RETURN alluserscursor;
    END;

    I think the restriction mentioned in the oracle database is something else: You create a ref cursor on database B, selecting data in tables and views on database B, and then you pass the ref cursor as a parameter to a function in database A. The ref cursor doesn’t make any sense in database A, as the select statement refers to tables and views in database B.

  20. Chet Says:
    January 24th, 2006 at 5:15 am

    Hi Andrejk,

    It would be great if you could provide me some insight into my problem. We have a requirement within our application which requires us to pass ref cursors back to Java wherein they are used to write the selected columns from the resultset to flat files. There are two questions that I have:

    1. Is there a particular rowcount or size limitation when passing the ref cursors back? (I understand that these are just pointers to the location hence there should not be any limitation)

    2. When I use this ref cursor to get only a subset of the rows it is supposed to return by explicitly saying ” and rownum

  21. site admin Says:
    January 24th, 2006 at 7:29 am

    1. don’t know
    2. rownum always counts the rows actually returned, so you can’t use it for a subset. You can return subsets by using subqueries. For an example see: http://andrej.racchvs.com/archives/2003/12/24/use-rownum-instead-of-scrollable-resultsets/

  22. Stephen Says:
    May 10th, 2006 at 12:36 am

    Hi Andrej,

    My requirement is to invoke an oracle stored procedure from JAVA.

    Following is the scenario

    1) Package Header————————-
    CREATE OR REPLACE PACKAGE UDE_Test AS
    TYPE rt_input IS RECORD
    (

    p_input1 VARCHAR2,
    p_input2 DATE,
    );
    TYPE rt_output IS RECORD
    (
    v_output1 VARCHAR2,
    v_output2 DATE,
    );
    PROCEDURE UDE_TestProc
    (
    p_rt_input IN UDE_Test.rt_input,
    p_rt_output OUT UDE_Test.rt_output
    );
    END UDE_Main;
    /

    2) Package body————————PROCEDURE UDE_TestProc
    (

    p_rt_input IN UDE_Test.rt_input,
    p_rt_output OUT UDE_Test.rt_output
    )
    AS

    v_one VARCHAR2;
    v_two DATE;

    CURSOR c_get_test IS
    SELECT t1.col1,

    t2.col2
    FROM table1 t1,
    table2 t2
    WHERE t1.PK_REF = “1000”
    AND t1.PK_REF = t2.PK_REF;

    BEGIN

    OPEN c_get_test;
    FETCH c_get_test

    INTO p_rt_output.v_output1,
    p_rt_output.v_output2;
    CLOSE c_get_test;

    END UDE_TestProc
    /

    Query——-

    My requirement is to call this stored procedure from java using an CallableStatment by passing an input record type and to

    fetch the output record type.

    From your article I found that passing RECORD type from java is not possible.

    FYI, Implementation in the above format is already been done in oracle layer and Java part is yet to start. In this

    situation, If we need to modify the backend layer, Please let me know the best way to proceed where the impact is less.

    For the above said scenario what would be best design to create an procedure and the best way to make a call to this

    procedure from JAVA.

    Awaiting for your reply.

    Thanks,
    Stephen

  23. Srimathi Says:
    May 25th, 2006 at 9:30 am

    Hi Andrej,

    Don’t we have to close the ref cursor variables somehow, to release the memory? If not won’t it lead to memory leak?
    But I guess we cannot close the ref cursors in the procedure/function from which the cursor is returned, because then the cursor cannot be accessed from java. Is there anyother way to go about it?
    Please let us know…

    Thanks in advance.

    -Srimathi

  24. Dhananjay Says:
    September 22nd, 2006 at 4:28 pm

    Hi,

    We are using oracle.jdbc.driver.OracleDriver and calling a pl/sql function which returns a ref cursor
    we are getting following error
    java.sql.SQLException: Bigger type length than Maximum

    waiting for earliest reply…

    Thanks & regards,
    Dhananjay

  25. rahul Says:
    October 2nd, 2006 at 6:47 pm

    You mention…

    “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.”

    How exactly do you go about doing that?

    Could you tell me that.
    thanks
    rahul

  26. Sisir Says:
    October 17th, 2006 at 2:44 pm

    How do I pass a ref cursor as IN parameter to a Stored Procedure, using JAVA and using Oracle JDBC thin driver.
    A code snippet would be helpful?

  27. Prasad Agaskar Says:
    January 8th, 2007 at 1:23 pm

    Hi
    I am using Oracle 9i. I want to do the following
    select a.something, cursor( select b.something from b where b.id = a.id )

    how do i access the second part the query in a resultset.
    I tried to do a (ResultSet) st.getObject() but it throws a
    wierd exception like:
    java.sql.SQLException: Internal Error: need resolution: do we want to handle ResultSet?

    Any suggestions are welcome.
    Thanks
    Prasad

  28. Priya Chakraborty Says:
    January 9th, 2007 at 8:35 am

    sir,

    I want to acess video files from database(oracle) on J2ME wireless toolkit , what is the possible architecture reqiured . Do I need any middle ware language or servlets to access it.

  29. Anto Says:
    March 12th, 2007 at 2:22 pm

    Hi,

    If some one could possibly help us on how to pass a Ref cursor (or some representation of a collection of DTO’s)as a IN Parameter from Java to an oracle SP, it would be highly appriciated.

    Thanks in advance.

    Anto

  30. Rajiv Says:
    June 19th, 2007 at 6:21 am

    Can you please tell me what is the difference/benefit in using weekly typed and strongly typed cursors?.
    I am a java developer, i was calling SQL function which is returning a strogly typed cursor. I was expecting column names in resultset will be same as the names mentioned in SQLType.
    but i was getting column names as what we have used in query.

    Is there any way to retrive data from resultset using SqlType names instead of column names in query.

Leave a Reply

Powered by WordPress