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 first published in April 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close