Defining a Stored Procedure job

This procedure describes how to define a database job with a program that is stored on the database.

Before You Begin

Ensure that you have met the following requirements:

• A configured Database connection profile, as described in Connection profile management.
• Successful completion of Accessing the Database job properties pane.

To define a Stored procedure job:

1. In the General tab, from the Connection Profile field, click .

   The Select Connection Profile dialog box appears.

2. Select the connection profile that you want to use and click OK.
3. From the Execution Type drop-down list, select Stored Procedure.
4. Do one of the following:
   • To retrieve available stored procedures from all available schemas or packages (Oracle only), in the Name field, click , select the required stored procedure, and then click OK.
   • To retrieve available stored procedures from a specific schema and/or a package (Oracle only), do the following:
     1. In the Schema field, click , select the schema, and click OK.
     2. In the Package field, type the name of the package, and click OK (Oracle only).
     3. In the Name field, click , select the required stored procedure, and click OK.
   • To retrieve available stored procedures from a partial list of available schemas and/or packages (Oracle only), do the following:
     1. In the Schema field, type a partial name of a schema, followed by an *.
     2. In the Package field, type a partial name of a package, followed by an * (Oracle only).
     3. In the Name field, click , select the required stored procedure and click OK.

   The object parameters are retrieved from the database and appear in the Parameters table.

5. Do one or more of the following:
   • For each In parameter, in the Value column, type a value such as string or an integer, or type a variable.
   • For each Out parameter, in the Variable column, type a variable (optional).
   • For each In/Out parameter, in the Value column, type a value, such as an integer, or a string, or type a variable.
   • For each Return parameter, in the Variable column, type a variable (optional).
6. Click More Options.
7. To automatically commit queries, select the Auto Commit checkbox.
8. In the Job Output option area, select one or more of the following:
   • Append execution log to Job Output: Shows the Environment information, Request statement, and Job statistics in the Output.
   • Append SQL output to Job Output: Shows the job output in a Text, XML, CSV, or HTML format.

     From the Output format drop-down list, select an output format.

     If you select CSV output format, define a CSV separator in the CSV Separator field. The CSV separator enables you to separate your output data with specific delimiter characters.

   For more information about defining a job in the job properties pane, see Control-M User Guide.