Jobs that execute a Stored Procedure fail

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 : bg@x (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

The same Stored procedure might have worked OK prior to 16.0.3 without passing parameters

Prior to 16.0.3 DataDirect was accepting some calls not conforming to the correct definition. 

16.0.3 and onwards the Native driver is not

Unable to call Procedures with parameters from GEL