Configuring the OracleStoredProc Execution Method

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 and the Oracle Connection Type in the Connection Store.

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.

NOTE: If you are upgrading from JAMS V7.2 to V7.3 and you were using the Oracle Integration that includes the OracleStoredProc Execution Method, you may need to run the command below to use the Oracle Integration in JAMS V7.3.
  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.

Configuring the OracleStoredProc Execution Method

In order to use the Oracle Stored Procedure Execution Method you will first 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.

The Oracle Stored Procedure relies on a default value being present in the parameters of the Execution Method, which can be overridden at the Folder or Job level.

The default format for the Execution Method is:

  • User Id={0}
  • Password={1}
  • DataSource={2}
NOTE: By default, the User Id, Password, and DataSource set on the given OracleStoredProc Job will be passed into the "{}" braces on the Execution Method. Users do not need to edit the OracleConnectionString parameter by default.

If using a different connection string, you will need to override the original connection string by creating a new Parameter at the Folder or Job level called OracleConnectionString.

Once the ConnectionString format has been set, include a datasource and User Name in the Execution Method’s 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.

NOTE: Users without a Data Source Name (DSN) for their Oracle Connection will need to specify the entire traditional Oracle Connection String in the Data Source field of the OracleStoredProc Job's Source tab.

When a stored procedure is selected, the executable will display the parameter values associated with the stored procedure and prepend a “@” symbol in order to distinguish the procedure parameters from the executable parameters.

Once all the properties have been populated, save and submit the Job.

NOTE: Submit Options located in the Job properties can now be used to manually enter your parameter values.

Related Topics