ORA errors in p_arc_01_transform.log
- Article Type: General
- Product: Aleph
- Product Version: 16.02
Description:
Looking at "p_arc_01_transform.log" on our aleph test server, I found the following errors. I wonder how we can interpret this error, and what problem(s) this error could cause to the end users in their use of the ARC web portal and its data:
"PROCESS_CODE TASK_CODE START_DATE END_DATE ERROR_DESC
935 1144 15-SEP-06 15-SEP-06
ORA-12801: error signaled in parallel query server P016
ORA-01722: invalid number
1 row selected."
Resolution:
Remember that CRM compresses spaces.
[1] To see the specific errors, in XXX50/$ds, enter command:
grep 'Not all records were loaded' p_arc_01.log
Result shows 2 errors:
Table mrr_budget Loaded 2301 of 2302 - Not all records were loaded
Table mrr_invoice_header Loaded 27878 of 27885 - Not all records were loaded
[2] To see find the log files for these specific tables, enter command:
ls -lrt *mrr_budget*
Result:
-rw-rw-r-- 1 aleph aleph 2655 Oct 19 13:15 mrr_budget_dmp_ora.log.115
And enter command:
ls -lrt *mrr_invoice_header*
Result:
-rw-rw-r-- 1 aleph aleph 3714 Oct 19 13:17 mrr_invoice_header_dmp_ora.log.129
[3] To see the specific error lines in these files, enter command:
grep Rejected mrr_budget_dmp_ora.log.115
Result:
Record 1494: Rejected - Error on table XXXR0.MRR_BUDGET, column MAX_OVER_COMMITED.
And enter command:
grep Rejected mrr_invoice_header_dmp_ora.log.129
Result:
Record 236: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
Record 5208: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
Record 7173: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
Record 8840: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
Record 13290: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
Record 13292: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
Record 18312: Rejected - Error on table XXXR0.MRR_INVOICE_HEADER, column PAYMENT_AMOUNT.
[4] This shows that the errors are in columns/fields: PAYMENT_AMOUNT and MAX_OVER_COMMITED.
[6] Check “ALEPH to MRR Mapping.doc” to find the column/field-names in ALEPH:
FOUND: Source Table: Z77
Destination Table: MRR_INVOICE_HEADER
Key: VENDOR_CODE
Source Type Destination Field
Z77_P_AMOUNT 9(16,2) PAYMENT_AMOUNT
FOUND: Source Table: Z76
Destination Table: MRR_BUDGET
Key: Budget_Number
Source Type Destination Field Remarks
Z76-MAX-OVER-COMMITTED 9(12)V99 MAX_OVER_COMMITED Need to divide by 100…
[6] Find the specific lines that have problems in the “bad” files with the command:
dir *.bad*
Result:
-rw-rw-r-- 1 aleph aleph 112 Oct 19 13:15 mrr_budget.bad.115
-rw-rw-r-- 1 aleph aleph 969 Oct 19 13:17 mrr_invoice_header.bad.129
These are the full contents of the 2 files:
mrr_budget.bad.115
NTCONT2 NA Ref Continuations-New-Medical Y =00000000064.20 000000000000.00 19900701 19910630 1-12010-2291
mrr_invoice_header.bad.129
ABCA 00218QO33 REG REG USD 19991208 00000000
00000000 20000218 N 0000000
00000.00 000000000000.00 000000000000.00 -00=000000405.01
BBS 20129TX71 REG REG USD 20020103 00000000
00000000 20020129 N -0000000
00014.76 -000000000014.76 000000000000.00 -00=000000143.32
BBS 90505PK39 REG REG USD 19990315 00000000
00000000 19990505 N -0000000
04852.82 -000000004852.82 000000000000.00 -00=000001758.04
BBSA 20128TX33 REG REG USD 20011108 00000000
00000000 20020128 N -0000000
16209.00 -000000016209.00 000000000000.00 -00=000003786.43
BNA 80925ON10 REG REG USD 19980810 00000000
00000000 19980925 N -0000000
08965.82 -000000008965.82 000000000000.00 -00=000003028.45
[7] Find problem record in Z76. Since the budget is a single record, view it in UTIL F / 4:
enter file name (or q to exit) z76
new key = K, exit = Q, continue = RETURN
K
NTCONT2
01 z76_budget \
02 z76_budget_number ...............NTCONT2
02 z76_parent_budget_number ........
02 z76_budget_type .................
02 z76_external_budget .............1-12010-2291
02 z76_use_parent_for_inv_report ...
02 z76_name ........................Ref Continuations-New-Medical
02 z76_department ..................
02 z76_annual ......................Y
02 z76_currency ....................
02 z76_max_over_committed ..........-0000000006420
02 z76_max_over_expenditure ........00000000000000
…
The z76_max_over_committed field shows invalid content (the ‘-‘ is a problem).
[9] Find problem records in Z77. You could try to view the records in UTIL F / 4:
enter file name (or q to exit) z77
new key = K, exit = Q, continue = RETURN
K
ABCA
This works if there aren’t many records. If there are, then sqlplus access is more direct. Check the record key.
Z77_REC_KEY
Z77-VENDOR-CODE M X(20) Upper case DESC: Vendor code.
Z77-INVOICE-NUMBER M X(15) DESC: Inv
- Article last edited: 10/8/2013