Running Oracle SQL Jobs in JAMS

Follow

Oracle SQL Processes and Queries can be ran in JAMS Jobs through a variety of methods.


If the Oracle .NET Data Access Components cannot be installed on the JAMS Server, a custom Execution Method can be configured to allow JAMS to Query Oracle via sqlplus.

  • In order to run an Oracle job on Linux, create a new Execution Method utilizing SSHAgent as your base. 
  • Within your new Oracle SQL Execution Method modify the Template with the following source:

    

#!/bin/ksh
###############################################################################
# JAMS OracleScript Execution Method
###############################################################################


#
# Source the Oracle environment
#
###. /Oracle/OraEnv
env
echo "Executing SQL Script"
$ORACLE_HOME/bin/sqlplus <<OraUsername>>/<<OraPassword>>@<<ORACLE_SID>> << ORAEND
<<JAMS.Source>>
exit
ORAEND
#
################################### END ########################################

    

Note: A central username and password can be stored in the OraUsername and OraPassword Parameters either on the Execution Method, Folder or individual Job Levels for any Jobs you wish to utilize this Execution Method for. This script also assumes the server running the job will have KornShell (ksh) installed.


  • Create a new JAMS Job referencing your Oracle SQL Execution Method, providing your SQL source directly in the Job's Source Code tab.


If the Oracle Data Access Components are installed, the OleDbCommand Execution Method can be utilized.

  • Create a new Job utilizing the OleDbCommand Execution Method. 
  • Add a new Parameter named "ConnectionString" with a value as shown:

 

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
 Note: A proper database, username and password must be supplied

  • Supply your SQL source directly in the Job's Source Code tab.
Have more questions? Submit a request

Comments