Sample Applications

Sample Applications for Other Oracle Extensions

This section contains sample code for these Oracle extensions:

REF CURSOR Sample

Following is a complete sample program that uses JDBC to create a stored package in the data server and uses a get on the REF CURSOR type category to obtain the results of a query. For more information on REF CURSORs, see "Oracle REF CURSOR Type Category".

Except for some changes to the comments, the following sample is similar to the RefCursorExample.java program in the Demo/samples/oci8/object-samples directory.

import java.sql.*;                                           // line 1
import java.io.*;
import oracle.jdbc.driver.*;

class RefCursorExample
{
   public static void main(String args[]) throws SQLException
   {
      //Load the driver.
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      // Connect to the database.
      // You can put a database name after the @ sign in the connection URL.
      Connection conn = 
         DriverManager.getConnection("jdbc:oracle:oci8:@", "scott", "tiger");
                                                            // line 16
      // Create the stored procedure.
      init(conn);

      // Prepare a PL/SQL call.                                line 20
      CallableStatement call = 
         conn.prepareCall("{ ? = call java_refcursor.job_listing (?) }");

      // Find out who all the sales people are.                line 24
      call.registerOutParameter(1, OracleTypes.CURSOR);
      call.setString(2, "SALESMAN");
      call.execute();
      ResultSet rset = (ResultSet)call.getObject(1);

      // Output the information in the cursor.                 line 30
      while (rset.next())
         System.out.println(rset.getString("ENAME"));
   }

// Utility function to create the stored procedure
                                                            // line 36
   static void init(Connection conn) throws SQLException
   {
      Statement stmt = conn.createStatement();
                                                            // line 40
      stmt.execute("CREATE OR REPLACE PACKAGE java_refcursor AS " +
                   " type myrctype is ref cursor return EMP%ROWTYPE; " +
                   " function job_listing(j varchar2) return myrctype; " +
                   "end java_refcursor;");
                                                            // line 45
      stmt.execute("CREATE OR REPLACE PACKAGE BODY java_refcursor AS " +
                   " function job_listing(j varchar2) return myrctype is " +
                   "    rc myrctype; " +
                   " begin " +
                   "    open rc for select * from emp where job = j; " +
                   "    return rc; " +
                   " end; " +
                   "end java_cursor;");                     // line 53
   }
}

Lines 1-16:

Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user scott with password tiger. You can optionally enter a database name following the @ symbol.

Lines 18-29:

Prepare a callable statement to the job_listing function of the java_refcursor PL/SQL procedure. The callable statement returns a cursor to the rows of information where job=SALESMAN. Register OracleTypes.CURSOR as the output parameter. The setObject() method passes the value SALESMAN to the callable statement. After the callable statement is executed, the result set contains a cursor to the rows of the table where job=SALESMAN.

Lines 30-33:

Iterate through the result set and print the employee name part of the employee object.

Lines 40-45:

Define the package header for the java_refcursor package. The package header defines the return types and function signatures.

Lines 46-53:

Define the package body for the java_refcursor package. The package body defines the implementation which selects rows based on the value for job.

Array Sample

Following is a complete sample program that uses JDBC to create a table with a VARRAY. It inserts a new array object into the table, then prints the contents of the table. For more information on arrays, see "Working with Arrays".

Except for some changes to the comments, the following sample is similar to the ArrayExample.java program in the Demo/samples/oci8/object-samples directory.

import java.sql.*;                                           // line 1
import oracle.sql.*;
import oracle.jdbc.oracore.Util;
import oracle.jdbc.driver.*;
import java.math.BigDecimal;

public class ArrayExample
{
  public static void main (String args[])
    throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ symbol in 
    // the connection URL.
    //
    // The sample retrieves an varray of type "NUM_VARRAY" and
    // materializes the object as an object of type ARRAY.
    // A new ARRAY is then inserted into the database.

    // Please replace hostname, port_number and sid_name with
    // the appropriate values

    Connection conn =
      DriverManager.getConnection 
("jdbc:oracle:oci8:@(description=(address=(host=hostname)(protocol=tcp)(port=por
t_number))(connect_data=(sid=sid_name)))", "scott", "tiger");
   
    // It's faster when auto commit is off
    conn.setLines (false);                                  // line 32

    // Create a Statement
    Statement stmt = conn.createStatement ();               // line 35

    try
    {
      stmt.execute ("DROP TABLE varray_table");
      stmt.execute ("DROP TYPE num_varray");     
    }
    catch (SQLException e)
    {
      // the above drop statements will throw exceptions
      // if the types and tables did not exist before
    }                                                       // line 47
 
    stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)");
    stmt.execute ("CREATE TABLE varray_table (col1 num_varray)");
    stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");

    ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);                                     // line 54

    //now insert a new row

    // create a new ARRAY object    
    int elements[] = { 300, 400, 500, 600 };                // line 59
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn);
    ARRAY newArray = new ARRAY(desc, conn, elements);
                                                            // line 62
    PreparedStatement ps = 
      conn.prepareStatement ("INSERT INTO varray_table VALUES (?)");
    ((OraclePreparedStatement)ps).setARRAY (1, newArray);

    ps.execute ();

    rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);
  }                                                         // line 70

  public static void showResultSet (ResultSet rs)           // line 72
    throws SQLException
  {       
    int line = 0;
    while (rs.next())
    {
      line++;
      System.out.println("Row " + line + " : ");
      ARRAY array = ((OracleResultSet)rs).getARRAY (1);

      System.out.println ("Array is of type " + array.getSQLTypeName());
      System.out.println ("Array element is of type code  
                           " + array.getBaseType()); 
      System.out.println ("Array is of length " + array.length());
                                                                     // line 86
      // get Array elements            
      BigDecimal[] values = (BigDecimal[]) array.getArray();

      for (int i=0; i<values.length; i++) 
      {
        BigDecimal value = values[i];
        System.out.println(">> index " + i + " = " + value.intValue());
      }
    }
  }	
}                                                           // line 97

Lines 1-32:

Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. This example of getConnection() uses Net8 name-value pairs to specify the host as hostname, protocol as tcp, port as 1521, sid as orcl, user as scott and password as tiger.

Use setAutoCommit(false) to disable the AUTOCOMMIT feature and enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.

Lines 35-47:

Create a Statement object and delete any previously defined tables or types named varray_table or num_varray.

Lines 49-54:

Create the type num_varray as a varray containing NUMBER data. Create a 1-column table, varray_table, to contain the num_varray type data. Insert into the table two rows of data. The values 100 and 200 are both of type num_varray. Use the showResultSet() method (defined later in the program) to display information about the arrays contained in the table.

Lines 59-61:

First, define an array of integer elements to insert into the varray_table. Next, create an array descriptor object that will be used to create new ARRAY objects. To create an array descriptor object, pass the SQL type name of the array type (NUM_ARRAY) and the connection object to the createDescriptor() method. Then create the new array object by passing to it the array descriptor, the connection object, and the array of integer elements.

Lines 63-70:

Prepare a statement to insert the new array object into varray_table. Cast the prepared statement object to an OraclePreparedStatement object to take advantage of the setARRAY() method.

To retrieve the array contents of the table, write and execute a SQL SELECT statement. Again, use the showResultSet method (defined later in the program) to display information about the arrays contained in the table.

Lines 72-85:

Define the showResultSet() method. This method loops through a result set and returns information about the arrays it contains. This method uses the result set getARRAY() method to return an array into an oracle.sql.ARRAY object. To do this, cast the result set to an OracleResultSet object. Once you have the ARRAY object, you can apply Oracle extensions getSQLTypeName(), getBaseType(), as well as length(), to return and display the SQL type name of the array, the SQL type code of the array elements, and the array length.

Lines 87-97:

You can access the varray elements by using the ARRAY object's getArray() method. Since the varray contains SQL numbers, cast the result of getArray() to a java.math.BigDecimal array. Then, iterate through the value array and pull out individual elements.