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

  • Avatar
    itoperations

    Hi, how can we save the output to a .txt or .csv file when using the OleDbCommand Execution Method please?

  • Avatar
    Gennaro Piccolo

    Hello, any Oracle query can use the spool command to spool out the result of a query to CSV, or TXT file.

    Here's an example:

    CHRIS>desc table_a
    Name Null? Type
    ------------------ -------- ---------------
    LAST_NAME VARCHAR2(1000)
    FIRST_NAME VARCHAR2(1000)
    UNIQUE_ID VARCHAR2(4000)

    CHRIS>spool out.csv
    CHRIS>select first_name || ',' || last_name from table_a;
    McDonald,Connor
    Saxon,Chris

    CHRIS>spool off

    This would be no different if you were doing the query outside of JAMS. If you want to use a CSV, make sure you concatenate the results of your query with a comma.

    CHRIS>select first_name || ',' || last_name from table_a;
    McDonald,Connor
    Saxon,Chris

    CHRIS>spool off