JAMS users can call an Oracle Package from JAMS using a Workflow Job.

To accomplish this, users should configure:

• A Service Account used to connect to Oracle and run the Oracle Package
• A JAMS Variable that will serve as the Oracle Connection String
• A JAMS Workflow Job that will contain the source to call the Package

Configure a Service Account for Oracle Connections

The best practice for configuring JAMS to run Oracle Packages is to create an Oracle Service account for the Oracle connections. Once the service account is created, it will be used to secure the Connection String Variable so that only JAMS Administrators can view the Service Account's username and password in JAMS. (Step 6 in the Variable instructions below)

Creating a Connection String Variable

Next, users should configure the JAMS Variable for the Oracle Connection String.

1. Open the JAMS Definitions Folder where the Oracle Package Jobs will run.
2. Select the Variable Definitions tab, then click the Add button. A dialog will appear.
3. In the Add a New JAMS Variable Definition dialog, set the Name of the Variable, a description (optional), and ensure the DataType is set to Text.
   
4. When the Properties are set as desired, click Ok. The Variable Definition dialog will appear.
5. Navigate to the Value tab and ensure the Connection String is properly set. The connection string should start with DATA SOURCE =, and end with the Oracle User and Password that will be used to call the Oracle Package. If users followed recommendations, the user and password will be for the Oracle Service Account.
   Note that the entire string should be on a single line. In the screenshot below, the line is wrapped due to the width of the JAMS dialog box.
   
6. Select the Security tab, then define security for the Account that should have access to this Variable. This should be the Oracle Service Account. Give that account Change, Control, and Inquire access to the Variable. 
   NOTE: Those in the JAMS Administrators (JAMSBypassGroup) will be able to see and modify the variable by default.
    
7. Save and Close the Variable Definition.
8. If additional Connection Strings should be referenced, or additional users will be referenced, repeat steps 1-7 for each new Connection String.

Back to Top

Creating an Oracle Package Job

With the variable defined, it's time to create the JAMS Workflow Job to call the Package.

1. Open the JAMS Definitions folder where the Oracle Package Job should reside.
2. Click the Add button to open the Add a New JAMS Job Definition dialog.
3. Set the Execution Method to Workflow, define other properties as desired, then click Ok.
   
4. The Job Definition dialog will appear. Navigate to the Source tab, then find and drag a SQL*Plus Script activity into the Workflow Editor.
5. Select the newly added activity, then enter the script that calls the Oracle Package in the activity source box.
   
6. Open the ConnectionString from the Properties Panel, then enter the name of the previously created connection string variable, wrapped in double quotes and curly braces
"{variablename}"
   NOTE: If the Variable Name is not unique (it exists in two or more folders), reference the fully qualified path name.
   
7. Ensure the Execute As user is set in the Properties tab, to a user with Inquire permissions to the Connection String Variable.
   Note that this value should be a windows user that will run the JAMS Job.
8. Click OK, then Save and Close the Job Definition.