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

Document ID : KB000052256
Last Modified Date : 14/02/2018
Show Technical Document Details

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.