Re: My DBA says this can't be done!

On Thu, 30 Sep 2004 11:03:18 GMT, Mike < none_at_nospan.com> wrote:

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

Of course it can be done. PL/SQL, a full-featured programming language, is fully capable of parsing such string fields.

What it can't do, is making the basic design choices for you. Define 1) How a null field should be handled,
2) What to do when 1,2,3... time fields occur.

Hand those definitions to a programmer and he should be done within hours.

• Kenneth Koenraadt