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

I've read this a couple of time, and am having a really hard time understanding some stuff. I'll put my coments and questions inline:

Mike 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 terms 'rows added' and 'view' are inconsistent in my mind. Do you mean you want the definition of the view (the CREATE VIEW statement) to be changed by a few rows, or do you mean you want a few rows of data added to the view?

Generally the term 'row' is used in reference to data. A view (with the exception of materialized view) only maps to data in a table, but does not have data of it's own, so you can not 'add a few rows' to a view, just a table.

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

Please expand on 'coming to us'.

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

You can not populate a view. I interpret this to mean 'populate a look up table', but with inconsistent terminology I'm not sure I interpret correctly.

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

Again - How are you getting the data? Is it a file that you could manipulate before load? If so, what is your operating system?

In *nix, it's a fairly simple matter to trim excess spaces out of a specific set of columns. (use cut, sed and paste, or awk, or perl) Perl is also one of mayn things that might help in Windows.

It is also possible that SQL Loader might be able to handle the problem.

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

Ther are several techniques possible, none of which are particularily difficult. Making the right recommendation will require a bit more info ... without OS and general description of the load technique, we could give a great solutions that won't work for you.

/Hans Received on Sun Oct 03 2004 - 20:18:43 CDT