SQL Stored Procedure Guidelines for Creating Jobs

Reports and Jobs › Develop New Jobs › SQL Stored Procedure Guidelines for Creating Jobs

When creating a new job using SQL stored procedures, you must pass the P_JOB_RUN_ID and P_JOB_USER_ID parameters.

The parameter order for SQL stored procedures is important on SQL Server and Oracle.

We recommend the following when writing stored procedure for SQL server and for Oracle:

The stored procedure must start as follows:

CREATE PROCEDURE OR REPLACE PROCEDURE <my new job> (
@P_JOB_RUN_ID			NUMERIC,
@P_JOB_USER_ID			NUMERIC,
)

The stored procedure must start as follows:

CREATE OR REPLACE PROCEDURE <my new job> (
P_JOB_RUN_ID	IN NUMBER,
P_JOB_USER_ID	IN NUMBER,
) AS
<procedure body>

Other parameters are passed in the order they are listed in the Job Definition: Parameters page.

More information: