Embedded Parameters in List not Passed by OFSAAI (Doc ID 1633868.1)

Last updated on OCTOBER 04, 2016

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.2.13 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)

Symptoms

In OFSAAI, when you are trying to execute a DT or PLT program with parameters separated by comma, the batch is failing with the following error:

ERROR

2014-01-21 | 17:44:31 | runprocedure | SEVERE | | | Could not Execute
2014-01-21 | 17:44:31 | runprocedure | INFO | | | After Calling execProc
2014-01-21 | 17:44:31 | runprocedure | SEVERE | | | Stored procedure execution failed

This issue can be reproduced with the following steps:

Step 1: Create a DT or PLT program with 10 paramters.

 P_Table_Description Varchar2 null
 P_Data_Filter Varchar2 null
 P_Is_Basis_Swap Varchar2 null
 P_Rule_Basis_Swap Varchar2 null
 P_Is_Liq_Add Varchar2 null
 P_Rule_Liq_Add Varchar2 null
 P_Is_Pipe_Interest Varchar2 null
 P_Rule_Pipe_Interest Varchar2 null
 P_Is_Pipe_Liq Varchar2 null
 P_Rule_Pipe_Liq Varchar2 null

Below is the procedure of parameters inside the DT or PLT program.

CREATE OR REPLACE FUNCTION Fn_Be_Propagate_TP_Rates (p_batch_id IN VARCHAR2,
  p_v_mis_date IN VARCHAR2,
  P_Table_Description IN VARCHAR2,
  P_Data_Filter IN VARCHAR2,
  P_Is_Basis_Swap IN VARCHAR2,
  P_Rule_Basis_Swap IN VARCHAR2,
  P_Is_Liq_Add IN VARCHAR2,
  P_Rule_Liq_Add IN VARCHAR2,
  P_Is_Pipe_Interest IN VARCHAR2,
  P_Rule_Pipe_Interest IN VARCHAR2,
  P_Is_Pipe_Liq IN VARCHAR2,
  P_Rule_Pipe_Liq IN VARCHAR2
  )
RETURN VARCHAR2

Step 2: Create a batch with a task which calls this DT or PLT program

Parameter List:
'Term Deposits', 'XIB_EVENT_TYPE IN (''100'',''101'', ''103'', ''104'')', 'No', 'None', 'Yes', 'TP', 'No', 'None', 'No', 'None'.
Parameter 1: 'Term Deposits'
Parameter2: 'XIB_EVENT_TYPE IN (''100'',''101'', ''103'', ''104'')'
Parameter3: 'No'
Parameter4: 'None'
Parameter5: 'Yes'
Parameter6: 'TP'
Parameter7: 'No'
Parameter8: 'None'
Parameter9: 'No'
Parameter10: 'None'

Test 1:

This batch fails at execution and the log file contains the following:

Log:
2014-01-21 | 17:44:31 | runprocedure | INFO | | | Invoking execProc
2014-01-21 | 17:44:31 | runprocedure | INFO | | | Dsn Id : INGOFSAABE
2014-01-21 | 17:44:31 | runprocedure | INFO | | | Database Type : ORACLE
2014-01-21 | 17:44:31 | runprocedure | INFO | | | NLS Value : AMERICAN_AMERICA.UTF8
2014-01-21 | 17:44:31 | runprocedure | INFO | | | Login Information Successfully Set
2014-01-21 | 17:44:31 | runprocedure | INFO | | | Parameters : Fn_Be_Propagate_TP_Rates,'INGOFSAABE_INGBE_FTP_TERM_DEPOSITS_KK_20130930_2','20130930','Term Deposits', 'XIB_EVENT_TYPE IN (''100'',''101'', ''103'', ''104'')', 'No', 'None', 'Yes', 'TP', 'No'
2014-01-21 | 17:44:31 | runprocedure | INFO | | | Statement to execute : BEGIN :D := Fn_Be_Propagate_TP_Rates('INGOFSAABE_INGBE_FTP_TERM_DEPOSITS_KK_20130930_2','20130930','Term Deposits', 'XIB_EVENT_TYPE IN (''100'',''101'', ''103'', ''104'')', 'No', 'None', 'Yes', 'TP', 'No'); END;
2014-01-21 | 17:44:31 | runprocedure | SEVERE | | | Could not Execute
2014-01-21 | 17:44:31 | runprocedure | INFO | | | After Calling execProc
2014-01-21 | 17:44:31 | runprocedure | SEVERE | | | Stored procedure execution failed

Analysis:
If you see the parameter passed in the log file, the last 3 parameters are not passed by OFSAA for calling the procedure.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms