My DBA says this can't be done!
Mike
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
kennethkoenraadt
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
Jim Kennedy
"Mike" < no...@nospan.com> wrote in message
news:8tpnl0h2nf75hqji3...@4ax.com...
write a function in pl/sql and add the function to the view.
Jim
Rene Nyffenegger
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/
Rene Nyffenegger
>
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
>
> are you on 10g? If so, you could use the regular expressions:
Not the best of my days.... you already mentioned that you're on
9.2... escaped my eyes.
Mladen Gogala
> 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.
Mike
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.
Hans Forbrich
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
ctc...@hotmail.com
> 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
Joel Garry
> 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
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.
jg
--
@home.com is bogus.
"I think we need to get off this planet, because I'm afraid we're
going to destroy it." - Kelvin Lynn
http://www.sfgate.com/cgi-bin/article.cgi?file=/c/a/2004/10/04/MNGM393GPK1.DTL