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

In article <8tpnl0h2nf75hqji3jaejcsst0qkrtjip9_at_4ax.com>, 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 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).

Mike,

are you on 10g? If so, you could use the regular expressions:

create table incoming_data (
  d varchar2(50)
);

insert into incoming_data values ('96 1    13002000');
insert into incoming_data values ('62 1 00000300 04001000');
insert into incoming_data values ('62 1 00000300 04001000 12001700');
insert into incoming_data values ('62 1 ');
insert into incoming_data values ('62 1         ');
insert into incoming_data values ('62 1       07001500');
insert into incoming_data values ('62 1     06001800');
insert into incoming_data values ('31 1              21000600');

create or replace type type_without_name   as object
  (a char(4), b char(4));
/

create or replace type table_2_without_name   as table of type_without_name;
/

create or replace function analyze_records   (in_line in incoming_data.d%type)
  return table_2_without_name
as
  ret table_2_without_name;
  s varchar2(50);
  p varchar2(50);
  a char(4);
  b char(4);
  i number:=1;
begin
  ret :=table_2_without_name();

  p := '[[:digit:]]{8}';
  s := regexp_substr(in_line, p);

  while s is not null loop
    ret.extend;
    ret(ret.count) :=

      type_without_name (
        substr(s,1,4),
        substr(s,5,4)
      );

    i := regexp_instr(in_line,p,i);
    i := i+8;

    s := regexp_substr(in_line, p,i);
  end loop;   

  return ret;   

end analyze_records;
/
show errors;

create view v_incoming_data as
select
  substr (d,1,2) DaysOfWeek,
  a from_time,
  b to_time
from incoming_data,

   table (analyze_records(d));

select * from v_incoming_data
  where to_time = '1500';  

DA FROM TO_T
-- ---- ----
62 0700 1500

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/