Dynamic time sequence query

Here's how to do a time-sequences query with no additional packages or procedures. -- or functions.

Suppose you need to see a full (10 minutes, half-hour, one hour, etc) time map for a given date with user_objects...

changes counter. And you need to do this in one SELECT statement, with no additional tables, views, packages, procedures, or functions. Is it possible? Yes, it is! Here's how:

  
------------------------------------------------------------------------------- 
-- @DYNAMIC_TIME_QUERY.SQL 
------------------------------------------------------------------------------- 
-- Author:  Dusan Djuric 
-- mail:    dusan.djuric@iii.hr 
-- http:    www.CroGuide.com 
-- 
-- Date:    24.01.2002 
-- 
-- Usage:   
--          1. Start SQL*Plus at directory where you put this script 
--          2. Connect to your schema 
--          2. At SQL prompt type "@DYNAMIC_TIME_QUERY.SQL" 
-- 
-- 
-- Purpose: Time-sequences query with no additional packages, procedures, 
-- or functions. 
-- 
-- Tested environment: 
--                    SQL*Plus: Release 8.1.7.0.0 
--                    Oracle8i  Release 8.1.7.2.1 
--                    Windows 98 SE client 
-- 
-- Explanations: 
-- 
--          This trick is based on these facts: 
--          1. One day has 86400 seconds so we need 1 to 5 figures 
--             number to generate all posible seconds of 1 day 
--          2. SELECT statement gives all row combinations
--             if tables are not joined in WHERE clause. 
--          3. Powerful MOD function (read Oracle reference) 
--             enables any wished increment of base number (time sequence 
--             in seconds) 
--          
--          I use the european DATE format. I didn't try any other. 
-- 
------------------------------------------------------------------------------- 

set linesize 300 
set pagesize 1000 
set long 2000 
set longchunksize 2000 
set numwidth 20 
set arraysize 10 
set maxdata 60000 

-- This is for proper date output format 
alter session set NLS_DATE_FORMAT = 'DD.MM.RRRR HH24:MI:SS'; 

-- VARIABLES 
VARIABLE START_TIME    CHAR(8); 
VARIABLE END_TIME      CHAR(8); 
VARIABLE TIME_FORMAT   VARCHAR2(10); 
VARIABLE TIME_SEQUENCE NUMBER; 
VARIABLE ENY_DATE      CHAR(10); 
VARIABLE DFORMAT       VARCHAR2(23); 

------------------------------------------------------------------------------- 
-- EXAMPLE 1 ---------------- SIMPLE TIME SEQUENCE QUERY ---------------------- 
------------------------------------------------------------------------------- 
--          This example gives 13 seconds time intervals between 15:53:13 
--          and 16:01:25 on 15.01.2001 
-- 
--          Be aware of :END_TIME := '16:01:25' setter. 
--          This interval is not in query output because the last 13 
--          seconds interval is bigger than :END_TIME limiter. 
-- 
--          If you set :TIME_FORMAT := 'HH24:MI' instead of 'HH24:MI:SS' 
--          you'll get just 1 minute intervals 
------------------------------------------------------------------------------- 

PROMPT -- EXAMPLE 1 ---------------- SIMPLE TIME SEQUENCE QUERY --------------- 

-- SETING VARIABLES 
BEGIN 
  :START_TIME    := '15:53:13';   -- Time format HH24:MI:SS 
  :END_TIME      := '16:01:25';   -- Time format HH24:MI:SS 
  :TIME_FORMAT   := 'HH24:MI:SS'; -- Time string formats HH24 , HH24:MI 
                                  -- or HH24:MI:SS for output 
  :TIME_SEQUENCE := 13;           -- always in seconds, e.g. 60*10 is 
                                  -- 10 minutes interval sequence 
  :ENY_DATE      := '15.01.2001'; -- eny date as CHAR in DD.MM.RRRR format 
END; 
/ 

-- COLUMN FORMATING 
column TSTRING format a10 heading 'TIME|(varchar2)' 
column TDATE   format a21 heading 'DTIME|(date)' 

SELECT UNIQUE 
--Time representation (string and date data type) 
  TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT) TSTRING, 
  TO_DATE(:ENY_DATE||' '||TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT),'DD.MM.RRRR '||:TIME_FORMAT) TDATE

FROM 
  ( 
    SELECT 
--Building numbers by length restriction 
      TO_NUMBER(DECODE(LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))), 

                       1,N1.N, 
                       2,N1.N||N2.N, 
                       3,N1.N||N2.N||N3.N, 
                       4,N1.N||N2.N||N3.N||N4.N, 
                       5,N1.N||N2.N||N3.N||N4.N||N5.N 
                      ) 
               ) SEC 
    FROM 
--5 number combinations 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 

      ) N1, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 

      ) N2, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 

      ) N3, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 

      ) N4, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 

      ) N5 
  ) INTERVAL 
WHERE 
-- No joins for N1-N5 subquerys to get 5 number combinations 
-- Min/Max number restriction 
  INTERVAL.SEC <= 
    ( 
      (TO_NUMBER(SUBSTR(:END_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:END_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:END_TIME,7,2))) 

      - 
      (TO_NUMBER(SUBSTR(:START_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:START_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:START_TIME,7,2))) 

    ) 
  AND 
-- See MOD function reference 
  MOD((INTERVAL.SEC + :TIME_SEQUENCE),:TIME_SEQUENCE) = 0 
ORDER BY TDATE; 

PROMPT -- END / EXAMPLE 1 ---------- SIMPLE TIME SEQUENCE QUERY --------------- 

PROMPT 

------------------------------------------------------------------------------- 
-- EXAMPLE 2 ------------ CHANGED USER OBJECTS half-hour TIME MAP ------------- 
------------------------------------------------------------------------------- 
--          This example count changed objects on half-hour time intervals 
--          between 00:00:00 and 23:59:59 on user shema objects last 
--          date changes 
------------------------------------------------------------------------------- 

PROMPT 

PROMPT 

PROMPT -- EXAMPLE 2 ---------- CHANGED USER OBJECTS half-hour TIME MAP -------- 

-- SETING VARIABLES 
BEGIN 
  :START_TIME    := '00:00:00'; 
  :END_TIME      := '23:59:59'; 
  :TIME_FORMAT   := 'HH24:MI:SS'; 
  :TIME_SEQUENCE := 60*30; -- HALF HOUR 
  -- user_object changes last date 
  SELECT TO_CHAR(MAX(LAST_DDL_TIME),'DD.MM.RRRR') 
  INTO   :ENY_DATE 
  FROM   USER_OBJECTS; 
END; 
/ 

-- COLUMN FORMATING 
column TSTRING format a10 heading 'TIME|(varchar2)' 
column TDATE   format a21 heading 'DTIME|(date)' 
column ALTERED format a7 heading 'ALTERED|' 

SELECT 
  TSEQ.TSTRING TSTRING, 
  TSEQ.TDATE TDATE, 
  DECODE(COUNT(O.OBJECT_ID),0,NULL,COUNT(O.OBJECT_ID)) ALTERED 
FROM 
( 
SELECT UNIQUE 
--Time representation (string and date data type) 
  TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT) TSTRING, 
  TO_DATE(:ENY_DATE||' '||TO_CHAR(TO_DATE(:START_TIME,'HH24:MI:SS')+INTERVAL.SEC/(24*60*60),:TIME_FORMAT),'DD.MM.RRRR '||:TIME_FORMAT) TDATE

FROM 
  ( 
    SELECT 
--Building numbers by length restriction 
      TO_NUMBER(DECODE(LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))), 

                       1,N1.N, 
                       2,N1.N||N2.N, 
                       3,N1.N||N2.N||N3.N, 
                       4,N1.N||N2.N||N3.N||N4.N, 
                       5,N1.N||N2.N||N3.N||N4.N||N5.N 
                      ) 
               ) SEC 
    FROM 
--5 number combinations 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 1 

      ) N1, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 2 

      ) N2, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 3 

      ) N3, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 4 

      ) N4, 
      ( 
        SELECT TO_NUMBER(NULL) N FROM DUAL UNION 
        SELECT 0 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 1 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 2 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 3 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 4 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 5 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 6 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 7 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 8 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 UNION 

        SELECT 9 N FROM DUAL WHERE LENGTH(TO_CHAR((TO_DATE(:END_TIME,'HH24:MI:SS') - TO_DATE(:START_TIME,'HH24:MI:SS'))*(60*60*24))) >= 5 

      ) N5 
  ) INTERVAL 
WHERE 
-- No joins for N1-N5 subquerys to get 5 number combinations 
-- Min/Max number restriction 
  INTERVAL.SEC <= 
    ( 
      (TO_NUMBER(SUBSTR(:END_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:END_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:END_TIME,7,2))) 

      - 
      (TO_NUMBER(SUBSTR(:START_TIME,1,2))*60*60 + TO_NUMBER(SUBSTR(:START_TIME,4,2))*60 + TO_NUMBER(SUBSTR(:START_TIME,7,2))) 

    ) 
  AND 
-- See MOD function reference 
  MOD((INTERVAL.SEC + :TIME_SEQUENCE),:TIME_SEQUENCE) = 0 
) TSEQ, 
  USER_OBJECTS O 
WHERE 
  TRUNC(O.LAST_DDL_TIME(+),'MI') BETWEEN TSEQ.TDATE-(:TIME_SEQUENCE/(24*60*60)) AND TSEQ.TDATE 
GROUP BY 
  TSEQ.TSTRING, 
  TSEQ.TDATE; 

PROMPT -- END / EXAMPLE 2 ------ CHANGED USER OBJECTS half-hour TIME MAP ------

PROMPT 


This was last published in April 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close