Oracle8i JDBC Developer's Guide and Reference
Release 8.1.5

A64685-01

Library

Product

Contents

Index

Prev  Chap Top Next

Sample Applications for JDBC 2.0-Compliant Oracle Extensions

This section contains sample code for the following Oracle extensions:

LOB Sample

This sample demonstrates basic support for LOBs in the OCI 8 driver. It illustrates how to create a table containing LOB columns, and includes utility programs to read from a LOB, write to a LOB, and dump the LOB contents. For more information on LOBs, see "Working with LOBs".

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

import java.sql.*;                                           // line 1
import java.io.*; 
import java.util.*; 
 
// Importing the Oracle Jdbc driver package 
// makes the code more readable 
import oracle.jdbc.driver.*; 
 
// Import this to get CLOB and BLOB classes 
import oracle.sql.*; 
 
public class NewLobExample1 
{ 
  public static void main (String args []) 
       throws Exception 
  { 
    // Load 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.
    Connection conn = 
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); 
 
    // It's faster when auto commit is off 
    conn.setAutoCommit (false);                             // line 26
     
    // Create a Statement 
    Statement stmt = conn.createStatement (); 
 
    try 
    { 
      stmt.execute ("DROP TABLE basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did
      // not exist already but we gleefully ignore it
    }                                                       // line 38
 
    // Create a table containing a BLOB and a CLOB             line 40
    stmt.execute ("CREATE TABLE basic_lob_table (x varchar2 (30), 
                   b blob, c clob)"); 
 
    // Populate the table 
    stmt.execute ("INSERT INTO basic_lob_table VALUES ('one', 
           '010101010101010101010101010101', 'onetwothreefour')"); 
    stmt.execute ("INSERT INTO basic_lob_table VALUES ('two', 
   '0202020202020202020202020202', 'twothreefourfivesix')"); 
                                                            // line 49
    System.out.println ("Dumping lobs"); 
 
    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("SELECT * FROM basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = ((OracleResultSet)rset).getBLOB (2); 
      CLOB clob = ((OracleResultSet)rset).getCLOB (3); 
 
      // Print the lob contents 
      dumpBlob (conn, blob); 
      dumpClob (conn, clob); 
 
      // Change the lob contents 
      fillClob (conn, clob, 2000); 
      fillBlob (conn, blob, 4000); 
    } 
                                                            // line 68
    System.out.println ("Dumping lobs again"); 
 
    rset = stmt.executeQuery ("SELECT * FROM basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = ((OracleResultSet)rset).getBLOB (2); 
      CLOB clob = ((OracleResultSet)rset).getCLOB (3); 
 
      // Print the lobs contents 
      dumpBlob (conn, blob); 
      dumpClob (conn, clob); 
    } 
  }                                                         // line 82
 
  // Utility function to dump Clob contents 
  static void dumpClob (Connection conn, CLOB clob) 
    throws Exception 
  { 
    // get character stream to retrieve clob data 
    Reader instream = clob.getCharacterStream(); 
 
    // create temporary buffer for read                        line 91
    char[] buffer = new char[10]; 
 
    // length of characters read 
    int length = 0; 
 
    // fetch data                                              line 98
    while ((length = instream.read(buffer)) != -1) 
    { 
      System.out.print("Read " + length + " chars: "); 
 
      for (int i=0; i<length; i++) 
        System.out.print(buffer[i]); 
      System.out.println(); 
    } 
 
    // Close input stream 
    instream.close(); 
  }                                                        // line 108
 
  // Utility function to dump Blob contents 
  static void dumpBlob (Connection conn, BLOB blob) 
    throws Exception 
  { 
    // Get binary output stream to retrieve blob data 
    InputStream instream = blob.getBinaryStream(); 
 
    // Create temporary buffer for read 
    byte[] buffer = new byte[10]; 
 
    // length of bytes read                                   line 120
    int length = 0; 
 
    // 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(); 
  } 
                                                           // line 135
  // Utility function to put data in a Clob 
  static void fillClob (Connection conn, CLOB clob, long length) 
    throws Exception 
  { 
    Writer outstream = clob.getCharacterOutputStream(); 
 
    int i = 0; 
    int chunk = 10; 
 
    while (i < length) 
    { 
      outstream.write(i + "hello world", 0, chunk);        // line 147
 
      i += chunk; 
      if (length - i < chunk) 
          chunk = (int) length - i; 
    } 
    outstream.close(); 
  }                                                                 // line 154
 
  // Utility function to write data to a Blob 
  static void fillBlob (Connection conn, BLOB blob, long length) 
    throws Exception 
  { 
    OutputStream outstream = blob.getBinaryOutputStream(); 
 
    int i = 0; 
 
    byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };               // line 165
   int chunk = data.length; 
 
    while (i < length) 
    { 
      data [0] = (byte)i; 
      outstream.write(data, 0, chunk); 
 
      i += chunk; 
      if (length - i < chunk) 
          chunk = (int) length - i; 
    } 
    outstream.close(); 
  } 
}                                                                   // line 175
Lines 1-26:

Import the necessary java.* and oracle.* classes. Register the driver with the DriverManager.registerDriver() method and connect to the database with DriverManager.getConnection(). 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.

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 27-38:

Create a statement object. Drop any pre-existing table named basic_lob_table. Then, create a new basic_lob_table directory to store the LOBs in-line.

Lines 40-49:

Use SQL statements to create a table with three columns: a column to store the row number as a VARCHAR2, a BLOB column, and a CLOB column. Then insert data into two rows of the table.

Lines 50-68:

SELECT the contents of the table into a result set.

Retrieve the LOBs. The getBLOB() and getCLOB() methods return locators to the LOB data; to retrieve the LOB contents, you must write additional code (which is defined later in this program). To use the getBLOB() and getCLOB() methods, cast the result set to an OracleResultSet object. Then call the "dump" functions to display the contents of the LOBs, and the "fill" functions to change the contents of the LOBs. The dump and fill functions are defined later in this program.

Lines 69-82:

Display the LOBs again, after their contents have been changed. SELECT the contents of the table into a result set, and then apply the dump functions. The dump functions are defined later in this program.

Lines 84-108:

Define the utility function dumpClob to display the contents of a CLOB. Read the CLOB contents as a character stream. Use the getCharacterStream() method to get a READER stream object. Set up the temporary character array to read the character data in 10-character chunks.

Set up a loop to read and display the contents of the CLOB. The length of the CLOB is displayed as well. Close the input stream when you are done.

Lines 110-135:

Define the utility function dumpBlob to display the contents of a BLOB. Read the BLOB contents as a binary stream. Use the getBinaryStream() method to get an InputStream stream object. Set up the temporary byte array to read the binary data in 10-byte chunks.

Set up a loop to read and display the contents of the BLOB. The length of the BLOB is displayed as well. Close the input stream when you are done.

Lines 136-154:

Define the utility function fillClob to write data to a CLOB. The fillClob function needs the CLOB locator and the length of the CLOB. To write to the CLOB, use the getCharacterOutputStream() method to get a WRITER object.

Set up a loop to write an index value and part of the string Hello World to the CLOB. Close the WRITER stream when you are done.

Lines 156-175:

Define the utility function fillBlob to write data to a BLOB. The fillBlob function needs the BLOB locator and the length of the BLOB. To write to the BLOB, use the getBinaryOutputStream() method to get an OutputStream object.

Define the byte array of data that you want to write to the BLOB. The while loop causes a variation of the data to be written to the BLOB. Close the OutputStream object when you are done.

BFILE Sample

This sample demonstrates basic BFILE support in the OCI 8 driver. It illustrates filling a table with BFILEs and includes a utility for dumping the contents of a BFILE. For more information on BFILEs, see "Working with LOBs".

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

import java.sql.*;                                           // line 1
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.*; 
                                                            // line 10
public class NewFileExample1 
{ 
  public static void main (String args []) 
       throws Exception 
  { 
    // Load the Oracle JDBC driver                             line 16
    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 example creates a DIRECTORY and you have to be connected as 
    // "system" to be able to run the test. 
    // If 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); 
                                                                     // line 32
    // Create a Statement 
    Statement stmt = conn.createStatement (); 
 
    try                                                     // line 36
    { 
      stmt.execute ("DROP DIRECTORY TEST_DIR"); 
    } 
    catch (SQLException e) 
    { 
      // An error is raised if the directory does not exist.  Just ignore it. 
    }                                                       // line 43
    stmt.execute ("CREATE DIRECTORY TEST_DIR AS '/temp/filetest'"); 
 
    try                                                     // line 46
    { 
      stmt.execute ("drop table test_dir_table"); 
    } 
    catch (SQLException e) 
    { 
      // An error is raised if the table does not exist.  Just ignore it. 
    } 
                                                            // line 54
    // 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                       // line 64
    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); 
    } 
  }                                                          //line 75
 
  // Utility function to dump the contents of a Bfile          line 77
  static void dumpBfile (Connection conn, BFILE bfile) 
    throws Exception 
  {                                                         // line 80
    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: ");                  // line 84
 
    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(); 
    }                                                      // line 108
 
    // Close input stream 
    instream.close(); 
 
    // close file handler 
    bfile.closeFile(); 
  }                                                        // line 115
}
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. Use the database URL jdbc:oracle:oci8:@ and connect as user system with password manager. You can optionally enter a database name following the @ symbol.

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 33-44:

Create a statement object. Drop any pre-existing directory named TEST_DIR. Then, create a new TEST_DIR directory to store the BFILE. You or your System Administrator can use whatever file name you wish.

Lines 46-53:

Drop any pre-existing table named test_dir_table.

Lines 55-63:

Create and populate a table with files. Use SQL statements to create a table, test_dir_table, with two columns: one column to indicate the row number as a VARCHAR2 (for example, "one" or "two"), and one column to hold the BFILE locator.

Use SQL statements to insert some data into the table. For the first row, insert a row number in the first column, and use the BFILENAME keyword to insert a BFILE, file1, located in TEST_DIR, in the second column. Do the same thing for the second row.

Lines 64-75:

SELECT the contents of the table into a result set. Set up a loop to retrieve the contents of the table. Use getString() to retrieve the row number data, and use getBFILE() to retrieve the BFILE locator. Since BFILE is an Oracle-specific datatype, and getBFILE() is an Oracle extension, cast the result set object to an OracleResultSet object.

Use the dumpBfile() method (defined later in the program) to display the BFILE contents and various statistics about the BFILE.

Line 77:

Define the dumpBfile() method to display the BFILE contents and various statistics about the BFILE. The dumpBfile() method takes the BFILE locator as input.

Lines 80-83:

Use the getName(), fileExists(), and isFileOpen() methods to return the name of the BFILE, and whether the BFILE exists and is open. Note that the BFILE does not have to be open to apply these methods to it.

Lines 84-108:

Read and display the BFILE contents. First open the BFILE. You can read the BFILE contents as a binary stream. Use the getBinaryStream() method to get an input stream object. Determine the size of the "chunk" in which the stream will read the BFILE data, and set up the temporary byte array to store the data.

Set up a loop to read and display the contents of the BFILE. The length of the BFILE is displayed as well.

Lines 110-115:

When you are finished, close the input stream and the BFILE.




Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index