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.
Hi, how can we save the output to a .txt or .csv file when using the OleDbCommand Execution Method please?
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
Is this also applicable to executing a db scheduler from an Oracle database?
thank you.
Hello Michael,
This is designed to execute an Oracle Query for an Oracle database.
Hi on the template above do i have to include the "<<" sign on my configuration?
thanks
Hello Michael,
I'm not sure what you mean, please submit a ticket to support@jamsscheduler.com so we can assist further.