Passing Multiple-Value Parameter To Oracle Procedure

Scenario:

Client wants to add new parameter - Transaction Reason.

At beginning, they want a drop down listing all the transaction reasons and ask for multiple value input.

As there are near 150 transaction reasons for claim, it is not a good way to list all the transaction reasons. For the drop down, it needs to create a view for select the reasons. And if new reason is added, we need to update the list. There will be a large maintenance work.

After further discuss with clients, no drop down for new parameter has reached the agreement: Letting the client enter transaction reason code. And allow multiple value input.

Analysis:

Since the report is from a stored procedure, the parameter of that procedure is automatically added to report as report parameter. In this case, one should add new parameter in procedure and use the parameter in ‘where’ statement.

However, Crystal Reports’ multiple-value parameter setting is disabled when report come from a stored procedure. Crystal will pass the multiple-value inputs as a string, such as transaction code: ’70, MO, MA, 40’.

The best possible solution is: Passing a comma-delimited string as one parameter and then parsing it in the stored procedure.

There are three methods to achieve this: using temporary table to store the after-parsing input list; and using table type to store the after-parsing input list. It is noticed however, when using table type (collection), it is hard to select a row as from a real table. On other hand, temporary table will slow the system. The third method is utilizing the Oracle built-in XML functions.

Solution:

Using Oracle existing XML functions:

• In order to use those XML functions, input string must in format delimited by comma:

'333,444,aaa'

• Replace the ',' with XML tag:

lv_Transaction_Reason_Code := '' Replace(Upper(Trim(Transaction_Reason_Code)), ',', '') '';

• Use XML functions to parse string and save into a table type collection.:

CREATE OR REPLACE TYPE TP_multi_value is Table Of VARCHAR2(10) Not Null;
lv_multi_value TP_multi_value;
Select Trim(t.EXTRACT('id/text()'))
Bulk Collect
Into lv_multi_value
From Table( XMLSequence(xmltype(lv_Transaction_Reason_Code).extract('//id'))) t
Where t.EXTRACT('id/text()') Is Not Null;

• In the ref_cursor use Table() function to change collection to a table for select from:
And
(lv_Transaction_Reason_Code Is Null
Or
tr.TRANSACTION_REASON_CODE In (Select t.column_value From Table (lv_multi_value) t)
) ;

• In report, add text to ask user enter code separated by ','.

• Need to show the selected transaction reason code in the report title. To do so, need to select correct formatted input string in back end:

lv_Reason_Code Varchar2(1000);
lv_Reason_Code := Upper(TRIM(Transaction_Reason_Code));
Open op_ObjCursor For
Select lv_StartDate StartDate
, lv_EndDate EndDate
, lv_Policy_number lv_Policy_number
, lv_Reason_Code Reason_Code
……

Then in report, show the code in title using formula:

Local StringVar sReasonCode;
If Isnull({PR_PAYMENTSRECOVERIES.REASON_CODE}) Then
sReasonCode := "All"
Else
sReasonCode := {PR_PAYMENTSRECOVERIES.REASON_CODE};
"Transaction Reason Code: " + sReasonCode + chr(13) +


Important tips:

• Trim the space from original input string;
• Only insert into collection the value is not null;
• Trim the space of each individual item before save into collection.
• Those XML function is deal with string. If need number, need To_number() as last.
• In report, edit parameter. Enter promoting text: Use comma to separate the code. For example: MA,70,40. (However, the promote text could not be saved with report. It is only one time show. This is the bug for Crystal X.)
• To display the code list, format the string at backend, then select this string available in cursor.