Exception Handling raised by Oracle
Posted: Wed Jul 14, 2004 12:35 pm
I am eager to know how to handle exception raised by Oracle.
We are using a file and then looking a database for some additional fields. We are experienceing a probelm in lookup. We are using RDB on VAX as lookup. We have problem with date field. Date field in RDB is having a date with year as 5 Digit while in input file its 4 Digit. Oracle does not understand the 5 digit year and we getting exception INVALID NUMBER (while using to_char function). Is there any way to by pass the bad record and continue with remaining record. We are using following query
To_char for BEg_eff_date is problem for us. Any help on this will be highly appreciated.
My scenario is as below
We are using a file and then looking a database for some additional fields. We are experienceing a probelm in lookup. We are using RDB on VAX as lookup. We have problem with date field. Date field in RDB is having a date with year as 5 Digit while in input file its 4 Digit. Oracle does not understand the 5 digit year and we getting exception INVALID NUMBER (while using to_char function). Is there any way to by pass the bad record and continue with remaining record. We are using following query
Code: Select all
SELECT MEMBER_MEDICARE.HIC_NUM,MEMBER_MEDICARE.HMO_ID,
MEMBER_MEDICARE.SUBSCBR_NUM,MEMBER_MEDICARE.MBR_SUFFIX,
TO_CHAR(MEMBER_MEDICARE.BEG_EFF_DATE, 'YYYYMMDD'),
TO_CHAR(MEMBER_MEDICARE.END_EFF_DATE ,'YYYYMMDD')
FROM MEMBER_MEDICARE MEMBER_MEDICARE
WHERE MEMBER_MEDICARE.HIC_NUM='431327774D' AND MEMBER_MEDICARE.HMO_ID='H0543'
AND (MEMBER_MEDICARE.BEG_EFF_DATE <= MEMBER_MEDICARE.END_EFF_DATE OR MEMBER_MEDICARE.END_EFF_DATE IS NULL)
AND TO_CHAR(MEMBER_MEDICARE.BEG_EFF_DATE,'YYYYMMDD') <= '20040701'
AND (TO_CHAR(MEMBER_MEDICARE.END_EFF_DATE,'YYYYMMDD') >= '20040731' OR MEMBER_MEDICARE.END_EFF_DATE IS NULL)
AND MEMBER_MEDICARE.BEG_EFF_DATE < SYSDATE
ORDER BY MEMBER_MEDICARE.BEG_EFF_DATE DESC
LIMIT TO 1 ROW
My scenario is as below
Code: Select all
input_file --> transformer --> output file (All Valid Record)
^
|
RDB Lookup