My DBA says this can't be done!

I can't believe my request for a couple of rows to be added to a view can't be done through Oracle 9.2. But then again, I am application developer and don't know the full limitation of Oracle. So I would like to propose this question to this group, which is bound to know.

The main problem he is having is that the external data coming in to us (which we have no control over) is not consistently formatted. Sometimes this field is null, other times it is spaced weird, plus it can have multiple values. The field coming in is called RECURTIMES. It is a VarChar2 field that contains 3 sets of data that must be parsed. The data contained in this field are: DaysOfWeek, Qualifier, and TimesInWeek respectively, and an example would be as follows: 48 1 09001500;

• The 48 corresponds to DaysOfWeek which can be found using a lookup table (48 = "Mondays and Tuesdays"
• The 1 is a qualifier we ignore.
• The 09001500 is a recurrent time which simply would convert from 9:00 AM to 3:00PM. This is the only field that could have multiple values.

The bottom line is I would like the DBA to populate a view with a DaysOfWeek (e.g. "Mondays and Tuesdays") and a TimesInWeek (e.g. "from 9:00 AM to 3:00PM") field based on the above RECURTIMES values.

OK, so far no problem as long as the data comes in consistently, but the some times the RECURTIMES field is NULL or it could look as follows:

96 1 13002000;
62 1 00000300 04001000;
62 1 00000300 04001000 12001700;
62 1 ;

62 1         ;
62 1       07001500;
62 1     06001800;
31 1              21000600;

0 1 07001400 ;
62 1 07000600;

The DBA says all the inconsistent spaces are causing problems and that any function he writes can't handle more then one TimesInWeek field (e.g. can't handle 00000300 04001000) .

Any thoughts on if this can be handled? As far as I know there are no limitations on what we can do on the Oracle side( we can use functions, procedures or packages).

Thanks Received on Thu Sep 30 2004 - 06:03:18 CDT