Hi all,
I am trying to convert a number without decimals to a decimal. This is for monetary value. Example: 3376 should be 33.78. I am unable to despite using division operator. This is because the number is stored in an nested table/array.
To give a context the way the data is set up is in a .csv file which comes from a payment system. This file has all the data pertaining to any DEBIT or CREDIT but unfortunately without a decimal. So the amounts are depicted as: 12345 for 123.45$, 3456 for 34.56$ and so on.
So we take the input file and then process line by line data. The data in the file is as follows:
DATE_TIME,AMOUNT,ORDER_ID,TRANSACTION_TYPE,AUTHORISATION_CODE,CARD_NUMBER,BATCH_NUMBER,TERMINAL_ID,CARD,ISO_CODE,RESPONSE_CODE,RESULT,REFERENCE_NUMBER,CUSTOMER_ID,TRANSACTION_NUMBER
11/01/2022 02:00,3378,DAR590775,Purchase,00154D ,41471238,30,66080053,Visa,1,27,Approved,660800530010300020 M,0,561671-0_283
11/01/2022 02:00,13514,DAR590760,Purchase,01508D ,41471195,30,66080053,Visa,1,27,Approved,660800530010300040 M,0,561673-0_283
11/01/2022 02:01,8108,DAR590761,Purchase,03382D ,41474883,30,66080053,Visa,1,27,Approved,660800530010300050 M,0,561674-0_283
11/01/2022 02:01,11351,DAR590762,Purchase,98277D ,41001788,30,66080053,Visa,1,27,Approved,660800530010300060 M,0,561675-0_283
11/01/2022 02:01,5405,DAR590763,Purchase,921001,37029689,30,66080053,AmEx,1,25,Approved,660800530010300070 M,0,561676-0_283
Here is a brief code of the program that I am using.
DECLARE
v_path varchar2(30) := '&&so_inpath';
v_file_name varchar2(130) := '&&so_infile';
v_file_type utl_file.file_type;
type fields_map is table of varchar2(2500) index by varchar2(40);
t_fields_map fields_map := fields_map();
t_key_value fields_map := fields_map();
t_init fields_map := fields_map();
rec_fztspay FZTSPAY%rowtype;
BEGIN
<<CODE>>
<<CODE>>
This is code where the problem lies. When I try to divide the amount of 3378 by 100 (which gets populated into T_KEY_VALUE('DEBIT_OR_CREDIT') field),
I get the following error:
IF t_key_value.EXISTS('DEBIT_OR_CREDIT') THEN
IF UPPER(t_key_value('FZTSPAY_TRXN_TYPE')) = '''PURCHASE''' THEN
DBMS_OUTPUT.PUT_LINE('1: '||t_key_value('DEBIT_OR_CREDIT'));
t_key_value('FZTSPAY_TRXN_CREDIT') := t_key_value('DEBIT_OR_CREDIT')/100 ;
ELSIF upper(t_key_value('FZTSPAY_TRXN_TYPE')) = '''REFUND''' THEN
DBMS_OUTPUT.PUT_LINE('2: '|| t_key_value('DEBIT_OR_CREDIT'));
t_key_value('FZTSPAY_TRXN_DEBIT') := t_key_value('DEBIT_OR_CREDIT');
END IF;
t_key_value.DELETE('DEBIT_OR_CREDIT') ;
END IF;
<<CODE>>
<<CODE>>
END;
====================================================
This report/process started: 2022-NOV-18 18:36:11
====================================================
1: '3378'
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 335
ORA-06512: at line 150
How can I convert this to 33.78 ? Any assistance from you is appreciated.
Thanks in advance.