Configuring the OracleStoredProc Execution Method

The OracleStoredProc Execution Method lets you create JAMS Jobs to run Oracle Stored Procedures.

Installing the OracleStoredProc Execution Method

To install the OracleStoredProc Execution method, run the command below in JAMSDBA after JAMS is installed. This command also adds the Oracle Sequence Tasks, the Oracle Connection Type in the Connection Store, and the ability to add an Oracle Query Parameter to a Job.

It is recommended that you create a maintenance window if you are doing this during normal business hours due to restarting the JAMS Scheduler and JAMS Server services.

1. Go to <JAMS Install Folder>\MVPSI\JAMS\Scheduler.
2. Right-click JAMSDBA.exe and select Run as administrator.
3. In the JAMSDBA, run the LOAD/ASSEMBLY=JAMSOracleIntegration command.
   
   
4. Verify the following screen is displayed after the assembly loads.
   
5. On the JAMS Scheduler, open the Windows Services application.
6. Click the JAMS Scheduler service and click Restart.
7. Click the JAMS Server service and click Restart.

Connection Information

To use the Oracle Stored Procedure Execution Method, you need to have a user name, password, and a datasource. As long as the datasource is in a format that is compatible with an Oracle Connection String, it will open a connection to the Oracle DB server.

You have two options for setting up the connection to the Oracle Database:

• (Recommended) Use the Oracle Connection in the JAMS Connection Store. This option is recommended for newer installations.
• Use the Parameters from the OracleStoredProc Execution Method. This option was available in legacy versions and is still supported.

See the sections below for information.

Using the Oracle Connection in the JAMS Connection Store

Creating Credentials in JAMS

To use the OracleStoredProc Execution Method, you first need to create two Credentials:

• A Credential that stores the username and password for connecting to the Oracle Database.
• A Credential the Windows process that initiates the connection will run under when the Job starts.

The Oracle Credential is used on the Connection Store item, and the JAMS Credential is used on the Oracle Stored Procedure Job. See Working with Credentials for more information on creating a Credential.

Creating a Connection Store Item to an Oracle Database

You can create a Connection Store Item for the connection information to the Oracle database. This Connection Store Item can be referenced in multiple Jobs, which allows you to store the connection information in one place. See Connections to Database Servers for more information on creating this Connection.

If you do not use a Connection Store Item, you need to enter the connection string and select a Login As credential on the Oracle Stored Procedure Job.

Creating an Oracle Stored Procedure Job

1. Click Definitions from the Shortcuts menu.
2. Click a Folder to save the Job.
3. Click +.
4. In the Name field, enter a name for the Oracle Stored Procedure Job.
5. In the Execution Method field, select OracleStoredProc.
6. Click Ok.
7. In the Oracle Connection field, select the Oracle Connection from the Connection Store. The Data Source field is automatically updated with the connection string and the Login As field is disabled.
8. In the Procedure field, select the stored procedure that will run in the Job.
9. (Optional) In the Output field, enter a file path to a file that will store the output from the Stored Procedure. For example, “C:\Users\Administrator\Desktop\output.txt.
10. Select or clear the Include dbms_output in JAMS log file option to have the output visible or hidden from the Log File tab in the Job entry in the Monitor view.
11. Click the Properties tab.
12. In the Execute As field, select a Credential in JAMS to run the Job.
13. Define the Schedule, Parameters, Documentation, and Security as desired.
14. When the Job has been configured as desired, click Save and Close.

You can manually submit the Job or schedule the Job to run at a set date, time, or condition.

Using Parameters for the Oracle Connection

The Oracle Stored Procedure can use a Parameter default value to store the Oracle Connection. The Parameter can be specified in the Execution Method, or at the Folder or Job level. An OracleConnectionString parameter is available on the Parameters tab.

The default format for the Execution Method is:

• User Id={0}
• Password={1}
• DataSource={2}

Once the ConnectionString format has been set, include a datasource and User Name in the Job Source tab. Establish the connection. Once the User has authorized access to the Oracle DB Server, click on the dropdown list to view a populated listing of stored procedures.

Related Topics