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

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 30 Sep 2004 18:48:35 GMT
Message-ID: <slrnclol8d.128.rene.nyffenegger@zhnt60m34.netarchitects.com>

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

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

No, you aren't. I overlooked that. So here's something that should do it on 9.2 as well.

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 table_of_vc as table of Varchar2(8); /

create or replace Function SplitLine(

            p_line       in Varchar2,
            p_delim      in table_of_vc  default table_of_vc(' '),
            p_min_length in Number default 3) 
         return table_of_vc 

is
  /* v_delim_len containts the length of the used delimiter after each split */   v_delim_len number;

  v_split_pos  number;
  v_split_from_pos number := 1;
  v_split_str  varchar2(100);
  v_ret        table_of_vc := table_of_vc();

begin

  begin
    select
      pos , len into v_split_pos, v_delim_len     from (

      select 
        len,
        pos,
        row_number () over (order by pos) r
      from (
        select 
          length(column_value)               len,
          instr(p_line,column_value,1)       pos
        from 
          table(p_delim)
        ) where pos > 0

    ) where r = 1;

  exception
    when no_data_found then
      v_split_pos := 0;
    when others then
      return v_ret;
  end;

  while v_split_pos > 0 loop

    v_split_str := substr
    (p_line, v_split_from_pos, v_split_pos-v_split_from_pos);     if length(v_split_str) >= p_min_length then

      v_ret.extend;
      v_ret(v_ret.count) := v_split_str;

    end if;

    v_split_from_pos := v_split_pos + v_delim_len;

    begin

      select 
        pos , len into v_split_pos, v_delim_len
      from (
        select 
          len,
          pos,
          row_number () over (order by pos)    r
        from (
          select 
            length(column_value)                    len,
            instr(p_line,column_value,v_split_from_pos) pos
          from 
            table(p_delim)
          ) where pos > 0
      ) where r = 1;
    exception
      when no_data_found then
        v_split_pos := 0;

    end;

  end loop;

  v_split_str := substr(p_line,v_split_from_pos);   if length (v_split_str) >= p_min_length then     v_ret.extend;
    v_ret(v_ret.count) := v_split_str;
  end if;

  return v_ret;
end SplitLine;
/

create view v_incoming_data as
select
  substr (d,1,2) DaysOfWeek,
  column_value tm
from incoming_data,

   table (SplitLine(substr(d,5)));

select * from v_incoming_data;

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

Received on Thu Sep 30 2004 - 13:48:35 CDT