Samples for Oracle Type Extensions

This section contains sample code for some of the Oracle type extensions:

• REF CURSORs--RefCursorExample.java

• BFILEs--FileExample.java

The REF CURSOR sample is located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

The BFILE example is in the object-samples directory.

REF CURSORs--RefCursorExample.java

This sample program shows Oracle JDBC REF CURSOR functionality, creating a PL/SQL package that includes a stored function that returns a REF CURSOR type. The sample retrieves the REF CURSOR into a result set object. For information on REF CURSORs, see "Oracle REF CURSOR Type Category".

/*
 * This sample shows how to call a PL/SQL function that opens
 * a cursor and get the cursor back as a Java ResultSet.
 */

import java.sql.*;
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");

    // Create the stored procedure
    init (conn);

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

    // Find out all the SALES person
    call.registerOutParameter (1, OracleTypes.CURSOR);
    call.setString (2, "SALESMAN");
    call.execute ();
    ResultSet rset = (ResultSet)call.getObject (1);

    // Dump the cursor
    while (rset.next ())
      System.out.println (rset.getString ("ENAME"));

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

  }

  // Utility function to create the stored procedure
  static void init (Connection conn)
       throws SQLException
  {
    Statement stmt = conn.createStatement ();

    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;");

    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_refcursor;");
    stmt.close();
  }
}

BFILEs--FileExample.java

This sample demonstrates Oracle JDBC BFILE support. It illustrates filling a table with BFILEs and includes a utility for dumping the contents of a BFILE. For information on BFILEs, see "Working with BFILEs".

/* 
 * This sample demonstrate basic File support
 */

import java.sql.*;
import java.io.*;
import java.util.*;

//including this import makes the code easier to read
import oracle.jdbc.driver.*;

// needed for new BFILE class
import oracle.sql.*;

public class FileExample
{
  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 can put a database name after the @ sign in the connection URL.
    //
    // The sample creates a DIRECTORY and you have to be connected as
    // "system" to be able to run the test.
    // I you can't connect as "system" have your system manager
    // create the directory for you, grant you the rights to it, and
    // remove the portion of this program that drops and creates the directory.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "system", "manager");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

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

    try
    {
      stmt.execute ("drop directory TEST_DIR");
    }
    catch (SQLException e)
    {
      // An error is raised if the directory does not exist.  Just ignore it.
    }
    stmt.execute ("create directory TEST_DIR as '/tmp/filetest'");

    try
    {
      stmt.execute ("drop table test_dir_table");
    }
    catch (SQLException e)
    {
      // An error is raised if the table does not exist.  Just ignore it.
    }

    // Create and populate a table with files
    // The files file1 and file2 must exist in the directory TEST_DIR created
    // above as symbolic name for /private/local/filetest.
    stmt.execute ("create table test_dir_table (x varchar2 (30), b bfile)");
    stmt.execute ("insert into test_dir_table values 
                   ('one', bfilename ('TEST_DIR', 'file1'))");
    stmt.execute ("insert into test_dir_table values 
                   ('two', bfilename ('TEST_DIR', 'file2'))");

    // Select the file from the table
    ResultSet rset = stmt.executeQuery ("select * from test_dir_table");
    while (rset.next ())
    {
      String x = rset.getString (1);
      BFILE bfile = ((OracleResultSet)rset).getBFILE (2);
      System.out.println (x + " " + bfile);

      // Dump the file contents
      dumpBfile (conn, bfile);
    }

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

  // Utility function to dump the contents of a Bfile
  static void dumpBfile (Connection conn, BFILE bfile)
    throws Exception
  {
    System.out.println ("Dumping file " + bfile.getName());
    System.out.println ("File exists: " + bfile.fileExists());
    System.out.println ("File open: " + bfile.isFileOpen());

    System.out.println ("Opening File: ");

    bfile.openFile();

    System.out.println ("File open: " + bfile.isFileOpen());

    long length = bfile.length();
    System.out.println ("File length: " + length);

    int chunk = 10;

    InputStream instream = bfile.getBinaryStream();

    // Create temporary buffer for read
    byte[] buffer = new byte[chunk];

    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " bytes: ");

      for (int i=0; i<length; i++)
        System.out.print(buffer[i]+" ");
      System.out.println();
    }

    // Close input stream
    instream.close();
 
    // close file handler
    bfile.closeFile();
  }
}

---