Oracle PL/SQL Error 06502

[saw15]

Good day,

We have just upgraded from Oracle 8.0.6 to 8.1.7, the following query used to work, but now I receive the following error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1.

Here is the query .. any help is more than appreciated.

SELECT /*+ ALL_ROWS */
/* 19_claims_aging_v008.qry */
im.name ipa_name,
Group_Processing_Status,

'&Begin_Date' Begin_Date,
'&End_Date' End_Date,
'&FundType' Fund_Type,
'&IPA_ID' IPA_ID,
rpad('&LOB',3) LOB,
rpad('&HP',10) HP,

/* average age */
AVG(Age_All) Avg_Age,
SUM(Age_All) Sum_Age_All,
SUM(Count_All) Sum_Count_All,
SUM(Age_Unpaid) Sum_Age_Unpaid,
SUM(Count_Unpaid) Sum_Count_Unpaid,

/* buckets for larger range aging */
SUM(Age_0_5) Age_0_5,
SUM(Age_6_10) Age_6_10,
SUM(Age_11_15) Age_11_15,
SUM(Age_16_20) Age_16_20,
SUM(Age_21_25) Age_21_25,
SUM(Age_26_30) Age_26_30,
SUM(Age_31_35) Age_31_35,
SUM(Age_36_50) Age_36_50,
SUM(Age_51_60) Age_51_60,
SUM(Age_61_90) Age_61_90,
SUM(Age_91_Plus) Age_91_Plus,

/* buckets for 1 day to 10 days detailed aging */
SUM(Age_1) Age_1,
SUM(Age_2) Age_2,
SUM(Age_3) Age_3,
SUM(Age_4) Age_4,
SUM(Age_5) Age_5,
SUM(Age_6) Age_6,
SUM(Age_7) Age_7,
SUM(Age_8) Age_8,
SUM(Age_9) Age_9,
SUM(Age_10) Age_10,

SUM(Billed_Amt) Billed_Amt,

SUM(To_Be_Paid) To_Be_Paid

FROM
hsd_ipa_master im,
(

SELECT
ipa_parent_id,
Group_Processing_Status,
Seq_Claim_id,
/* for all average age */
MAX(Age) As Age_All,
1 As Count_All,

/* for unpaid average age */
decode(Group_Processing_Status,
'Paid', 0,
MAX(Age)
) As Age_Unpaid,
decode(Group_Processing_Status,
'Paid', 0,
1
) As Count_Unpaid,

/* buckets for larger range aging */
MAX(Age_0_5) AS Age_0_5,
MAX(Age_6_10) AS Age_6_10,
MAX(Age_11_15) AS Age_11_15,
MAX(Age_16_20) AS Age_16_20,
MAX(Age_21_25) AS Age_21_25,
MAX(Age_26_30) AS Age_26_30,
MAX(Age_31_35) AS Age_31_35,
MAX(Age_36_50) AS Age_36_50,
MAX(Age_51_60) AS Age_51_60,
MAX(Age_61_90) AS Age_61_90,
MAX(Age_91_Plus) AS Age_91_Plus,

/* buckets for 1 day to 10 days detailed aging */
MAX(Age_1) AS Age_1,
MAX(Age_2) AS Age_2,
MAX(Age_3) AS Age_3,
MAX(Age_4) AS Age_4,
MAX(Age_5) AS Age_5,
MAX(Age_6) AS Age_6,
MAX(Age_7) AS Age_7,
MAX(Age_8) AS Age_8,
MAX(Age_9) AS Age_9,
MAX(Age_10) AS Age_10,

SUM(decode(Group_Processing_Status,
'Paid', 0,
'Preregistered', nvl(total_billed_amt, 0),
nvl(billed_amt, 0))
) AS Billed_Amt,

SUM(decode(Group_Processing_Status,
'Preregistered', nvl(total_billed_amt, 0),
'Unposted', nvl(net_amt, 0),
'Held', nvl(net_amt, 0),
0)
) AS To_Be_Paid
from
(

/*
* in-line view that selects the detailed rows needed for the report,
* resolves the processing_status in the group_processing_status,
* and does the initial bucket determination for each detail row
*/
SELECT
decode(CV.PROCESSING_STATUS,
'C', 'Paid',
'F', 'Paid',
'N', 'Paid',
'P', 'Paid',
'H', 'Held',
'U', 'Unposted',
'Preregistered') AS Group_Processing_Status,
CV.HDR_SEQ_CLAIM_ID AS Seq_Claim_Id,
CV.POST_DATE,
CV.DATE_RECEIVED,
/* age; must correspond to code used in claims_aging_bucket function */
decode(cv.processing_status,
'C', trunc(cv.post_date) - trunc(cv.date_received),
'F', trunc(cv.post_date) - trunc(cv.date_received),
'N', trunc(cv.post_date) - trunc(cv.date_received),
'P', trunc(cv.post_date) - trunc(cv.date_received),
trunc(sysdate) - trunc(cv.date_received)
) as Age,

/* bucket counts for macro level buckets */
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, -99999999, 6) as Age_0_5,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 5, 11) as Age_6_10,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 10, 16) as Age_11_15,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 15, 21) as Age_16_20,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 20, 26) as Age_21_25,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 25, 31) as Age_26_30,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 30, 36) as Age_31_35,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 35, 51) as Age_36_50,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 50, 61) as Age_51_60,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 60, 91) as Age_61_90,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 90, 99999999) as Age_91_plus,
/* bucket counts for 1 day to 10 days detail buckets */
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, -99999999, 2) as Age_1,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 1, 3) as Age_2,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 2, 4) as Age_3,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 3, 5) as Age_4,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 4, 6) as Age_5,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 5, 7) as Age_6,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 6, 8) as Age_7,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 7, 9) as Age_8,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 8, 10) as Age_9,
claims_aging_bucket(cv.processing_status, cv.post_date, cv.date_received, 9, 11) as Age_10,
XRPT_IPA_MASTER_XREF.IPA_PARENT_ID,
HSD_IPA_MASTER.NAME,
CV.USER_DEFINED_1,
CV.BATCH_NUMBER,
/* denied claims do not contribute to dollar amounts in any way */
decode(CV.CLAIM_STATUS,
'D', 0, CV.BILLED_AMT) billed_amt,
decode(CV.CLAIM_STATUS,
'D', 0, CV.TOTAL_BILLED_AMT) total_billed_amt,
decode(CV.CLAIM_STATUS,
'D', 0, CV.NET_AMT) net_amt,
CV.CLAIM_STATUS,
CV.PROCESSING_STATUS
FROM
XRPT_CLAIMS_OUTER_VW CV,
XRPT_IPA_MASTER_XREF XRPT_IPA_MASTER_XREF,
HSD_IPA_MASTER HSD_IPA_MASTER,
HSD_GROUP_MASTER HSD_GROUP_MASTER
WHERE
/* join conditions */
CV.SEQ_GROUP_ID = HSD_GROUP_MASTER.SEQ_GROUP_ID AND
CV.PCP_IPA_ID = XRPT_IPA_MASTER_XREF.IPA_ID AND
XRPT_IPA_MASTER_XREF.IPA_PARENT_ID = HSD_IPA_MASTER.IPA_ID

/* row selection criteria */
AND (CV.LINE_OF_BUSINESS LIKE '&LOB' or CV.LINE_OF_BUSINESS is null)
AND HSD_GROUP_MASTER.GROUP_ID LIKE '&HP'
AND XRPT_IPA_MASTER_XREF.IPA_PARENT_ID = '&IPA_ID'
AND (CV.USER_DEFINED_1 LIKE '&FundType' or CV.USER_DEFINED_1 is null)
AND (Upper(CV.BATCH_NUMBER) <> 'D' OR CV.BATCH_NUMBER IS NULL)
AND (
(CV.CLAIM_STATUS in ('P','A')
AND CV.PROCESSING_STATUS in ('P','C','N')
AND CV.POST_DATE BETWEEN '&Begin_Date' and '&End_Date')
OR (CV.CLAIM_STATUS in ('C', 'I')
AND CV.PROCESSING_STATUS in ('F','C','N')
AND CV.POST_DATE BETWEEN '&Begin_Date' and '&End_Date')
OR (CV.CLAIM_STATUS = 'D'
AND CV.PROCESSING_STATUS in ('F','P','C','N')
AND CV.POST_DATE BETWEEN '&Begin_Date' and '&End_Date')
OR (CV.CLAIM_STATUS in ('P','A','I','C','D')
AND CV.PROCESSING_STATUS in ('U','H')
AND CV.DATE_RECEIVED <= '&End_Date')
OR ( CV.DATE_RECEIVED <= '&End_Date'
AND CV.DTL_SEQ_CLAIM_ID IS NULL)
)
) details /* end of select statement pulling in detail rows */

GROUP BY
IPA_PARENT_ID,
Group_Processing_Status,
Seq_Claim_id

) counts /* end of select statement performing the counts */
WHERE
counts.ipa_parent_id = im.ipa_id

GROUP BY
im.name,
Group_Processing_Status

ORDER BY
Group_Processing_Status

 

[saw15]

It appears that the error occurs in the MAX portion of second select statement, it this will assist in trouble shooting...