Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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();
      }
    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index