Sample demonstrating usage of REFCURSOR

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This sample shows how to retrieve and process a REFCURSOR using JDBC. The sample creates a table("OTN_AIRLINES") and a PLSQL function that returns a REFCURSOR. This PLSQL function is called from the sample, and the REFCURSOR returned by the function is processed using JDBC classes and methods.This sample uses the JDBC-Thin driver; the code is the same for JDBC-OCI, except for the database URL syntax.

Working of the Sample

The sample application uses a database table OTN_AIRLINES. When the application is invoked, the table is created if it does not exist. It also creates a PLSQL package called DEMO_REFCURSOR, having a function called getRefCursor that returns a REFCURSOR. The status of the connection is shown in the status bar. Errors if any, are shown in the status bar.

When the user clicks on the "Get first 5 rows" button, the application makes a call to the DEMO_REFCURSOR.getRefCursor PLSQL function, and the REFCURSOR returned by the function is obtained. The REFCURSOR is then accessed and the first 5 records of the REFCURSOR are displayed in the table area.

When the user clicks on the "Get Next 5 rows" button, the next 5 records from the REFCURSOR are displayed in the Table area.

Here is the code usage for obtaining a REFCURSOR. You can find more details of the code in RefcursorSample.java file under src/oracle/otnsamples/jdbc/refcursor folder. Look into Description of Sample Files section for folder and file details.

private void getFirstFive() { ....................... ....................... // Prepare a Callable Statement, to make a call to the PLSQL function // demo_refcursor.getRefCursor, and obtain the returned REFCURSOR CallableStatement stmt = connection.prepareCall("{? = call demo_refcursor.getRefCursor }"); // Set the Out Parameter type to be of type CURSOR stmt.registerOutParameter(1, OracleTypes.CURSOR); stmt.execute(); // Execute the statement // Cast the returned parameter, (defined as type, OracleTypes.CURSOR) // to a JDBC result-set. resultSet = (ResultSet)stmt.getObject(1); ...................... ......................

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above This can be downloaded from here .
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here .
• Oracle9i JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided RefcursorSample.jar using the following command 

> jar xvf RefcursorSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder RefCursorSample with all the source files

• Edit RefcursorSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to your own database.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the RefcursorSample.jws from the RefcursorSample directory.
• Next, select Project/Make RefcursorSample.jpr from main menu.
• Now, select Run/Run RefcursorSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from RefcursorSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\RefcursorSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\RefcursorSample> set JAVA_HOME=d:\jdk1.3.1
D:\RefcursorSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar
• Also add RefcursorSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\RefcursorSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\RefcursorSample;.
  
• From the directory RefcursorSample\src\oracle\otnsamples\jdbc\refcursor, compile all the java files using javac:
  Example: 
  D:\RefcursorSample\src\oracle\otnsamples\jdbc\refcursor>javac -d . *.java
  
• Run the class file using java from  RefcursorSample\src\oracle\otnsamples\jdbc\refcursor directory
  Example:
  D:\RefcursorSample\src\oracle\otnsamples\jdbc\refcursor>
  java oracle.otnsamples.jdbc.refcursor.RefcursorSample

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from RefcursorSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to RefcursorSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar
• RefcursorSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/RefcursorSample:.
• From the directory RefcursorSample/src/oracle/otnsamples/jdbc/refcursor, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same RefcursorSample/src/oracle/otnsamples/jdbc/refcursor directory.
  Example:
  $java oracle.otnsamples.jdbc.refcursor.RefcursorSample