search cancel

Jobs that execute a Stored Procedure fail

book

Article ID: 252654

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

Running a job that executes a Stored Procedure fails.

Steps to Reproduce:

  1. Create a Stored Procedure in Oracle with no parameters:
    CREATE OR REPLACE PROCEDURE <my new job>
    AS
    <procedure body>
  2. Create a new SQL Stored Procedure job: Administration > Data Administration > Reports and Jobs > Job definitions > New:
    Name, ID, Description, Executable Name: <my new job>
    Executable Type: SQL Stored Procedure
    Active: Yes
  3. Run the job from Home > Personal > Reports and Jobs.

Expected Results: Job executes successfully.

Actual Results: Job fails.

Logs:
ERROR 2022-10-20 06:49:32,334 [Dispatch Z_TEST_SP : [email protected] (tenant=clarity)] niku.njs (clarity:admin:x:<my new job>) Error executing job: 5054030
com.niku.union.persistence.PersistenceException: 
SQL error code: 6550
Error message: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to '<my new job>'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Executed:
 {call <my new job> ( 5243787, 1) } 
[...]

Environment

Release : 16.0.3

Cause

Broadcom made a decision to change the driver from DataDirect to the Native driver in Clarity 16.0.3, and DataDirect was not mindful of these expectations which native drive does not allow.

Resolution

This is working as designed. The native driver is strict on the syntax that is used, and Clarity is sending two parameters for the jobs as documented:

Create a Job

Use the following syntax instead:

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 that they are listed in the Parameters page of job definition.

Additional Information

Unable to call Procedures with parameters from GEL