Re: Case for Index Organized table?

From: Michael Haddon <m.haddon_at_tx.rr.com>
Date: Tue, 24 Apr 2007 15:52:06 -0600
Message-ID: <462E7C06.3030406@tx.rr.com>

Ram -

I cannot understand the necessity to store this data at all, I have had several times that developers have come to me with sql that they say is best. Usually we can give them what they need if we just understand the problem,.. you are trying to create a dataset that doesn't exist, hence the need for a table that is very bad.

What I would do is this,

create an object with a date column in it, then define a table of that object.
Then create a function that will return the table object as every second for the date that is passed.

To prevent too much conversion pass a character representation of the date you are interested in,..

For example,..

drop type daysecs_tab;
drop type daysecs_obj;

create or replace type daysecs_obj
as object (
virt_secs date)
/

create or replace type daysecs_tab
as table of daysecs_obj
/

CREATE OR REPLACE FUNCTION daysecs (TDAY IN varchar2) -- day in character form ddmmyyyy
RETURN daysecs_tab
PIPELINED
IS
BEGIN

        FOR secs_in_day IN 0..86399
        LOOP
                PIPE ROW(daysecs_obj(to_date(TDAY||' 
'||secs_in_day,'ddmmyyyy sssss')));
        END LOOP;

EXCEPTION
    WHEN OTHERS THEN

         RAISE_APPLICATION_ERROR
              (-20000, 'SQL Code: '|| SQLCODE || ' SQL Error Message: ' 

|| SQLERRM);
END daysecs;
/

then call it with this syntax and outer join with the reports table, rows with no report are what you are looking for right??

select to_char(VIRT_SECS,'DD-MON-YYYY HH24:MI:SS') from table (select cast(DAYSECS('01012007') as DAYSECS_TAB) from dual);

TO_CHAR(VIRT_SECS,'D

---

01-JAN-2007 00:00:00
01-JAN-2007 00:00:01
01-JAN-2007 00:00:02
01-JAN-2007 00:00:03
01-JAN-2007 00:00:04
01-JAN-2007 00:00:05
01-JAN-2007 00:00:06
01-JAN-2007 00:00:07
01-JAN-2007 00:00:08

The data is not stored at all in this example but the rows can be part of an outer join to indicate no report for that second.

Hope this helps.

Mike

> Example:
> I have an instrument that measures something (furnace temp, motor speed,
> etc) every second. Occasionally it fails to report the value. Find each
> second that it didn't report between date_x and date_y. What's the best
> way to answer that question?
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 24 2007 - 16:52:06 CDT