Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section contains sample code for these Oracle extensions:
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
CURSOR
s, 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 } }
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.
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
.
Iterate through the result set and print the employee name part of the employee object.
Define the package header for the java_refcursor
package. The package header defines the return types and function signatures.
Define the package body for the java_refcursor
package. The package body defines the implementation which selects rows based on the value for job
.
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
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.
Create a Statement object and delete any previously defined tables or types named varray_table
or num_varray
.
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.
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.
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.
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.
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.