Exception Handling raised by Oracle

[gulshanr]

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

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

To_char for BEg_eff_date is problem for us. Any help on this will be highly appreciated.

My scenario is as below

Code: Select all

input_file --> transformer   --> output file (All Valid Record)
                     ^
                     |
                   RDB Lookup

[WoMaWil]

Have you tried to write your data into a hash file and using the hashfile as lookup or to divide in Oracle the sting in a year, month and day part and then concatinating it.

[roy]

Hi,
You'll probably get what you need by writing a transform routine that changes the date formats.
transform routines can be placed in the lookup key column to wrap your source date data column and hence pass the date in the correct format for your lookup.

IHTH,