Exemplo em Java de como usar um REF CURSOR com Tibero

Neste exemplo demonstraremos como realizar uma chamada a um objeto do tipo REF CURSOR no banco de dados Tibero, criado em uma FUNCTION de uma PACKAGE no banco, a partir de um código Java utilizando o driver JDBC do Tibero.

Código de exemplo (RefCursorExample.java):

import java.sql.*;
import java.io.*;
import com.tmax.tibero.*;

/*
DML/DDL for this test:

create table TIBERO.TB_USER (id number, name varchar(50));

insert into tibero.tb_user values (01, 'Name 01');
insert into tibero.tb_user values (02, 'Name 02');
insert into tibero.tb_user values (03, 'Name 03');
insert into tibero.tb_user values (04, 'Name 04');
insert into tibero.tb_user values (05, 'Name 05');
insert into tibero.tb_user values (06, 'Name 06');
insert into tibero.tb_user values (07, 'Name 07');
insert into tibero.tb_user values (08, 'Name 08');
insert into tibero.tb_user values (09, 'Name 09');
insert into tibero.tb_user values (10, 'Name 10');
commit;

create or replace package TIBERO.JAVA_REFCURSOR
as
  type myrctype is ref cursor return TIBERO.TB_USER%ROWTYPE;

  function JOB_LISTING return myrctype;
end java_refcursor;

create or replace package body TIBERO.JAVA_REFCURSOR as
  function JOB_LISTING return myrctype
  is
rc myrctype;
  begin
open rc for select * from TIBERO.TB_USER;
return rc;
  end;
end java_refcursor;

Compiling Java test program: (Copy Tibero JDBC driver (tibero6-jdbc.jar) here before to compile)

javac -g -verbose -classpath .\tibero6-jdbc.jar RefCursorExample.java

Executing Java test program:

java -cp .\tibero6-jdbc.jar; RefCursorExample
*/
public class RefCursorExample
{
  public static void main (String args []) throws SQLException
  {
    // Load the driver
    DriverManager.registerDriver(new com.tmax.tibero.jdbc.TbDriver());

    // Connect to the database
    Connection conn = DriverManager.getConnection ("jdbc:tibero:thin:@100.100.100.100:8629:tibero", "TIBERO", "tmax");

    // Prepare a PSM call
    CallableStatement call = conn.prepareCall ("{ ? = call java_refcursor.job_listing }");

    // Get the result set
    call.registerOutParameter (1, TbTypes.CURSOR);
    call.execute ();
    ResultSet rset = (ResultSet)call.getObject(1);

    // Dump the cursor
    while (rset.next ())
{
System.out.println(rset.getString ("ID") + " - " + rset.getString ("NAME"));
}

    // Close all the resources
    rset.close();
    call.close();
    conn.close();
  }
}