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:
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).
On Thu, 30 Sep 2004 11:03:18 GMT, Mike < n...@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:
>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.
> 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:
> 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
Write the logic on how you want to parse the field for every case. Then write a function in pl/sql and add the function to the view. Jim
In article <8tpnl0h2nf75hqji3jaejcsst0qkrtj...@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:
> 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';
> In article <8tpnl0h2nf75hqji3jaejcsst0qkrtj...@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:
>> 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;
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)));
On Thu, 30 Sep 2004 11:03:18 +0000, Mike wrote: > 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) .
This is your second post about "your DBA". Why isn't "your DBA" on this group and what are you trying to do? Are you trying to micro-manage "your DBA" or trying to take his place? As a DBA, I don't see any compelling reason to help you with that. If you don't trust "your DBA", get another one, which will make you more inclined to trust him or her. This is the first soap opera with an oracle DBA playing the lead role that I've ever seen. Have you considered getting Susan Lucci or Heather Locklear?
-- Artificial Intelligence is no match for natural stupidity.
>> 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) .
>This is your second post about "your DBA". Why isn't "your DBA" on this >group and what are you trying to do? Are you trying to micro-manage "your >DBA" or trying to take his place? As a DBA, I don't see any compelling >reason to help you with that. If you don't trust "your DBA", get another >one, which will make you more inclined to trust him or her. This is the >first soap opera with an oracle DBA playing the lead role that I've ever >seen. Have you considered getting Susan Lucci or Heather Locklear?
This was my first post to the board and I was just asking a question. I sent my DBA a set of requirments and he said it could not be done. It just seemed to me that Oracle 9.2 was more powerful then that. But I asked the question because I am ignorant to what Oracle can and can't do. I am on the application developer side and the code to solve this would be trivial in C, Java, VB etc. I could write this function in the program that calls this database but it would be very inefficient to call it for each of the rows I will be bringing in.
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.
> 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.
> 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.
Mladen Gogala <gog...@sbcglobal.net> wrote: > On Thu, 30 Sep 2004 11:03:18 +0000, Mike wrote:
> > 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) .
> This is your second post about "your DBA". Why isn't "your DBA" on this > group and what are you trying to do? Are you trying to micro-manage "your > DBA" or trying to take his place? As a DBA, I don't see any compelling > reason to help you with that. If you don't trust "your DBA", get another > one, which will make you more inclined to trust him or her.
And how best to do that? I guess you could hire a conslutant who will tell you your current DBA is doing everything wrong and you should fire him and hire [conslutant's friend] instead.
Or you could hang out here a bit, which may not turn one into a DBA but seems like a fine way learn enough to make an informed decision about supposed DBAs.
> This is the > first soap opera with an oracle DBA playing the lead role that I've ever > seen.
I wish I could claim as much.
Xho
-- -------------------- http://NewsReader.Com/ -------------------- Usenet Newsgroup Service $9.95/Month 30GB
> > 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) .
> This is your second post about "your DBA". Why isn't "your DBA" on this > group and what are you trying to do? Are you trying to micro-manage "your
Most DBA's are not on this group. They're too busy calculating hit ratios and avoiding being pinned down to responsibility for any problems.
> DBA" or trying to take his place? As a DBA, I don't see any compelling > reason to help you with that. If you don't trust "your DBA", get another
Micromanage? That seems a bit paranoid. People don't have to be a DBA to post here, just be on topic. Most of the time. Any DBA that would have to be worried about being unseated because of a post by a developer here probably deserves the worry.
> one, which will make you more inclined to trust him or her. This is the > first soap opera with an oracle DBA playing the lead role that I've ever > seen. Have you considered getting Susan Lucci or Heather Locklear?
You've never seen DBA soap operas? Jeez, where've you been?
Susan Lucci - always a developer, never a DBA :-)
Heather Locklear is now on bad prime-time TV, of course. A mile-high club or something.
