Ora Error 6502 & 6512 In SQL Query

[saw15]

The following query will start to retrieve results, and then give the following error:

ORA-6502: PL/SQL: numberic or value error; character string buffer too small ORA-6512: at Line 1.

This query did work in 8.0.6, started giving this error in 8.1.7. Again .. the query will start to retrieve data and then give the error.

No idea what is causing it.. any help is much appreciated.

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)
)

 

[maxpower1]

6512 is an ambiguous error. This happens when the value you try to assign to a variable is too large for the field. TO recover, type this at the sql> prompt: alter system flush shared_pool;

However, you need to figure out which value passed in is greater than the variable default size since you will get 6512 when the value that is trying to be assigned to
a variable is too big.

I had that problem when we used 8.0.5. Then when we moved to 8.0.6, it fixed the problem. You may want to call Oracle to see whether this is a known bug.

good luck