See Also:

Usage Guidelines in JDBC

You must annotate a query with a/*+ result_cache */ hint to indicate that results are to be stored in the client result cache. For example, look at the following code snippet:

String  query  = "select /*+ result_cache */ * from emp where empno < : 1";   ((oracle.jdbc.OracleConnection)conn).setImplicitCachingEnabled(true);   ((oracle.jdbc.OracleConnection)conn).setStatementCacheSize(10);   PreparedStatement  pstmt;   ResultSet rs;         for (int j = 0 ; j < 10 ; j++ )    {       pstmt  = conn.prepareStatement (query);       pstmt.setInt(1,7500);       rs  = pstmt.executeQuery();         while (rs.next( ) )        {     // see the values  }          rs.close;          pstmt.close( ) ;        }    }

In the preceding example, the client result cache hint/*+ result_cache */ is annotated to the actual query, that is,select * from emp where empno < : 1. So, the first execution of the query goes to the database and the result set is cached for the remaining nine executions of the query. This improves the performance of your application significantly.

You must use JDBC statement caching or cache statements at the application level when using the JDBC OCI client result cache.

Validation of the Client Result Cache

Validation of the JDBC OCI client result cache can be achieved in the following ways:

See Also:

See Also:

Overview of Instant Client

The Instant Client feature makes it extremely easy to deploy OCI, Oracle C++ Call Interface (OCCI), Open Database Connectivity (ODBC), and JDBC-OCI based customer applications, by eliminating the need for an Oracle home. The storage space requirement of a JDBC OCI application running in the Instant Client mode is significantly reduced compared to the same application running on a full client-side installation. The Instant Client shared libraries occupy only about one-fourth the disk space used by a full client installation.

Table 6-1 shows the Oracle client-side files required to deploy a JDBC OCI application. Library names of release 11.1 are used in the table. The number part of library names will change in future releases to agree with the release.

Benefits of Instant Client

The benefits of Instant Client are the following:

• Installation involves copying a small number of files.

• The number of required files and the total disk storage on the Oracle client-side are significantly reduced.

• There is no loss of functionality or performance for applications deployed with the Instant Client.

• It is simple for independent software vendors to package applications.

JDBC OCI Instant Client Installation Process

The Instant Client libraries can be installed by choosing the Instant Client option from Oracle Universal Installer. The Instant Client libraries can also be downloaded from the Oracle Technology Network Web site. The installation process is as follows:

1. Download and install the Instant Client shared libraries and Oracle JDBC class libraries to a directory, such asinstantclient.

2. Set the library path environment variable to the directory from Step 1. For example, on UNIX systems, set theLD_LIBRARY_PATH environment variable toinstantclient. On Microsoft Windows, set thePATH environment variable to locate theinstantclient directory.

3. Add the full path names of the JDBC class libraries to theCLASSPATH environment variable.

After completing these steps you are ready to run the JDBC OCI application.

The JDBC OCI application operates in the Instant Client mode when the OCI and JDBC shared libraries are accessible through the library path environment variable. In the Instant Client mode, there is no dependency on theORACLE_HOME and none of the other code and data files provided inORACLE_HOME is needed by JDBC OCI, except for thetnsnames.ora file.

Instant Client can be also installed from Oracle Universal Installer by selecting the Instant Client option. The Instant Client files should always be installed in an empty directory. As with the OTN installation, you must set theLD_LIBRARY_PATH environment variable to the Instant Client directory to operate in the Instant Client mode.

If you have done a complete client installation by choosing theAdmin option, then the Instant Client shared libraries are also installed. The location of the Instant Client shared libraries and JDBC class libraries in a full client installation is:

On Linux or UNIX systems:

• libociei.so library is in$ORACLE_HOME/instantclient

• libclnstsh.so.11.1,libocijdbc11.so, andlibnnz11.so are in$ORACLE_HOME/lib

• The JDBC class libraries are in$ORACLE_HOME/jdbc/lib

On Microsoft Windows:

• oraociei11.dll library is inORACLE_HOME\instantclient

• oci.dll,ocijdbc11.dll, andorannzsbb11.dll are inORACLE_HOME\bin

• The JDBC class libraries are inORACLE_HOME\jdbc\lib

By copying these files to a different directory, setting the library path to locate this directory, and adding the path names of the JDBC class libraries to theCLASSPATH environment variable, you can enable running the JDBC OCI application in the Instant Client mode.

Usage of Instant Client

Instant Client is a deployment feature and should be used for running production applications. For development, a full installation is necessary to access demonstration programs and so on. In general, all JDBC OCI functionality is available to an application being run in the Instant Client mode, except that the Instant Client mode is for client-side operation only. Therefore, server-side external procedures cannot operate in the Instant Client mode.

Patching Instant Client Shared Libraries

Because Instant Client is a deployment feature, the emphasis has been on reducing the number and size of files required to run a JDBC OCI application. Therefore, all files needed to patch Instant Client shared libraries are not available in an Instant Client deployment. AnORACLE_HOME based full client installation is needed to patch the Instant Client shared libraries. Theopatch utility will take care of patching the Instant Client shared libraries.

After applying the patch in anORACLE_HOME environment, copy the files listed inTable 6-1, "OCI Instant Client Shared Libraries" to the instant client directory as described in"JDBC OCI Instant Client Installation Process".

Instead of copying individual files, you can generate Instant Client ZIP files for OCI, OCCI, JDBC, and SQL*Plus as described in"Regeneration of Data Shared Library and ZIP files". Then, you can copy the ZIP files to the target computer and unzip them as described in"JDBC OCI Instant Client Installation Process".

Theopatch utility stores the patching information of theORACLE_HOME installation inlibclnstsh.so.11.1. This information can be retrieved by the following command:

genezi -v

Note that if the computer from where Instant Client is deployed does not have thegenezi utility, then it must be copied from theORACLE_HOME/bin directory on the computer that has theORACLE_HOME installation.

Regeneration of Data Shared Library and ZIP files

The OCI Instant Client Data Shared Library,libociei.so, can be regenerated by performing the following steps in an Administrator Installation ofORACLE_HOME:

mkdir -p $ORACLE_HOME/rdbms/install/instantclient/lightcd $ORACLE_HOME/rdbms/libmake -f ins_rdbms.mk ilibociei

A new version of thelibociei.so Data Shared Library based on the current files in theORACLE_HOME is then placed in theORACLE_HOME/rdbms/install/instantclient directory.

Note that the location of the regenerated Data Shared Library,libociei.so, is different from that of the original Data Shared Library,libociei.so, which is located in theORACLE_HOME/instantclient directory.The preceding steps also generate Instant Client ZIP files for OCI, OCCI, JDBC, and SQL*Plus.

Regeneration of data shared library and ZIP files is not available on Microsoft Windows platforms.

Database Connection Names for OCI Instant Client

All Oracle Net naming methods that do not require theORACLE_HOME orTNS_ADMIN environment variables to locate configuration files, such astnsnames.ora orsqlnet.ora, work in the Instant Client mode. In particular, the connection string can be specified in the following formats:

• A Thin-style connection string of the form:

  host:port:service_name

  For example:

  url="jdbc:oracle:oci:@//example.com:5521:bjava21"

• A SQL connection URL string of the form:

  //host:[port][/service name]

  For example:

  url="jdbc:oracle:oci:@//example.com:5521/bjava21

• As an Oracle Net keyword-value pair. For example:

  url="jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)   (HOST=dlsun242) (PORT=5521))  (CONNECT_DATA=(SERVICE_NAME=bjava21)))"

Naming methods that requireTNS_ADMIN to locate configuration files continue to work if theTNS_ADMIN environment variable is set.

If theTNS_ADMIN environment variable is not set andTNSNAMES entries, such asinst1, are used, then theORACLE_HOME environment variable must be set and the configuration files are expected to be in the$ORACLE_HOME/network/admin directory.

The empty connection string is not supported. However, an alternate way to use the empty connection string is to set theTWO_TASK environment variable on UNIX systems, or theLOCAL variable on Microsoft Windows, to either atnsnames.ora entry or an Oracle Net keyword-value pair. IfTWO_TASK orLOCAL is set to atnsnames.ora entry, then thetnsnames.ora file must be loaded by theTNS_ADMIN orORACLE_HOME setting.

Example

Consider that thelistener.ora file on the database server contains the following information:

LISTENER = (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573)))SID_LIST_LISTENER = (SID_LIST=                     (SID_DESC=(SID_NAME=rdbms3)                     (GLOBAL_DBNAME=rdbms3.server6.us.alchemy.com)                     (ORACLE_HOME=/home/dba/rdbms3/oracle)))

You can connect to this server in one of the following ways:

url = "jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)                     (HOST=server6)(PORT=1573))                     (CONNECT_DATA=(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))"

or:

url = "jdbc:oracle:oci:@//server6:1573/rdbms3.server6.us.alchemy.com"

Alternatively, you can set theTWO_TASK environment variable to any of the connection strings and connect to the database server without specifying the connection string along with thesqlplus command. For example, set theTWO_TASK environment in one of the following ways:

setenv TWO_TASK "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))                 (CONNECT_DATA=(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))"

or:

setenv TWO_TASK //server6:1573/rdbms3.server6.us.alchemy.com

Now, you can connect to the database server using the following URL:

url = "jdbc:oracle:oci:@"

The connection string can also be stored in thetnsnames.ora file. For example, consider that thetnsnames.ora file contains the following:

conn_str = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server6)(PORT=1573))                (CONNECT_DATA=(SERVICE_NAME=rdbms3.server6.us.alchemy.com)))

If thistnsnames.ora file is located in the/home/webuser/instantclient directory, then you can set theTNS_ADMIN environment variable (orLOCAL on Microsoft Windows) as follows:

setenv TNS_ADMIN /home/webuser/instantclient

Now, you can connect as follows:

url = "jdbc:oracle:oci:@conn_str"

If thistnsnames.ora file is located in the/network/server6/home/dba/oracle/network/admin directory in the Oracle home, then instead of usingTNS_ADMIN to locate thetnsnames.ora file, you can set theORACLE_HOME environment variable as follows:

setenv ORACLE_HOME /network/server6/home/dba/oracle

Now, you can connect with either of theconn_str connection strings, as specified previously.

Iftnsnames.ora can be located byTNS_ADMIN orORACLE_HOME, thenTWO_TASK can be set to:

setenv TWO_TASK conn_str

You can then connect with the following URL:

url = "jdbc:oracle:oci:@"

Environment Variables for OCI Instant Client

TheORACLE_HOME environment variable no longer determines the location of the Globalization Support files and error message files. An OCI-only application does not require theORACLE_HOME environment variable to be set. However, if the variable is set, then it does not have an impact on the operation of the OCI driver. OCI will always obtain its data from the Data Shared Library. If the Data Shared Library is not available, only then is theORACLE_HOME environment variable used and a full client installation is assumed. Even though theORACLE_HOME environment variable is not required to be set, if it is set, then it must be set to a valid operating system path name that identifies a directory.

Environment variablesORA_NLS10 andORA_NLSPROFILES33 are ignored in the Instant Client mode.

In the Instant Client mode, if theORA_TZFILE variable is not set, then the smaller, default,timezone.dat file from the Data Shared Library is used. If the largertimezlrg.dat file is to be used from the Data Shared Library, then set theORA_TZFILE environment variable to the name of the file without any absolute or relative path names. That is:

On UNIX systems:

setenv ORA_TZFILE timezlrg.dat

On Microsoft Windows:

set ORA_TZFILE timezlrg.dat

If the driver is not operating in the Instant Client mode, then theORA_TZFILE variable, if set, names a complete path name, as it does in previous Oracle Database releases.

IfTNSNAMES entries are used, then, as mentioned earlier, theTNS_ADMIN directory must contain theTNSNAMES configuration files, and ifTNS_ADMIN is not set, then theORACLE_HOME/network/admin directory must contain Oracle Net Services configuration files.

Table 6-2 Data Shared Library for Instant Client and Instant Client Light (English)

Globalization Settings

TheNLS_LANG setting determines the language, territory, and character set aslanguage_territory.characterset. In Instant Client Light,language can only beAmerican,territory can be any that is supported, andcharacterset can be any one of the following:

• Single-byte

  • US7ASCII

  • WE8DEC

  • WE8MSWIN1252

  • WE8ISO8859P1

• Unicode

  • UTF8

  • AL16UTF16

  • AL32UTF8

Specifying character set or national character set other than those listed as the client or server character set or setting the language inNLS_LANG on the client will throw one of the following errors:

• ORA-12734

• ORA-12735

• ORA-12736

• ORA-12737

With Instant Client Light, the error messages obtained are only in English. Therefore, the valid values for theNLS_LANG setting are of the type:

American_territory.characterset

where,territory can be any valid and supported territory andcharacterset can be any one the previously listed character sets.

Instant Client Light can operate with the OCI environment handles created in the OCI_UTF16 mode.

Operation

To operate in the Instant Client Light mode, an application must set theLD_LIBARARY_PATH environment variable in UNIX systems or thePATH environment variable in Microsoft Windows to a location containing the client and data shared libraries. OCI applications by default look for the OCI Data Shared Library,libociei.so in theLD_LIBRARY_PATH environment variable in UNIX systems or theoraociei11.dll Data Shared Library in thePATH environment variable in Microsoft Windows, to determine if the application should operate in the Instant Client mode. In case this library is not found, then OCI tries to load the Instant Client Light Data Shared Library,libociicus.so in UNIX systems orlibociicus11.dll in Microsoft Windows. If this library is found, then the application operates in the Instant Client Light mode. Otherwise, a non-Instant Client mode is assumed.

Installation

Instant Client Light can be installed in one of the following ways:

• From OTN

  You can download the required file from

  http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html

  For Instant Client Light, instead of downloading and expanding the Basic package, download and unzip the Basic Light package. Theinstantclient_11_1 directory in which the lightweight libraries are unzipped should be empty before unzipping the files.

• From Client Admin Install

  Instead of copyinglibociei.so ororaociei11.dll from theORACLE_HOME/instantclient directory, copylibociicus.so ororaociic10.dll from theORACLE_HOME/instantclient/light directory. That is, the Instant Client directory on theLD_LIBRARY_PATH environment variable, in UNIX systems, should contain the Instant Client Light Data Shared Library,libociicus.so, instead of the larger OCI Instant Client Data Shared Library,libociei.so. In Microsoft Windows, thePATH environment variable should containoraociicus11.dll instead oforaociei11.dll.

• From Oracle Universal Installer

  If the Instant Client option is selected from Oracle Universal Installer, thenlibociei.so (ororaociei11.dll on Microsoft Windows) is installed in the base directory of the installation which is going to be placed on theLD_LIBRARY_PATH environment variable. This is so that Instant Client Light is not enabled by default. The Instant Client Light Data Shared Library,libociicus.so (ororaociicus11.dll on Microsoft Windows), is installed in thelight subdirectory of the base directory. Therefore, to operate in the Instant Client Light mode, the OCI Data Shared Library,libociei.so (ororaociei11.dll on Windows) must be deleted or renamed and the Instant Client Light Data Shared Library must be copied from thelight subdirectory to the base directory of the installation.

  For example, if Oracle Universal Installer has installed the Instant Client inmy_oraic_11_1 directory on theLD_LIBRARY_PATH environment variable, then one would need to do the following to operate in the Instant Client Light mode:

  cd my_oraic_11_1rm libociei.somv light/libociicus.so .

  Note:

  All the Instant Client files should always be copied or installed in an empty directory. This is to ensure that no incompatible binaries exist in the installation.