Hashtable env = new Hashtable (5);
env.put (Context.INITIAL_CONTEXT_FACTORY,
          "com.sun.jndi.fscontext.RefFSContextFactory");
env.put (Context.PROVIDER_URL, "file:C:TempJNDI" );
// use the url supplied in command line or the
// following default
String url = "jdbc:oracle:oci8:@";
// import the Oracle database 10g JDBC pool package
import oracle.jdbc.pool.*;

OracleDataSource ods = new OracleDataSource();

/*
 * Explicit parameters setting. It is also possible, with
 * JDKs, to load
 * parameter values from a file or XML document.
 */
 ods.setURL(url);
 ods.setUser("Scott");
 ods.setPassword("tiger");

/*
 * Create an InitialContex object and bind the logical name
 * "MyDS" with
 * the previously created Oracle dataSource
 */
Context ctx = new IntialContext(env);
ctx.bind("MyDS", ods);

ods =(OracleDataSource) ctx. lookup("MyDS");  // lookup
DataSource

Connection conn = ods.getConnection();

datasoure.properties
====================
url=jdbc:oracle:thin:scott/tiger@//localhost:1521/orcl

ods.setConnectionCachingEnabled(True);

public void setConnectionCacheProperties(java.util.Properties
cp)
     throws java.sql.SQLException

ods.setConnectionCacheName("MyCache"); // optional
ods.setConnectionCacheProperties(cp);  // optional
ctx.bind("MyDS", ods);
ods =(OracleDataSource) ctx.lookup("MyDS");  // lookup
// DataSource

// create a database session (physical connection)
   conn = ods.getConnection();

// look up the datasource object from the Context
   OracleDataSource ds = (OracleDataSource) ctx.lookup("SalesDB");
   java.util.Properties prop = new java.util.Properties ();
// setting the cache size to at least 2
   prop.setProperty("MinLimit", "2");

// create the cache and populate with 4 connections
   prop.setProperty("InitialLimit", "4");
   prop.setProperty("MaxStatementsLimit", "1");
   prop.setProperty("InactivityTimeout", "10");    // 10 seconds
   prop.setProperty("PropertyCheckInterval", "5"); // 5 seconds

// Setting cache properties on the datasource object
   ds.setConnectionCacheProperties (prop);

// 1st  invocation of getConnection creates the
// Cache with 4 connections
   Connection conn = ds.getConnection();

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

...

// Do some work

...

// Close the Statement
    stmt.close();
    stmt = null;

// a connection that matches with NLS_LANG attribute
   java.util.Properties connAttr = null;
   connAttr.setProperty("NLS_LANG", "ISO-LATIN-1");
   conn = ds.getConnection(connAttr);
...

// retrieve a connection that matches Transaction Isolation
   java.util.Properties connAttr = null;
   connAttr.setProperty("TRANSACTION_ISOLATION",
"SERIALIZABLE");
   conn = ds.getConnection(connAttr);
...
   conn.close(connAttr); // save settings for this connection

...
java.util.Properties connAttr = null;
connAttr.setProperty("CONNECTION_TAG", "Rita'S_CONNECTION");
conn = ds.getConnection(connAttr); // retrieve Rita's
// connection
conn.close(connAttr); // Save connection_tag  for future
// retrieval
conn = ds.getConnection(connAttr); // retrieve Rita's
// connection

// Connection request
   java.util.properties connAttr = null;
   connAttr.setProperty("NLSLANG", "ISO-LATIN-1");
   conn = ds.getConnection(connAttr); // request connection
   java.util.properties unmatchedAttr =
                conn.getUnMatchedConnectionAttributes();
...
//Apply unmatched attributes before using the connection.
...

// Save the attributes settings
conn.close(connAttr);

String url =
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTO
COL=tcp)(HOST=<host>)(PORT=<port>)))(CONNECT_DATA=(SID=<sid>)
))";
DriverPropertyInfo[] dpi =
              new OracleDriver().getPropertyInfo(url,new
Properties());
for (int i=0; i<dpi.length; i++) {
 String dp_name = dpi[i].name; // get the name of property
 String value = dpi[i].value; // get the current value
  String dp_desc = dpi[i].description; // get the description
}

package oracle.jdbc.driver;
public interface OracleConnectionCacheCallback
{
    public boolean handleAbandonedConnection(OracleConnection conn,
                                                  Object userObject);
    public void releaseConnection(OracleConnection conn,
                                                  Object userObject);
}

// Callback implementation Skeleton
public class UserConnectionCacheCallback implements
OracleConnectionCacheCallback
{
     public boolean handleAbandonedConnection(OracleConnection conn,
        Object userObject)
     {
       ... implementation ...
     }

     public void releaseConnection(OracleConnection conn, Object
 userObject)
    {
         ... implementation ...
    }
}

handleAbandonedConnection

public void setConnectionReleasePriority(int priority) throws
java.sql.SQLException;

where priority takes one of the following value
    CONNECTION_RELEASE_LOCKED
    CONNECTION_RELEASE_LOW
    CONNECTION_RELEASE_HIGH

public int getReleasePriority();

registerConnectionCacheCallback

registerConnectionCacheCallback

registerConnectionCacheCallback(OracleConnectionCacheCallback
                  callbackImpl,  Object userObj, int callbackFlag)
-- callbackImpl user's implementation of the
                        OracleConnectionCacheCallback interface.
-- userObj any user object which is passed back, unmodified, when
                        the callback method is invoked.
-- callbackFlag  specifies which callback method should be invoked.
   It must be one of the following values:
   OracleConnection.ABANDONED_CONNECTION_CALLBACK - calls only
                   the  handleAbandonedConnection() method
   OracleConnection.RELEASE_CONNECTION_CALLBACK - calls only
                    the releaseConnection() method (see below)
   OracleConnection.ALL_CALLBACKS - calls all the connection cache
                    callback methods

((OracleConnection)conn).registerConnectionCacheCallback( new
UserConnectionCacheCallback(), new <userObject>,
                          OracleConnection.ALL_CALLBACKS);

conn.registerConnectionCacheCallback(new
UserConnectionCacheCallback(),
              this,  OracleConnection.ABANDONED_CONNECTION_CALLBACK);

public void cachecallback(String dsName)
{
  // Look up the datasource object
  OracleDataSource ds = (OracleDataSource) ctx.lookup(dsName);

  // Set Cache properties
  java.util.Properties prop = new java.util.Properties();
  prop.setProperty("AbandonedConnectionTimeout", "4"); // 4 seconds
  prop.setProperty("PropertyCheckInterval", "1");

  // Apply the new properties to test the callback mechanism
  ds.setConnectionCacheProperties(prop);

  // get a connection from the cache
  OracleConnection conn = (OracleConnection) ds.getConnection();
  ds.getConnectionCacheProperties());

  public boolean abandonedFlag = false;
  public boolean ConnOk = false;

  conn.registerConnectionCacheCallback(new
UserConnectionCacheCallback(),
                  this,
OracleConnection.ABANDONED_CONNECTION_CALLBACK);

  System.out.println("sleep 8 sec to kick-in
AbandoonedConnectionTimeout");
  Thread.sleep(8000);
  try {
       Statement stmt = conn.createStatement();
       stmt.executeQuery("Select USER from dual");
       System.out.println("The connection is not abandoned");
       stmt.close();
  } catch (Exception e){
       System.out.println("The connection is abandoned");
       System.out.println(e.getMessage());
   }
}
class UserConnectionCacheCallback implements
                          OracleConnectionCacheCallback
{
   public boolean handleAbandonedConnection(OracleConnection conn,
                                                Object userObject)
   {
Statement stmt = null;
cachecallback snipet = (cachecallback) userObject;
snipet.abandonedFlag = true;
try {
stmt = conn.createStatement();
stmt.executeQuery("Select USER from dual");
stmt.close();
snipet.ConnOk = true;
       } catch (SQLException e) {
       snipet.ConnOk = false;
snipet.System.out.println("SQLException: " + e.getMessage());
       }

   if (snipet.abandonedFlag == true) {
    try {
conn.close();
   } catch (SQLException e) {
snipet.logErr(e);
   }
return true;
   } else
return false;

   } // end handleAbandonedConnection


    public void releaseConnection(OracleConnection conn,
                                           Object userObject)
    {
    cachecallback snipet = (cachecallback) userObject;
    String prioritySetting = "";
    prioritySetting = snipet.getPriority(conn);
    snipet.System.out.println("priority of connection is: " +
                                                 prioritySetting);
    try {
 snipet.System.out.println("Release connection with close()");
conn.close();
   } catch (SQLException e) {
snipet.logErr(e);
   }

  snipet.System.out.println("Exiting releaseConnection()");
   } // end releaseConnection

 }// end UserConnectionCacheCallback

}// end cachecallback

$ onsctl ping
ONS Is Running

racgons.bin add_config <hostname1>:<port> [<hostname2>:<port>
...]

$ORACLE_HOME/opmn/conf/ons.config
localport=6100 # This is the port ONS is writing to
remoteport=6200 # This is the port ONS is listening on
loglevel=3
# This is the list of hosts and ports ONS is poting to.
# Include RAC and client nodes.
nodes=node-1.country.company.com:6200,node-
2.country.company.com:6200,
node-3.country.company.com:6200,node-4.country.company.com:6200,
node6-1.country.company.com:6200

$ mv $ORACLE_HOME/opmn/conf $ORACLE_HOME/opmn/conf.orig
$ mkdir $HOME/opmn/conf
$ cp ORACLE_HOME/opmn/conf.orig/* $HOME/opmn/conf
$ ln –s $HOME/opmn/conf $ORACLE_HOME/opmn/conf

localport=6100    # This is the port ONS is writing to
remoteport=6200   # This is the port ONS is listening on
loglevel=3
useocr=on $ populate OCR

ORACLE_HOME/bin/onsctl start|stop|ping|reconfig|debug|help|detailed

/*
 * Copyright (c) 2001, 2004 by Oracle. All Rights Reserved
 * ONC Subscription client. This client listens for all events ONS receives
 * Based on the event type decisions are made on how and whether to print the
 * event body
 */

import oracle.ons.*;
import java.util.*;
import java.io.*;
import java.nio.*;

public class onc_subscriber
{

  public static void main(String args[])
  {
    boolean debug = false;
    // Set ONC-required System property for oracle.ons.oraclehome:
    //System.setProperty("oracle.ons.oraclehome", "<your ons home>");

    //Subscriber s = new Subscriber(""oraDb/myProdCluster"",
     "database/event/*");
    //Subscriber s = new Subscriber("", "database/event/*");
    Subscriber s = new Subscriber("", ""); // subscribe to all events
    Notification e;

    System.out.println("ONC subscriber starting");

    boolean shutdown = false;
    while (!shutdown)
    {
       e = s.receive(true);  // blocking wait for notification receive

       System.out.println( "** HA event received -- Printing header:" );
       e.print();
       System.out.println( "** Body length = " + e.body().length);
       System.out.println( "** Event type = " + e.type());

       /* Test the event type to attempt to determine the event body format.
          Database events generated by the racg code are "free-format" events -
          the event body is a string. It consists of space delimited key=value
          pairs. Events constructed using the ONC Java API have an event body
          that is a byte array (byte []) which have a terminating character.
          The Java API call to read the body waits on this character to
          terminate the read.
          Attempting to read a free-form event using the Java API can cause the
          reading thread to hang waiting on the byte array terminator.

          The following test only looks for database events, and events
          generated by the program onc_publisher.java. Other events will be

          received, but their bodies will not be displayed.
       */

        if (e.type().startsWith("database")) {
           if (debug) { System.out.println( "New print out"); }
           evtPrint myEvtPrint = new evtPrint(e);
        } else if (e.type().startsWith("javaAPI")){
           oncPrint myPrint = new oncPrint(e);
        } else {
          System.out.println("Unknown event type. Not displaying body");
        }

        try
        {
          if (e.type().equals("onc/shutdown")) {
             System.out.println("Shutdown event received.");
             shutdown = true;
          }
          else {
             java.lang.Thread.currentThread().sleep(100);
             System.out.println("Sleep and retry.");
          }
        }
        catch (Exception te)
        {
          te.printStackTrace();
        }
    }

    s.close();

    System.out.println(" ONC subscriber exiting!");
  }
}

JHOME=/net/myclient/private/myuser/java/SUNWappserver/jdk/
export JHOME
ORACLE_HOME=/myclient/myuser/product/oracle/10gClient
export ORACLE_HOME

$ Jmake_onc sub -c
>> Recompiling onc_subscriber ...
>> Executing onc_subscriber ...
ONC subscriber starting

** HA event received -- Printing body:
Notification Type:         database/event/service
Affected Components:       null
Affected Nodes:            null
Delivery Time:             1095793164948
Generating Component:      oraDb/myProdCluster
Generating Node:           dlsun1344.us.oracle.com
Generating Process:        1095793164651
Notification ID:           109579316465111
Notification Creation Time:1095793164766
Cluster ID:                databaseClusterId
Cluster Name:              databaseClusterName
Instance ID:               databaseInstanceId
Instance Name:             databaseInstanceName
Local Only Flag:           FALSE
Cluster Only Flag:         FALSE
Body:                      [B@1888759

// In this code snippet, OracleDataSource are bound to JNDI
// and cache properties are set on the datasource.

   import oracle.jdbc.pool.*;
   Context ctx = new IntialContext(ht);
   OracleDataSource ods = new OracleDataSource();

// Set Datasource properties
   ods.setUser("Scott");
   ods.setPassword("tiger");
   ods.setConnectionCacheName("MyCache");
   ods.setConnectionCacheProperties(cp);
   ods.setURL("jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on)
  (CONNECT_DATA=(SERVICE_NAME=SalesDS)))");

// Enable Connection Caching First
   ods.setConnectionCachingEnabled(True);

// Enable Fast Connection Failover programmatically
   ods.setFastConnectionFailoverEnabled(true);
   ctx.bind("MyDS", ods);

...
// lookup datasource
   ds = lookup("MyDS");

// Create a Failover Enabled Connection Cache
   conn = ds.getConnection();

// do some work...

// return the connection to the cache
   conn.close();
...

// close datasource and cleanup the cache
   ods.close()

java -D oracle.jdbc.FastConnectionFailover=true

// declare datasource
ods.setUrl("jdbc:oracle:oci:@(DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias)
   (PORT=1521))
   (CONNECT_DATA=(SERVICE_NAME=SalesDB)))");

ods.setUser("scott");
// Enable Implicit Connection Cache
ods.setConnectionCachingEnabled(true);
// Enable and Fast Connection FailOver
ods.setFastConnectionFailoverEnabled(true):
//  Bind and lookup the data source
ctx.bind("myDS",ods);
ds =(OracleDataSource) ctx.lookup("MyDS");
// The first getConnection will create the cache:
try {
     ods.getConnection();
     // do some work
} catch (SQLException e) {
    handleSQLException(e);
}
...
handleSQLException (SQLException e)
{
  if (connectionCacheManager.isFatalConnectionError(e))
      ConnRetry = true;  // Fatal Connection error detected
}

FCFConnectionTest.java
======================

/*
 * FCFConnectionTest
 * Adapted from JDBC code sample with additional contributions
 *
 */
package FCFTest;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Properties;
import oracle.jdbc.pool.OracleDataSource;

public class Main  {
  public Main() {
    try {
      OracleDataSource ods = new OracleDataSource();
      String url = "jdbc:oracle:thin:@" +

"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sturachh1vip)(PORT=1521))"
+
      "(ADDRESS=(PROTOCOL=TCP)(HOST=sturachh2vip)(PORT=1521))" +
      "(LOAD_BALANCE=yes)" +
      "(CONNECT_DATA=(SERVICE_NAME=FCF)))";

      Boolean retryConnection = false;

      Properties prop = new Properties();
      prop.setProperty("MinLimit", "100");
      prop.setProperty("MaxLimit", "200");
      prop.setProperty("InitialLimit", "100");

      ods.setURL(url);
      ods.setUser("SCOTT");
      ods.setPassword("TIGER");
      ods.setConnectionCachingEnabled(true);

      ods.setConnectionCacheProperties(prop);
      ods.setFastConnectionFailoverEnabled(true);
      ods.setConnectionCacheName("ImplicitConnectionCache");

      SimpleDateFormat formatTime =
      (SimpleDateFormat)DateFormat.getTimeInstance(DateFormat.LONG);
      formatTime.applyPattern("dd.MM.yy HH:mm:ss");
      Connection conn = null;
      Statement stmt = null;
      ResultSet rset = null;
      long curTime = 0;
      long startTime = 0;

      for (int i=0; i<50; i++) {
        try {
          startTime = System.currentTimeMillis();
          System.out.println(formatTime.format(new Long(startTime))+
             ": Connecting with FCF Enabled" );
          if (retryConnection)
          {
           // This is a retry;
           // You migh want to do additional processing beforehand
            System.out.println ("getConnection retry");
           }
           conn = ods.getConnection();
           stmt = conn.createStatement();
           rset = stmt.executeQuery("select * from v$instance");
           while (rset.next()) {
             curTime = System.currentTimeMillis();
           System.out.println(formatTime.format(new Long(curTime))+":
 " +
             rset.getInt("INSTANCE_NUMBER") + " " +
 rset.getString("HOST_NAME"));
           }
           rset.close();
           stmt.close();
           conn.close();
           curTime = System.currentTimeMillis();
           System.out.println(formatTime.format(new Long(curTime))+
               ": Connection Closed");
           byte[] input = new byte[10];
           System.out.println("Enter to continue");

          System.in.read(input);
        } catch (SQLException sqle) {
            handleSE(sqle);
      }
    } catch (Exception e) {
      System.out.println("Caught an Exception" + e.getMessage());
      e.printStackTrace();
    }
  }

handleSE (SQLException e)
{
   e.getMessage();
   e.printStackTrace();
   if (connectionCacheManager.isFatalConnectionError(e)) {
      System.out.println("Caught a Fatal SQLException, must retry");
      retryConnection = true;  // Retry getConnection()
      Thread.sleep(30000);      // Wait few seconds before retry
}

/**
  *
  * @param args
  */
  public static void main(String[] args) {
    Main main = new Main();
  }
}

// register TAF callback function "cbk"
   ((OracleConnection) conn).registerTAFCallback(cbk,
msg);

primary=(DESCRIPTION=
           (ADDRESS=(PROTOCOL=tcp)(Host=hostname)(Port=1521))
              (CONNECT_DATA=(SERVICE_NAME=payrol)
                (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))

String url =
"jdbc:oracle:oci8:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=

"myhosy")(PORT="5521"))(CONNECT_DATA=(SID="ORCL")(FAILOVER_MO
DE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=10000)(DELAY=2))))";

CallBack  callbck = new CallBack();
...
// register TAF callback function
    ((OracleConnection) conn).registerTAFCallback(callbck,
msg);

public interface OracleOCIFailover{

// Possible Failover Types
public static final int FO_SESSION = 1;
public static final int FO_SELECT  = 2;

public static final int FO_NONE  = 3;
public static final int;

// Possible Failover events registered with callback
public static final int FO_BEGIN   = 1;
public static final int FO_END     = 2;
public static final int FO_ABORT   = 3;
public static final int FO_REAUTH  = 4;
public static final int FO_ERROR  = 5;
public static final int FO_RETRY  = 6;
public static final int FO_EVENT_UNKNOWN = 7;

public int callbackFn (Connection conn,
                      Object ctxt, // Anything the user wants to save
                      int type, // One of the possible Failover Types
                       int event ); // One of the possible Failover
                                 // Events

class CallBack implements OracleOCIFailover {
  public int callbackFn (Connection conn, Object ctxt, int type, int
event) {

    String failover_type = null;

    switch (type) {
      case FO_SESSION:
           failover_type = "SESSION";
           break;
      case FO_SELECT:
           failover_type = "SELECT";
           break;
      default:
           failover_type = "NONE";
    }

    switch (event) {

      case FO_BEGIN:
           testCase.begin_called++;
           break;
      case FO_END:
           testCase.end_called++;

            break;
       case FO_ABORT:
            testCase.abort_called++;
            break;
      case  FO_REAUTH:
            testCase.reauth_called++;
            break;
       case FO_ERROR:
            testCase.error_called++;
            // Sleep for a while
            try {
              Thread.sleep(100);
            }
            catch (InterruptedException e) {
              System.out.println ("Thread.sleep Issue: " +
 e.toString());
            }
            return FO_RETRY;
       default:
            System.out.println (ctxt + ": Unknown failover event.");
            break;

    }
.}

public void openProxySession (int type,
java.util.Properties prop)
                      throws java.sql.SQLException

- OracleConnection.PROXYTYPE_USER_NAME  ->
   a java.lang.String object PROXY_USER_NAME needs to be set in prop.

- OracleConnection.PROXYTYPE_DISTINGUISHED_NAME  ->
   a java.lang.String object PROXY_DISTINGUISHED_NAME has to be set
   in prop.

- OracleConnection.PROXYTYPE_CERTIFICATE ->
   a bytep[] which contains the certificate PROXY_CERTIFICATE needs to be
   set in prop. Properties prop = new Properties();
   prop.put(OracleConnection.PROXY_CERTIFICATE, cert);
   oraConn.openProxySession(OracleConnection.PROXYTYPE_CERTIFICATE, prop);

- OracleConnection.PROXY_ROLES ->
   Roles can also be set in the property argument, the value is a String[]
   which contains the roles.
   prop.put(OracleConnection.PROXY_ROLES, roles);
   oraConn.openProxySession(OracleConnection.PROXY_ROLES, prop);


public boolean isProxySession(): returns true if the session
   associated with this connection is a proxy session.

public void close(int OracleConnection.PROXY_SESSION) throws
  java.sql.SQLException closes the proxy session.

ProxyAuten.java
===============
/*
 * Pre-Requisite: create the database users (client, proxy)
 * or use existing database schemas: HR (client), and SCOTT (proxy)
 * See "How proxy Authentication Works"
 *
 * connect system/<password>
 * create role proxyquery;
 * create role proxyinsert;
 * create role proxyupdate;
 * create role proxydelete;
 * grant select on regions to proxyquery;
 * grant insert on regions to proxyinsert;
 *      grant proxyquery, proxyinsert to hr;
 *      ALTER USER hr GRANT CONNECT THROUGH scott WITH ROLE
 *              proxyquery,proxyinsert;
 *
 */

import java.util.*;
import java.sql.*;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

class ProxyAuthen
{
  public static void main (String args [])
       throws SQLException
  {
    OracleConnection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    OracleDataSource ods = new OracleDataSource();
    String url = "jdbc:oracle:thin:scott/tiger@//localhost:1521/
orcl";
    ods.setURL(url);

    // get a proxy connection
    conn = (OracleConnection)ods.getConnection();

    // Check if the connecton is a proxy session, should return false

    System.out.println("isProxySession? " + conn.isProxySession());


    // open a proxy session of type "connect through with role".
    Properties prop = new Properties();
    prop.put(OracleConnection.PROXY_USER_NAME, "HR");
    String[] roles = {"proxyquery", "proxyinsert"};
    prop.put(OracleConnection.PROXY_ROLES, roles);
    conn.openProxySession(OracleConnection.PROXYTYPE_USER_NAME,
prop);

    // Check if the connecton is a proxy session, should return true
   System.out.println("isProxySession? " + conn.isProxySession());


    // Check the Current Id of the Session
    stmt = conn.createStatement();
    rset = stmt.executeQuery("select user from dual");
    while (rset.next())
     {
         System.out.println("This Session is associated with: " +
         rset.getString(1));
     }
     rset.close();

     // Performing authorized activities on behalf of the client
     rset = stmt.executeQuery("select count(*) from regions");
     while (rset.next())
     {
      System.out.println("# of existing Regions: " + rset.getInt(1));
     }
     rset.close();

     System.out.println("Adding a new Region and Printing the whole
table");
     stmt.execute("insert into regions values (5, 'New Region')");

     rset = stmt.executeQuery("select * from regions");
     while (rset.next())
     {
       System.out.println("Region id: " + rset.getInt(1) + "  Region
Name: "

         + rset.getString(2));
      }
      rset.close();

     // Attempt to perform unauthorized activities/roles must fail

      try
      {
       stmt.execute("delete from regions where region_id = 1");

      } catch (SQLException e)
      {
        System.out.println("Proxy user attempts to delete from
HR.Regions");
     }
     stmt.close();
     conn.close();
     ods.close();

  }
}

C:>javac ProxyAuthen.java

C:>java ProxyAuthen
isProxySession? false
isProxySession? true
This Session is associated with: HR
# of existing Regions: 4
Adding a new Region and Printing the whole table
Region id: 1 Region Name: Europe
Region id: 2 Region Name: Americas
Region id: 3 Region Name: Asia
Region id: 4 Region Name: Middle East and Africa
Region id: 5 Region Name: New Region
Proxy user attempts to delete from HR.Regions

C:>

...

java.util.Properties connAttr = null;
...
//obtain connection from a cache enabled DataSource
OracleConnection conn = ds.getConnection("scott",tiger");
conn.openProxySession(proxyType, proxyProps);
...
connAttr.setProperty("CONNECTION_TAG","JOE'S_PROXY_CONNECTION");
conn.applyConnectionAttributes(connAttr); //apply attributes to the
// connection
conn.close(); //return the tagged connection to the cache
...
//come back later and ask for Joe's proxy connection
conn=ds.getConnection(connAttr); //This will retrieve Joe's proxy
// connection

String connString="jdbc:oracle:thin:@prodHost:1521:ORCL";

String connString = "jdbc:oracle:thin:scott/tiger@//
mycomany.com:5521/sales"

Statement stmt = null;
ResultSet rs = null;
// assume conn refers to a connection object
stmt = conn.createStatement();

// Select ename from the emp where the hiredate is Nov-25-1995
       ResultSet rset = stmt.executeQuery
       ("SELECT ename FROM emp WHERE hiredate = {d '1995-11-25'}");
        or
       ("SELECT ename FROM emp WHERE hiredate = {t '12:00:00'}");

// Select empno from the emp where ename starts with '_'
 ResultSet rset = stmt.executeQuery
 ("SELECT empno FROM emp WHERE ename LIKE '&_%' {escape '&'}");

("SELECT empno FROM emp WHERE ename LIKE '\_%' {escape ''}");

SELECT prod_id, prod_name from prod_tab where prod_id
in ( ?, ?, );

SELECT prod_id, prod_name
FROM prod_tab
WHERE prod_id IN (SELECT column_value FROM TABLE(?))

pstmt.setXXX(<column index>, <constant value>|<bind
variable>)

int empnum = 7934

PreparedStatement pstmt =
  conn.prepareStatement
("SELECT empno, ename FROM emp WHERE empno = ? ");
pstmt.setInt(1, empnum);
...

-------

int empnum = 7934

PreparedStatement pstmt =
  conn.prepareStatement("UPDATE emp
  SET ename = ? WHERE empno = ? ");
pstmt.setString(1, "FOOBAR");

pstmt.setInt(2, empnum);
...

pstmt.executeUpdate();
...

int empno = 12345;
String ename = "uFF2AuFF4FuFF45";
String job = "Manager";
oracle.jdbc.OraclePreparedStatement pstmt =
(oracle.jdbc.OraclePreparedStatement)
conn.prepareStatement
    ("INSERT INTO emp (empno, ename, job) VALUES(?, ?, ?)");
pstmt.setFormOfUse(2, FORM_NCHAR);
pstmt.setFormOfUse(3, FORM_NCHAR);
pstmt.setInt(1, 1);
pstmt.setString(2, ename);
pstmt.setString(3, job);
pstmt.execute();

opstmt.setIntAtName ("col1", 12);
opstmt.setStringAtName("col2", "string");
opstmt.setFloatAtName("col3", (float)1.2);
opstmt.setBooleanAtName("col4", true);
opstmt.setShortAtName("col5", (short)7);
opstmt.setBigDecimalAtName("col7", new BigDecimal(123));
opstmt.setDoubleAtName("col8", 1.23);
opstmt.setIntAtName("col9", 12);
opstmt.setLongAtName("col10", (long)123);

cstmt1.registerOutParameter(1,Types.VARCHAR);
...

cstmt1.setString(2, "aadfadfad");
cstmt1.setInt(3, 125);
...

cstmt1.execute();
...

// var = cstmt.getXXX(index);
String outParam = cstmt1.getString(1);

// Calling a procedure which has no parameter
try (
  cstmt = conn.prepareCall("{call p0}");
  cstmt.execute();
} catch (SQLException ex) {
}

// Calling a function which has no parameter but returns
// a VARCHAR
try (
cstmt2 = conn.prepareCall("{? = call f0o}");
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.execute();
String res = cstmt.getString(1);
} catch (SQLException ex) {
}


// Calling a procedure which has one IN parameter
try (
cstmt = conn.prepareCall("{call p1i(?)}");
cstmt.setString(1, "foo");
cstmt.execute();
} catch (SQLException ex) {
}

/*
 * Calling a stored procedure with no parameters
 *
 */

 {
    CallableStatement proc0 = conn.prepareCall
    ("begin foo; end;");
    proc0.execute ();
 }

/*
 * Calling a procedure with an IN parameter
 *
 */
 {
  CallableStatement p1in =
              conn.prepareCall ("begin proc1 (?);
end;");
  p1in.setString (1, "foobar");
  p1in.execute ();
}
†

/*
 * Calling a procedure with an OUT parameter
 *
 */
 {
    CallableStatement p1out =
           conn.prepareCall ("begin ocstmt (?); end;");
    p1out.registerOutParameter (1, Types.CHAR);
    p1out.execute ();
    System.out.println ("Out argument is: "+
p1out.getString (1));
 }

 /*
  * Calling a procedure with an IN/OUT prameter
  *
  */
  {
    CallableStatement p1inout =
             conn.prepareCall ("begin procinout (?);
end;");
    p1inout.registerOutParameter (1, Types.VARCHAR);
    p1inout.setString (1, "foobar");

    p1inout.execute ();
    System.out.println ("Out argument is: " +
p1inout.getString (1));
  }

/*
 * Calling a function with no parameters
 *
 */
 {
  CallableStatement f0 =
              conn.prepareCall ("begin ? := funcnone;
end;");
   f0.registerOutParameter (1, Types.CHAR);
   f0.execute ();
      System.out.println ("Return value is: "+
f0.getString (1));
  }

/*
 * Calling a function with an IN parameter
 *
 */
 {
   CallableStatement f1in =
             conn.prepareCall ("begin ? := funcin (?);
end;");
   f1in.registerOutParameter (1, Types.CHAR);
   f1in.setString (2, "aaaaaaaaaaa");
   f1in.execute ();
   System.out.println ("Return value is: " +
f1in.getString (1));
 }


/*
 * Calling a function with an OUT parameter
 *
 */
 {
     CallableStatement f1out =
             conn.prepareCall ("begin ? := funcout (?);

end;");
      f1out.registerOutParameter (1, Types.CHAR);
      f1out.registerOutParameter (2, Types.CHAR);
      f1out.execute ();
      System.out.println ("Return value is: " +
f1out.getString (1));
      System.out.println ("Out argument is: " +
f1out.getString (2));
    }

defineParameterType, defineParameterTypeBytes,
defineParameterTypeChars, getExecuteBatch,
getReturnResultSet, OracleGetParameterMetaData,
registerReturnParameter, setARRAY, setArrayAtName,
setARRAYAtName, setAsciiStreamAtName, setBfile, setBFILE,
setBfileAtName, setBFILEAtName, setBigDecimalAtName,
setBinaryDouble, setBinaryDoubleAtName, setBinaryFloat,
setBinaryFloatAtName, setBinaryStreamAtName, setBLOB,
setBlobAtName, setBLOBAtName, setBooleanAtName,
setByteAtName, setBytesForBlob, setBytesForBlobAtName,
setCHAR, setCHARAtName, setCheckBindTypes, setCLOB,
setClobAtName, setCLOBAtName, setCursor, setCursorAtName,
setCustomDatum, setCustomDatumAtName, setDATE, setDateAtName,
setDATEAtName, setDisableStmtCaching, setDoubleAtName,
setFixedCHAR, setFixedCHARAtName, setFloatAtName,
setFormOfUse, setIntAtName, setINTERVALDS,
setINTERVALDSAtName, setINTERVALYM, setINTERVALYMAtName,
setLongAtName, setNullAtName, setNUMBER, setNUMBERAtName,
setObjectAtName, setOPAQUE, setOPAQUEAtName,
setOracleObject, setOracleObjectAtName, setORAData,
setORADataAtName, setPlsqlIndexTable, setRAW,
setRAWAtName, setREF, setRefAtName, setREFAtName,
setRefType, setRefTypeAtName, setROWID, setROWIDAtName,
setShortAtName, setStringAtName, setStringForClob,
setStringForClobAtName, setSTRUCT, setSTRUCTAtName,
setStructDescriptor, setStructDescriptorAtName,
setTimeAtName, setTIMESTAMP, setTimestampAtName,
setTIMESTAMPAtName, setTIMESTAMPLTZ,
setTIMESTAMPLTZAtName, setTIMESTAMPTZ,
setTIMESTAMPTZAtName, setUnicodeStreamAtName,
setURLAtName.

getAnyDataEmbeddedObject, getARRAY, getAsciiStream, getBFILE,
getBinaryStream, getBLOB, getCHAR, getCharacterStream,
getCLOB, getCursor, getDATE,  getINTERVALDS, getINTERVALYM,
getNUMBER, getOPAQUE, getOracleObject,
getOraclePlsqlIndexTable, getORAData, getPlsqlIndexTable,
getRAW, getREF, getROWID, getSTRUCT, getTIMESTAMP,
getTIMESTAMPLTZ, getTIMESTAMPTZ, getUnicodeStream,
registerIndexTableOutParameter, registerOutParameter,
sendBatch, setBinaryDouble, setBinaryFloat, setBytesForBlob,
setExecuteBatch, setStringForClob.

/* Example#1
 * Basic CallableStatement using Cursor
 *
 */

OracleCallableStatement cstmt =
  (OracleCallableStatement) conn.prepareCall
       ("begin select cursor(select 1 from dual) into ? from dual;
end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);

ResultSet rs = cstmt.executeQuery();

// returns true if the column is of type NCHAR/NVARCHAR/NCLOB
cstmt.isNCHAR(1);

while(rs.next())
System.out.println ("column1 = " + rs.getString(1));
cstmt.close();


/* Example#2
 * CallalableStatement with TIMESTAMP dtatypes OUT parameters
 * Assume a procedure timeproc (?, ?, ?, ?)
 */


String dateStr = "2005-09-03 12:31:56.78";
Timestamp tss = Timestamp.valueOf(dateStr);
TimeZone tz = TimeZone.getDefault();
tz.setID("US/Pacific");
greg_cal = new GregorianCalendar(tz);

tsltz = new oracle.sql.TIMESTAMPLTZ(conn, tss, gcal);
tstz = new oracle.sql.TIMESTAMPTZ(conn, tss, gcal);
ts = new oracle.sql.TIMESTAMP(tss);
date = new oracle.sql.DATE(tss);
long longTime = tss.getTime();
time = new Time(longTime);
dateTime = new java.sql.Date(longTime);

CallableStatement ocstmt = null;

((oracle.jdbc.OracleConnection) conn).setSessionTimeZone("GMT");

ocstmt = conn.prepareCall ("begin timeproc (?, ?, ?, ?); end;");

((OracleCallableStatement)ocstmt).setTIMESTAMPLTZ(1, tsltz);
((OracleCallableStatement)ocstmt).registerOutParameter
                                  (1, OracleTypes.TIMESTAMPLTZ);

((OracleCallableStatement)ocstmt).setTIMESTAMPTZ(2, tstz);
((OracleCallableStatement)ocstmt).registerOutParameter
                                   (2, OracleTypes.TIMESTAMPTZ);

((OracleCallableStatement) ocstmt).setTIMESTAMP(3, ts);
((OracleCallableStatement) ocstmt).registerOutParameter
                                       (3, Types.TIMESTAMP);

((OracleCallableStatement) ocstmt).setTimestamp(4, tss, greg_cal);
((OracleCallableStatement) ocstmt).registerOutParameter
                                  (<index>, Types.TIMESTAMP);
ocstmt.executeUpdate();
ocstmt.close();

ResultSet rset = null;
Statement stmt = conn.createStatement();

int countrows stmt.executeUpdate("INSERT INTO prodtab " +
                   "(prodid, prodname) " +
                   "VALUES (12345,'abdcefghi')",
                   Statement.RETURN_GENERATED_KEYS);

// Obtain the result set object from the query.
rs = stmt.getGeneratedKeys();

// Retrieve the auto generated key.
while (rset.next())
     int key = rs.getInt(1);
rset.close();

// Set & Get the statement cache size at the data source
// level
OracleConnectionCacheImpl ods =
        new OracleConnectionCacheImpl(ocpds);
 ods.setStmtCacheSize(12);

/*
 *   Implicit Statement Caching
 *
 */
// import the java.sql package
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class ImplStmtCache
{
 public static void main (String args [])
 throws SQLException
 {
  long  start;
  long end;

  long elapsed;

  String name = null;
  OracleConnection conn = null;
  OraclePreparedStatement pstmt = null;
  OracleDataSource ods = new OracleDataSource();
  // Set the URL (user name, and password)
  String url = "jdbc:oracle:thin:scott/tiger@//localhost:1521/orcl";
  ods.setURL(url);

  String query = "select EMPNO, ENAME from EMP";
  System.out.println("PredStmt is " + query);

  //
  // Phase 1 - NO caching
  //
  // Get a connection object
  conn = (OracleConnection)ods.getConnection();
  // Set the start time
     start = System.currentTimeMillis();
  // Loop, prepare and execute the query 10 times
  for (int i = 1; i <= 10; i++) {
    pstmt = (OraclePreparedStatement)conn.prepareStatement (query);
    ResultSet rset = pstmt.executeQuery ();
    // Do not process ResultSet
    rset.close();
    pstmt.close();

  }
  // Set the End time
     end = System.currentTimeMillis();
     elapsed = end - start ;
  // Print the time taken to prepare and execute query
     System.out.println
     (" Time to prepare and execute NON CACHED query 10 times is: " +
       elapsed);
  // Close the connection
  conn.close();
  //
  // Phase 2 - Implicit Statement Caching

  //
  // Get a connection object
     conn = (OracleConnection)ods.getConnection();
  // Set the Statement cache size to 1
     conn.setStatementCacheSize(1);
  // Enable Implicit caching
     conn.setImplicitCachingEnabled(true);
  // Set the start time
     start = System.currentTimeMillis();
  // Loop, prepare and execute the query 10 times
  for (int i = 1; i <= 10; i++) {
     pstmt = (OraclePreparedStatement)conn.prepareStatement (query);
     ResultSet rset = pstmt.executeQuery ();
  // Do not process ResultSet
     rset.close();
     pstmt.close();
   }
  // Set the End time
     end = System.currentTimeMillis();
     elapsed = end - start;
  // Print the time taken to prepare and execute query
     System.out.println
       (" Time to prepare and execute CACHED query 10 times is: "
       + elapsed);
  // Close the connection
  conn.close();
 }
}

C:>javac ImplStmtCache.java
C:>java ImplStmtCache
PredStmt is select EMPNO, ENAME from EMP
 Time to prepare and execute NON CACHED query 10 times is: 611
 Time to prepare and execute CACHED query 10 times is: 20

pstmt.closeWithKey("Stmt1");

/*
 *   Explicit Statement Caching
 *
 */
// import the java.sql package
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class ExplStmtCache
{
 public static void main (String args [])
 throws SQLException
 {
  long  start;
  long end;
  long elapsed;

  String name = null;
  OracleConnection conn = null;
  OraclePreparedStatement pstmt = null;
  OracleDataSource ods = new OracleDataSource();
  // Set the URL (user name, and password)
  String url = "jdbc:oracle:thin:scott/tiger@//localhost:1521/orcl";
  ods.setURL(url);
  String query = "select EMPNO, ENAME from EMP";
  System.out.println("PredStmt is " + query);

  //
  // Phase 1 - NO caching

  //
  // Get a connection object
  conn = (OracleConnection)ods.getConnection();
  // Set the start time
     start = System.currentTimeMillis();
  // Loop, prepare and execute the query 10 times
  for (int i = 1; i <= 10; i++) {
    pstmt = (OraclePreparedStatement)conn.prepareStatement(query);
    ResultSet rset = pstmt.executeQuery ();
    // Do not process ResultSet
    rset.close();
    pstmt.close();
   }
    // Set the End time
    end = System.currentTimeMillis();
    elapsed = end - start ;
    // Print the time taken to prepare and execute query
    System.out.println
    (" Time to prepare and execute NON CACHED query 10 times is : "
    + elapsed);

  // Close the connection
  conn.close();
  //
  // Phase 2 - Explicit Statement Caching
  //
  // Get a connection object
     conn = (OracleConnection)ods.getConnection();
  // Set the Statement cache size to 1
     conn.setStatementCacheSize(1);

  // Enable Explicit caching
     conn.setExplicitCachingEnabled(true);

  // Set the start time
     start = System.currentTimeMillis();
  // Loop, search/find or prepare and execute the query 10 times

  for (int i = 1; i <= 10; i++) {
  // Look first in the cache
   pstmt =
(OraclePreparedStatement)conn.getStatementWithKey("Stmt1");

   if (pstmt == null) { //If not in the cache then prepare again
       pstmt = (OraclePreparedStatement)conn.prepareStatement(query);
   }

   ResultSet rset = pstmt.executeQuery ();
   // Do not process ResultSet
   rset.close();
   pstmt.closeWithKey("Stmt1"); ;
   }
  // Set the End time
     end = System.currentTimeMillis();
     elapsed = end - start;
  // Print the time taken to prepare and execute query
     System.out.println
      (" Time to prepare and execute EXPLICITLY CACHED query 10 times
is " +
          elapsed);
  // Close the connection
     conn.close();
 }
}

C:>javac ExplStmtCache.java
C:>java ExplStmtCache
PredStmt is select EMPNO, ENAME from EMP
 Time to prepare and execute NON CACHED query 10 times is : 581
 Time to prepare and execute EXPLICITLY CACHED query 10 times is 10

PreparedStmt pstmt =
      conn.preparedStatement("INSERT INTO PRODUCT VALUES (?,
?)");

pstmt.setInt(1, 1234);
pstmt.setString(2, "Product #1");
pstmt.addBatch();
...
pstmt.setInt(1, 1234);
pstmt.setString(2, "Product #1");
pstmt.addBatch();
...
pstmt.setInt(1, 1234);
pstmt.setString(2, "Product #1");
pstmt.addBatch();
...
int [] batchCount = pstmt.executeBatch();

Statement stmt = conn.createStatement();
...
// First batch set
stmt.addBatch("INSERT INTO product VALUES( 1111, 'Prod1')");
stmt.addBatch("INSERT INTO product VALUES( 2222, 'Prod2')");
stmt.addBatch("INSERT INTO product VALUES( 3333, 'Prod3')");
stmt.addBatch("INSERT INTO product VALUES( 4444, 'Prod4')");
executeBatch();

//Second batch set
stmt.addBatch("insert into product values (5555,'Prod5')");
stmt.executeBatch();

// Explicit Commit
conn.commit();
ps.close();

Connection conn = ds.getConnection();

//Always disable auto-commit when using update batching
conn.setAutoCommit(false);


PreparedStmt pstmt =
      conn.preparedStatement("INSERT INTO PRODUCT VALUES (?,
?)");

//Set Batch value for this statement to 3
((OraclePreparedStatement)pstmt).setExecuteBatch (3);

pstmt.setInt(1, 1234);
pstmt.setString(2, "Product #1");
pstmt.executeUpdate();
...
pstmt.setInt(1, 1234);
pstmt.setString(2, "Product #1");
pstmt.executeUpdate();

...
pstmt.setInt(1, 1234);

pstmt.setString(2, "Product #1");
pstmt.executeUpdate(); // This one triggers the shipment

conn.commit();  // Explicit Commit
pstmt.close();