...

Except for use of the JS7 - JITL SQLPLUSJob no Oracle Client installation is required at run-time for use of a wallet with JS7 Agents.

However, users need an Oracle Client to set up and to configure the wallet.

...

The JS7 - JITL PLSQLJob does not use a Hibernate configuration file but uses the job argument db_url for the database URL.

...

The Hibernate configuration file is located in JETTY_BASE/resources/joc/hibernate.cfg.xml, see JS7 - Database. The hibernate configuration can look like this:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
  <property name="hibernate.connection.password"></property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@/js7?tns_admin=/home/js7/wallet</property>
  <property name="hibernate.connection.username"></property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle12cDialect</property>
  <property name="hibernate.show_sql">false</property>
  <property name="hibernate.connection.autocommit">false</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
  <property name="hibernate.connection.provider_class">org.hibernate.hikaricp.internal.HikariCPConnectionProvider</property>
  <property name="hibernate.hikari.maximumPoolSize">10</property>
 </session-factory>
</hibernate-configuration>

...

Anchor
wallet_location wallet_location

Wallet Location for Java

The JS7 - JITL SQLPLUSJob identifies the wallet location from its sqlnet.ora configuration file.

The JS7 - JITL SQLExecutorJob and JS7 - JITL PLSQLJob identify the wallet location from a Java define.

• Configure the location of the wallet by use of a Java define like this: 
  -Doracle.net.wallet_location=/home/js7/wallet. This setting should point to the directory where the wallet files are located. This setting can be specified for an Agent with one of the following options:
  
  • specify the JAVA_OPTIONS environment variable before running the agent_<port>.sh|.cmd Instance Start Script.
  • for Unix add the JAVA_OPTIONS environment variable to the systemd service file, see JS7 - systemd Service Files for automated Startup / Shutdown with Unix Systems
  • Find further details from JS7 - How To - Apply Java Options.

Using Oracle Wallet® for the JITL

...

SQLExecutorJob

JS7 offers the JS7 - JITL SQLExecutorJob template for use with Oracle Wallet®.

The job template is running with JS7 Agents and performs standard SQL operations for any DBMS including Oracle. This job template cannot be used to execute PL/SQL code that is specific to Oracle.

Prerequisites

All of the above explained Prerequisites apply.

Configuration

All of the above explained Configuration items apply.

Using Oracle Wallet® for the JITL PLSQLJob

JS7 offers the JS7 - JITL PLSQLJob template JS7 offers the following job templates for use with Oracle Wallet®:

• JS7 - JITL SQLPLUSJob : SQL*Plus Command Line Client

.

The job template is running with JS7 Agents and can be used to execute PL/SQL code that is specific to Oracle. This job template can be used for the Oracle DBMS only.

Prerequisites

All of the above explained Prerequisites apply.

Configuration

All of the above explained Configuration items apply.

Using Oracle Wallet® for the JITL SQLPlusJob

JS7 offers the JS7 - JITL SQLPLUSJob template for use with Oracle Wallet®.

The job template is running with JS7 The template job is running with Agents and makes use of the sqlplus Command Line Client, therefore the wallet configuration is applied to the respective Agent. This job template requires prior installation of an Oracle Client that includes the SQL*Plus Command Line Client.

Prerequisites

Prerequisites to execute SQL*Plus with Oracle Wallet® include that

• the Oracle Client including SQL*Plus is installed
• the following environment variables are set: ORACLE_HOME, LD_LIBRARY_PATH=$ORACLE_HOME/lib, TNS_ADMIN

The prerequisites for setting up the wallet are the same as explained above with chapter Prerequisites, Oracle Wallet®.

• Consider that the mkstore command will add the location of the wallet to your sqlnet.ora configuration file.
  • This file is required by SQL*Plus and allows to execute the command line client like this: sqlplus /@js7. 
  • The  js7 is the key for the tnsnames.ora configuration file to identify the database connection settings and for the wallet to identify the matching credentials.

Configuration

Environment Variables

The prerequisites to set environment variables for use of SQL*Plus with Oracle Wallet® can be met

• by adding environment variable to the Agent Instance Start Script or
• by setting up JS7 - Job Resources to inject environment variables to workflows and jobs.

Environment Variables from the Agent Instance Start Script

• Adjust Agent Instance Start Script
  • For Unix add environment variables to the Agent Instance Start Script ./bin/agent_<port>.sh
    • ORACLE_HOME=/some_location
LD_LIBRARY_PATH=$ORACLE_HOME/lib
TNS_ADMIN=/some_location
export ORACLE_HOME LD_LIBRARY_PATH TNS_ADMIN
  • For Windows add environment variables to the Agent Instance Start Script ./bin/agent_<port>.cmd
    • set ORACLE_HOME=C:\some_location
set LD_LIBRARY_PATH=%ORACLE_HOME%\lib
set TNS_ADMIN=C:\some_location
  • The Instance Start Script is executed on startup of the Agent in the context of the user account that the Agent is operated for. The environment variables are forwarded to subsequent jobs in a workflow.
• Restart the Agent.

...

Environment Variables from Job Resources

Instead of adding the above environment variables to the JS7 Agent's Instance Start Script, they can be added to JS7 - Job Resources that are assigned to the workflow or job that requires access to an Oracle database. Job Resources include name/value pairs that can be assigned any workflow or job.

...

This file is located in the directory specified by the TNS_ADMIN environment variable. The wallet location is identified from the sqlnet.ora configuration file.

The following example is not authoritative but is intended to explain a few basic settings:

...

• The wallet location specified from /var/sos-berlin.com/js7/agent/var_4445/config/wallet is a possible location that corresponds to JS7_AGENT_CONFIG_DIR/config/wallet. Any location that is within reach of the JS7 Agent and that allows to read the wallet's files can be used.