Clarity: Jobs that call stored procedures with over 4 parameters in Oracle fail to execute the SP correctly.

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

Jobs that call stored procedures with over 4 parameters in Oracle fail to execute the SP correctly.

Steps to Recreate:

1. Create two stored procedures in the database:
   
   
   1. Code of the first stored procedure:
      
      CREATE OR REPLACE
      PROCEDURE PROCEDURE1
      ( P_JOB_RUN_ID IN NUMBER
      , P_JOB_USER_ID IN NUMBER
      , P_PORTFOLIO_ID IN NUMBER
      , P_NEW_NAME IN VARCHAR
      ) IS
      BEGIN
      UPDATE PMA_PORTFOLIOS SET NAME = P_NEW_NAME WHERE ID = P_PORTFOLIO_ID;
      END PROCEDURE1;
      
      
   2. Code of the second stored procedure:
      
      CREATE OR REPLACE
      PROCEDURE PROCEDURE2
      ( P_JOB_RUN_ID IN NUMBER
      , P_JOB_USER_ID IN NUMBER
      , P_PORTFOLIO_ID IN NUMBER
      , P_NEW_NAME IN VARCHAR
      , P_NEW_ID IN VARCHAR
      ) IS
      BEGIN
      UPDATE PMA_PORTFOLIOS SET NAME = P_NEW_NAME WHERE ID = P_PORTFOLIO_ID;
      END PROCEDURE2;
      
      
2. Create two new jobs:
   
   
   1. Create the first job:
      
      
      • Job Definition Name: TEST PROCEDURE 1
        
        
      • Job Definition ID: TEST_PROC_1
        
        
      • Description: test 4 parameters
        
        
      • Active: checked
        
        
      • Executable Type: SQL Stored Procedure
        
        
      • Executable Name: PROCEDURE1
        
        Add two parameter:
        
        The first:
        
        
      • Parameter Label: portfolio
        
        
      • Bind Parameter Code: P_PORTFOLIO_ID
        
        
      • Type: Pull-down
        
        
      • Pull-down: Portfolios Select (ID: PMA_PORTFOLIOS)
        
        
      • Required: checked
        
        The second:
        
        
      • Parameter Label: New Name
        
        
      • Bind Parameter Code: P_NEW_NAME
        
        
      • Type: Text
        
        
   2. Create the second job:
      
      
      • Job Definition Name: TEST PROCEDURE 2
        
        
      • Job Definition ID: TEST_PROC_2
        
        
      • Description: test 5 parameters
        
        
      • Active: checked
        
        
      • Executable Type: SQL Stored Procedure
        
        
      • Executable Name: PROCEDURE2
        
        Add tree parameters:
        
        The first:
        
        
      • Parameter Label: portfolio
        
        
      • Bind Parameter Code: P_PORTFOLIO_ID
        
        
      • Type: Pull-down
        
        
      • Pull-down: Portfolios Select (ID: PMA_PORTFOLIOS)
        
        
      • Required: checked
        
        The second:
        
        
      • Parameter Label: New Name
        
        
      • Bind Parameter Code: P_NEW_NAME
        
        
      • Type: Text
        
        The third:
        
        
      • Parameter Label: New ID
        
        
      • Bind Parameter Code: P_NEW_ID
        
        
      • Type: Text
        
        
3. Execute the jobs:
   
   
   1. Create a portfolio
      
      Portfolio Name: PF_1
      Portfolio ID: pf_1
      
      
   2. Execute the job TEST PROCEDURE 1
      
      
      • Choose the portfolio PF_1 and enter the new name: NEW_PF_1
        
        
      • Check the new name of the portfolio: it's NEW_PF_1
        
        
   3. Execute the job TEST PROCEDURE 2
      
      
      • Choose the portfolio NEW_PF_1 and enter the new name: NEW_PF_2
        
        
      • Check the new name of the portfolio: it's always NEW_PF_1 and not NEW_PF_2

Actual Results: The SQL update doesn't run
Expected Results: The job calling Test Procedure 2 (5 parameter job) should cause the SQL update to complete.

Solution:

Workaround:
Reduce parameters to 4 and query within the stored procedure for some of the required information.

Status/Resolution:
Pending resolution in a future release.

Keywords: CLARITYKB, CLRT-39185, stored procedure, sp.

Environment

Release:
Component: PPMENV