ORA-01841: Creation of dates bc

It took me some time to find out what the problem of the following statement is. Ok, I did not try too hard at it 🙊.

select to_date('-0001-01-01', 'YYYY-MM-DD') from DUAL;

It simply misses the sign sign 😉.

select to_date('-0001-01-01', 'SYYYY-MM-DD') from DUAL;

Search tables of schemas for a string

declare
   -- constants
   C_SEARCH_PATTERN                constant varchar2(32767 char) := 'search_pattern';
   C_SCHEMA_INCLUSION_PATTERN      constant varchar2(32767 char) := 'SDM_DATA';
   C_LIST_NO_HITS                  constant boolean := false;
   C_TAB_NAME_EXCLUSION_PATTERN    constant varchar2(32767 char)
      := '(^(BCKP|BACKUP|BAK|BCK|BKP)_|_ERROR_)' ;
   C_TAB_NAME_INCLUSION_PATTERN    constant varchar2(32767 char) := '.*';
   C_COL_NAME_EXCLUSION_PATTERN    constant varchar2(32767 char)
      := '(^(BCKP|BACKUP|BAK|BCK|BKP)_|_ERROR_)' ;
   C_COL_NAME_INCLUSION_PATTERN    constant varchar2(32767 char) := '.*';
   C_DATA_TYPE_EXCLUSION_PATTERN   constant varchar2(32767 char)
                                               := 'LONG'; -- LONG is a nuisance
   C_DATA_TYPE_INCLUSION_PATTERN   constant varchar2(32767 char)
      := '^(CHAR|CLOB|NCHAR|NCLOB|NVARCHAR2|VARCHAR2)$' ;
   C_PARALLEL_DEGREE               constant integer := 4;
   C_PLACEHOLDER_SCHEMA_NAME       constant char(6 char) := 'çPHSNç';
   C_PLACEHOLDER_TABLE_NAME        constant char(6 char) := 'çPHTNç';
   C_PLACEHOLDER_COLUMN_NAME       constant char(6 char) := 'çPHcNç';
   C_STATEMENT_STUB                constant clob
      := 'select /*+ parallel(' ||
         C_PARALLEL_DEGREE ||
         ') */' ||
         CHR(10)||
         '      COUNT(*)' ||
         CHR(10)||
         '  from ' ||
         C_PLACEHOLDER_SCHEMA_NAME ||
         '.' ||
         C_PLACEHOLDER_TABLE_NAME ||
         CHR(10)||
         ' where regexp_like(' ||
         C_PLACEHOLDER_COLUMN_NAME ||
         ', :C_SEARCH_PATTERN)' ;

   -- variables
   V_STATEMENT                              clob;
   V_NUMBER_OF_HITS                         integer := 0;
begin
   for REC
      in (
            with TC as
                    (select OWNER as SCHEMA_NAME
                          , TABLE_NAME
                          , COLUMN_NAME
                          , DATA_TYPE
                       from ALL_TAB_COLS
                      where REGEXP_LIKE(OWNER, C_SCHEMA_INCLUSION_PATTERN)
                        and not REGEXP_LIKE(
                                            TABLE_NAME
                                          , C_TAB_NAME_EXCLUSION_PATTERN
                                           )
                        and REGEXP_LIKE(
                                        TABLE_NAME
                                      , C_TAB_NAME_INCLUSION_PATTERN
                                       )
                        and not REGEXP_LIKE(
                                            COLUMN_NAME
                                          , C_COL_NAME_EXCLUSION_PATTERN
                                           )
                        and REGEXP_LIKE(
                                        COLUMN_NAME
                                      , C_COL_NAME_INCLUSION_PATTERN
                                       )
                        and not REGEXP_LIKE(
                                            DATA_TYPE
                                          , C_DATA_TYPE_EXCLUSION_PATTERN
                                           )
                        and REGEXP_LIKE(
                                        DATA_TYPE
                                      , C_DATA_TYPE_INCLUSION_PATTERN
                                       )
                        and HIDDEN_COLUMN = 'NO')
              select TC.*
                from TC
                     left outer join ALL_VIEWS V
                        on TC.SCHEMA_NAME = V.OWNER
                       and TC.TABLE_NAME = V.VIEW_NAME
               where V.OWNER is null
            order by TC.SCHEMA_NAME asc
                   , TC.TABLE_NAME asc
                   , TC.COLUMN_NAME asc
         )
   loop
      begin

         V_STATEMENT      :=
            REPLACE(
               REPLACE(
                  REPLACE(
                          C_STATEMENT_STUB
                        , C_PLACEHOLDER_SCHEMA_NAME
                        , REC.SCHEMA_NAME
                         )
                , C_PLACEHOLDER_TABLE_NAME
                , REC.TABLE_NAME)
             , C_PLACEHOLDER_COLUMN_NAME
             , REC.COLUMN_NAME);

         execute immediate V_STATEMENT
            into V_NUMBER_OF_HITS
            using C_SEARCH_PATTERN;

         if C_LIST_NO_HITS or V_NUMBER_OF_HITS > 0 then
             DBMS_OUTPUT.PUT(
                             'Schema: ' ||
                             RPAD(REC.SCHEMA_NAME, 30)
                            );
             DBMS_OUTPUT.PUT(
                             'Table: ' ||
                             RPAD(REC.TABLE_NAME, 30)
                            );
             DBMS_OUTPUT.PUT(
                             ', Column: ' ||
                             RPAD(REC.COLUMN_NAME, 30)
                            );
             DBMS_OUTPUT.PUT(
                             ', Type: ' ||
                             RPAD(REC.DATA_TYPE, 30)
                            );
             DBMS_OUTPUT.PUT_LINE(
                                  ', Number of hits: ' ||
                                  V_NUMBER_OF_HITS
                                 );
         end if;
      exception
         when others
         then
            DBMS_OUTPUT.PUT_LINE(
               CHR(10)||
               'Following statement failed with $C_SEARCH_PATTERN = ''' ||
               C_SEARCH_PATTERN ||
               '''. ' ||
               CHR(10)||
               V_STATEMENT ||
               CHR(10));
            DBMS_OUTPUT.PUT_LINE('*** Error stack ***');
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
            DBMS_OUTPUT.PUT_LINE('*** Error backtrace ***');
            DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
            DBMS_OUTPUT.PUT_LINE('');
      end;
   end loop;
end;
/

ORA-01007

see

How to find the records with multiple value combinations of columns of a distinct superset combination of columns

Let's say you want to check whether the discount of online shopping cart is singular for the items of a shopping cart (Sounds strange but I am affraid this is real life in that very moment of my life. I personally feel this is a design flaw as there is a information of shopping cart level in a table of item level.)

with BASE
     as (  select distinct [DISTINCT COLUMNS]
             from [TABLE NAME]
            where [FILTER])
   , TUPS
     as (  select [SUBSET OF DISTINCT COLUMNS]
             from BASE
         group by [SUBSET OF DISTINCT COLUMNS]
           having count(*) > 1)
  select /*+ parallel(4) */
         B.*
    from BASE B
         inner join TUPS on (B.[SUBSET OF DISTINCT COLUMNS]) in ((TUPS.[SUBSET OF DISTINCT COLUMNS]))
order by B.[SUBSET OF DISTINCT COLUMNS];

Put together JDBC URL for thin driver

An Oracle database server does not know the port the listener listens on, so we cannot retrieve it from the Oracle instance.

with BASE
     as (select sys_context('USERENV', 'DB_NAME') as DB_NAME
              , sys_context('USERENV', 'DB_UNIQUE_NAME') as DB_UNIQUE_NAME
              , sys_context('USERENV', 'DB_DOMAIN') as DB_DOMAIN
              , sys_context('USERENV', 'INSTANCE_NAME') as INSTANCE_NAME
              , sys_context('USERENV', 'ORACLE_HOME') as ORACLE_HOME
              , sys_context('USERENV', 'SERVER_HOST') as SERVER_HOST
              , sys_context('USERENV', 'SERVICE_NAME') as SERVICE_NAME
              , sys_context('USERENV', 'SESSION_USER') as SESSION_USER
           from dual)
select BASE.*
     , 'jdbc:oracle:thin:@' || SERVER_HOST || ':<port>:' || DB_NAME
          as JDBC_CONNECT_STRING_SID
     , 'jdbc:oracle:thin:@//' || SERVER_HOST || '<port>/' || SERVICE_NAME
          as JDBC_CONNECT_STRING_SERVICE
  from BASE;

You can look in the file

${ORACLEHOME}/Network/Admin/tnsnames.ora

of your local Oracle Home. If there is nothing in, you probably use LDAP. You check that in

${ORACLEHOME}/Network/Admin/sqlnet.ora

. There is DBMS_LDAP to access LDAP services from within PL/SQL. However, I do not know how to retrieve the data.

PL/SQL: get trace information

DBMS_OUTPUT.PUT_LINE('*** Error stack ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT_LINE('*** Error backtrace ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('*** Call stack ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);

Get information on locks

select C.OWNER
     , C.OBJECT_NAME
     , C.OBJECT_TYPE
     , B.SID
     , B.SERIAL#
     , B.STATUS
     , B.OSUSER
     , B.MACHINE
     , case A.LOCKED_MODE
          when 1 then 'No Lock'
          when 2 then 'Row Share'
          when 3 then 'Row Exclusive'
          when 4 then 'Shared Table'
          when 5 then 'Shared Row Exclusive'
          when 6 then 'Exclusive'
       end
          as LOCKED_MODE
     , case L.type
          when 'BL' then 'Buffer Cache Management (PCM lock)'
          when 'CF' then 'Controlfile Transaction'
          when 'CI' then 'Cross Instance Call'
          when 'CU' then 'Bind Enqueue'
          when 'DF' then 'Data File'
          when 'DL' then 'Direct Loader'
          when 'DM' then 'Database Mount'
          when 'DR' then 'Distributed Recovery'
          when 'DX' then 'Distributed Transaction'
          when 'FS' then 'File Set'
          when 'IN' then 'Instance Number'
          when 'IR' then 'Instance Recovery'
          when 'IS' then 'Instance State'
          when 'IV' then 'Library Cache Invalidation'
          when 'JQ' then 'Job Queue'
          when 'KK' then 'Redo Log Kick'
          when 'LA' then 'Library Cache Lock'
          when 'LB' then 'Library Cache Lock'
          when 'LC' then 'Library Cache Lock'
          when 'LD' then 'Library Cache Lock'
          when 'LE' then 'Library Cache Lock'
          when 'LF' then 'Library Cache Lock'
          when 'LG' then 'Library Cache Lock'
          when 'LH' then 'Library Cache Lock'
          when 'LI' then 'Library Cache Lock'
          when 'LJ' then 'Library Cache Lock'
          when 'LK' then 'Library Cache Lock'
          when 'LL' then 'Library Cache Lock'
          when 'LM' then 'Library Cache Lock'
          when 'LN' then 'Library Cache Lock'
          when 'LO' then 'Library Cache Lock'
          when 'LP' then 'Library Cache Lock'
          when 'MM' then 'Mount Definition'
          when 'MR' then 'Media Recovery'
          when 'NA' then 'Library Cache Pin'
          when 'NB' then 'Library Cache Pin'
          when 'NC' then 'Library Cache Pin'
          when 'ND' then 'Library Cache Pin'
          when 'NE' then 'Library Cache Pin'
          when 'NF' then 'Library Cache Pin'
          when 'NG' then 'Library Cache Pin'
          when 'NH' then 'Library Cache Pin'
          when 'NI' then 'Library Cache Pin'
          when 'NJ' then 'Library Cache Pin'
          when 'NK' then 'Library Cache Pin'
          when 'NL' then 'Library Cache Pin'
          when 'NM' then 'Library Cache Pin'
          when 'NN' then 'Library Cache Pin'
          when 'NO' then 'Library Cache Pin'
          when 'NP' then 'Library Cache Pin'
          when 'NQ' then 'Library Cache Pin'
          when 'NR' then 'Library Cache Pin'
          when 'NS' then 'Library Cache Pin'
          when 'NT' then 'Library Cache Pin'
          when 'NU' then 'Library Cache Pin'
          when 'NV' then 'Library Cache Pin'
          when 'NW' then 'Library Cache Pin'
          when 'NX' then 'Library Cache Pin'
          when 'NY' then 'Library Cache Pin'
          when 'NZ' then 'Library Cache Pin'
          when 'PF' then 'Password File'
          when 'PI' then 'Parallel Slaves'
          when 'PR' then 'Process Startup'
          when 'PS' then 'Parallel Slave Synchronization'
          when 'QA' then 'Row Cache Lock'
          when 'QB' then 'Row Cache Lock'
          when 'QC' then 'Row Cache Lock'
          when 'QD' then 'Row Cache Lock'
          when 'QE' then 'Row Cache Lock'
          when 'QF' then 'Row Cache Lock'
          when 'QG' then 'Row Cache Lock'
          when 'QH' then 'Row Cache Lock'
          when 'QI' then 'Row Cache Lock'
          when 'QJ' then 'Row Cache Lock'
          when 'QK' then 'Row Cache Lock'
          when 'QL' then 'Row Cache Lock'
          when 'QM' then 'Row Cache Lock'
          when 'QN' then 'Row Cache Lock'
          when 'QO' then 'Row Cache Lock'
          when 'QP' then 'Row Cache Lock'
          when 'QQ' then 'Row Cache Lock'
          when 'QR' then 'Row Cache Lock'
          when 'QS' then 'Row Cache Lock'
          when 'QT' then 'Row Cache Lock'
          when 'QU' then 'Row Cache Lock'
          when 'QV' then 'Row Cache Lock'
          when 'QW' then 'Row Cache Lock'
          when 'QX' then 'Row Cache Lock'
          when 'QY' then 'Row Cache Lock'
          when 'QZ' then 'Row Cache Lock'
          when 'RT' then 'Redo Thread'
          when 'SC' then 'System Commit number'
          when 'SM' then 'SMON synchronization'
          when 'SN' then 'Sequence Number'
          when 'SQ' then 'Sequence Enqueue'
          when 'SR' then 'Synchronous Replication'
          when 'SS' then 'Sort Segment'
          when 'ST' then 'Space Management Transaction'
          when 'SV' then 'Sequence Number Value'
          when 'TA' then 'Transaction Recovery'
          when 'TM' then 'DML Enqueue'
          when 'TS' then 'Table Space (or Temporary Segment)'
          when 'TT' then 'Temporary Table'
          when 'TX' then 'Transaction'
          when 'UL' then 'User-defined Locks'
          when 'UN' then 'User Name'
          when 'US' then 'Undo segment Serialization'
          when 'WL' then 'Writing redo Log'
          when 'XA' then 'Instance Attribute Lock'
          when 'XI' then 'Instance Registration Lock'
       end
          as type
  from V$LOCKED_OBJECT A
       inner join V$SESSION B on A.SESSION_ID = B.SID
       inner join DBA_OBJECTS C on A.OBJECT_ID = C.OBJECT_ID
       inner join GV$LOCK L on B.SID = L.SID;

Find indexes that are useless or not of the proper type

There is a rule of thumbs according to which a normal non-unique index should be quite selective to be taken by the CBO. Selective means rather to return only rather less than 5 % than 10 % of the data.
On the other hand, bitmaps are good on columns that are not selective at all but you have several of those on one table and queries restrict on a variety of combinations on bitmap indexed columns which in combination are selective. The more selective a bitmap indexed column is the more bloated gets the index.
Following query can help to find indexes that should be re-considered.

with IND_BASE
     as (select *
           from ALL_INDEXES
          where UNIQUENESS = 'NONUNIQUE' -- UNIQUE indexes are per definition
                                         -- very selective
            and OWNER not in ('SYS'
                            , 'OPS$DEZA'
                            , 'SYSTEM'
                            , 'XDB'))
   , COL_BASE
     as (select C.*     --  density = 1 / (Number of distinct NON null values)
           from ALL_TAB_COLS C
                inner join IND_BASE I
                   on C.OWNER = I.TABLE_OWNER
                  and C.TABLE_NAME = I.TABLE_NAME)
   , COLS_INVALID_DENS
     as (select *
           from COL_BASE
          /* we must rule out indexes with columns without or with invalid
             density information */
          where DENSITY is not null
             /* 0 or negative density is actually not defined but at least 0s
                occur for some obscure reason */
             or DENSITY > 0)
   , IND_COL_BASE
     as (select C.*
           from ALL_IND_COLUMNS C
                inner join IND_BASE I
                   on C.TABLE_OWNER = I.TABLE_OWNER
                  and C.TABLE_NAME = I.TABLE_NAME
                  and C.INDEX_OWNER = I.OWNER
                  and C.INDEX_NAME = I.INDEX_NAME)
   , IND_INVALID_DENS
     as (  select I.TABLE_OWNER
                , I.TABLE_NAME
                , I.INDEX_OWNER
                , I.INDEX_NAME
             from IND_COL_BASE I
                  inner join COLS_INVALID_DENS C
                     on I.TABLE_OWNER = C.OWNER
                    and I.TABLE_NAME = C.TABLE_NAME
                    and I.COLUMN_NAME = C.COLUMN_NAME
         group by I.TABLE_OWNER
                , I.TABLE_NAME
                , I.INDEX_OWNER
                , I.INDEX_NAME)
   , IND_VALID_BASE
     as (select I.*
           from IND_BASE I
                left outer join IND_INVALID_DENS IV
                   on I.TABLE_OWNER = IV.TABLE_OWNER
                  and I.TABLE_NAME = IV.TABLE_NAME
                  and I.OWNER = IV.INDEX_OWNER
                  and I.INDEX_NAME = IV.INDEX_NAME
          where IV.TABLE_OWNER is null)
   , IND_VALID_COL
     as (select C.*, I.INDEX_TYPE
           from IND_VALID_BASE I
                inner join IND_COL_BASE C
                   on I.TABLE_OWNER = C.TABLE_OWNER
                  and I.TABLE_NAME = C.TABLE_NAME
                  and I.OWNER = C.INDEX_OWNER
                  and I.INDEX_NAME = C.INDEX_NAME)
   , IND_COL_DENS
     as (select I.*, C.DENSITY as DENS
           from IND_VALID_COL I
                inner join COL_BASE C
                   on I.TABLE_OWNER = C.OWNER
                  and I.TABLE_NAME = C.TABLE_NAME
                  and I.COLUMN_NAME = C.COLUMN_NAME)
   , IND_DENS
     as (  select INDEX_OWNER
                , INDEX_NAME
                , TABLE_OWNER
                , TABLE_NAME
                , INDEX_TYPE
                , exp(sum(ln(DENS))) as DENS -- instead of multiplying numbers,
                                             -- their logarithms can be added, 
                                             -- and the result exponentiated: 
                                             -- only works if the density > 0
             from IND_COL_DENS
         group by INDEX_OWNER
                , INDEX_NAME
                , TABLE_OWNER
                , TABLE_NAME
                , INDEX_TYPE)
select /*+ parallel(4) */
      'Please consider converting the index into an normal index'
          as COMMENTS
     , I.*
  from IND_DENS I
 where I.INDEX_TYPE = 'BITMAP'
   and DENS <= 0.1
union all
select /*+ parallel(4) */
      'Please consider removing the index or converting it into an bitmap index, if it is a compound index into several bitmap indexes'
          as COMMENTS
     , I.*
  from IND_DENS I
 where I.INDEX_TYPE = 'NORMAL'
   and DENS >= 0.1;

Find indexes that possibly can be integrated into others

Following query returns all single column indexes (sci) of which the attribute is part of a multi column index (cmi) but not on first position. If the order of the cmi is changed such that the respective column is on the first position, the sci gets obsolete. This is only possible if the affected table is not queried with restrictions on the former first position column of the cmi.

with IND_COL_BASE
     as (select *
           from ALL_IND_COLUMNS
          where TABLE_OWNER not in ('SYS'
                                  , 'OPS$DEZA'
                                  , 'SYSTEM'
                                  , 'XDB'))
   , IND_COL_CNT
     as (  select TABLE_OWNER
                , TABLE_NAME
                , INDEX_OWNER
                , INDEX_NAME
                , count(*) as ANZ
             from IND_COL_BASE
         group by TABLE_OWNER
                , TABLE_NAME
                , INDEX_OWNER
                , INDEX_NAME)
   , IND_COL_1_ONLY
     as (select TABLE_OWNER
              , TABLE_NAME
              , INDEX_OWNER
              , INDEX_NAME
           from IND_COL_CNT
          where ANZ = 1)
   , IND_COL_P1
     as (select B.*
           from IND_COL_BASE B
                inner join IND_COL_1_ONLY C
                   on B.TABLE_OWNER = C.TABLE_OWNER
                  and B.TABLE_NAME = C.TABLE_NAME
                  and B.INDEX_OWNER = C.INDEX_OWNER
                  and B.INDEX_NAME = C.INDEX_NAME)
   , NON_UNIQUE_INDEXES
     as (select TABLE_OWNER
              , TABLE_NAME
              , OWNER
              , INDEX_NAME
           from ALL_INDEXES
          where UNIQUENESS = 'NONUNIQUE'
            and TABLE_OWNER not in ('SYS'
                                  , 'OPS$DEZA'
                                  , 'SYSTEM'
                                  , 'XDB'))
   , IND_COL_P1_NON_KEY
     as (select I.*
           from IND_COL_P1 I
                inner join NON_UNIQUE_INDEXES N
                   on I.TABLE_OWNER = N.TABLE_OWNER
                  and I.TABLE_NAME = N.TABLE_NAME
                  and I.INDEX_OWNER = N.OWNER
                  and I.INDEX_NAME = N.INDEX_NAME)
  select /*+ parallel(4) */
        *
    from IND_COL_P1_NON_KEY IC1
         inner join IND_COL_BASE IC
            on IC1.TABLE_OWNER = IC.TABLE_OWNER
           and IC1.TABLE_NAME = IC.TABLE_NAME
           and IC1.INDEX_OWNER = IC.INDEX_OWNER
           and IC1.COLUMN_NAME = IC.COLUMN_NAME
           and IC1.INDEX_NAME != IC.INDEX_NAME
           and IC1.COLUMN_POSITION != IC.COLUMN_POSITION
order by IC1.TABLE_OWNER asc
       , IC1.TABLE_NAME asc
       , IC1.INDEX_OWNER asc
       , IC1.INDEX_NAME asc
       , IC1.COLUMN_NAME asc;

Check whether "something" is of number type

There are in the net several proposals for this. One is to write a proper PL/SQL function that tries to assign a given value to a number type variable. It returns TRUE if no exception is raised and catches the exception for the data type incompatibility and returns false.
However, within SQL this is inefficient as it will make a context switch for every row the function is applied to. Maybe the use of regular expressions with regexp_like is more efficient. However, it means that notation conventions have to be clear and met. In the following I will show a solution for following conventions.

• A negative number is marked by a - as the first character
• No white space character are allowed
• No grouping separators allowed (for thousands and so on)
• The decimal separator is a .
• Fractions always need at least one digit after the decimal separator
• Before decimal separator digits are optional, e.g. -0.12 equals -.12

Regular expression:

^-?[0-9]*([.][0-9]+)?$

SQL example:

select *
  from DUAL
 where regexp_like(
            '-.3',
            '^-?[0-9]*([.][0-9]+)?$')

Historicisation tests: time gaps and overlappings

--
--
@set resultset name OLAPs/GAPs ; -- Client command DbVis setting the name of the result tab
with PM
     as (select 1 as TIME_GRANULARITY_IN_DAYS
               ,1 as BOUNDARY_INCLUSIVE_IS_0
           from dual)
    ,BASE
     as (    select [KEY_COLUMNS]
                   ,[FROM_COLUMN]
                   ,case
                       when [UNTIL_COLUMN] >=   to_date('9999-12-31 23:59:59'
                                                       ,'yyyy-mm-dd hh24:mi:ss')
                                              - PM.TIME_GRANULARITY_IN_DAYS
                       then   to_date('9999-12-31 23:59:59'
                                     ,'yyyy-mm-dd hh24:mi:ss')
                            - PM.TIME_GRANULARITY_IN_DAYS
                       else [UNTIL_COLUMN]
                    end                      as [UNTIL_COLUMN]
                   ,lead([FROM_COLUMN]) over (partition by [KEY_COLUMNS]
                                              order by [FROM_COLUMN] asc
                                                      ,[UNTIL_COLUMN] asc)
                                             as [FROM_COLUMN]_NEXT
                   ,lead(case
                            when [UNTIL_COLUMN] >=   to_date('9999-12-31 23:59:59'
                                                            ,'yyyy-mm-dd hh24:mi:ss')
                                                   - PM.TIME_GRANULARITY_IN_DAYS
                            then   to_date('9999-12-31 23:59:59'
                                          ,'yyyy-mm-dd hh24:mi:ss')
                                 - PM.TIME_GRANULARITY_IN_DAYS
                            else [UNTIL_COLUMN]
                         end) over (partition by [KEY_COLUMNS]
                                    order by [FROM_COLUMN] asc
                                            ,[UNTIL_COLUMN] asc)
                                             as [UNTIL_COLUMN]_NEXT
                   ,PM.TIME_GRANULARITY_IN_DAYS
                   ,PM.BOUNDARY_INCLUSIVE_IS_0
               from [TABLE_NAME] -- default of the columns
         inner join PM
                 on 1 = 1
              where 1 = 1
                and (   (    PM.BOUNDARY_INCLUSIVE_IS_0 = 0
                         and   [UNTIL_COLUMN]
                             - [FROM_COLUMN] >= PM.TIME_GRANULARITY_IN_DAYS)
                     or (        PM.BOUNDARY_INCLUSIVE_IS_0 != 0 -- to handle NULL
                         and [UNTIL_COLUMN] > [FROM_COLUMN] -- for boundary excluding, time granularity does not make sense
                         and 1 = 1))
                and 1 = 1)
    ,GAPS
     as (select 'Gap' as FINDING
               ,[KEY_COLUMNS]
               ,[FROM_COLUMN]
               ,[UNTIL_COLUMN]
               ,[FROM_COLUMN]_NEXT
               ,[UNTIL_COLUMN]_NEXT
           from BASE
          where 1 = 1
            and [FROM_COLUMN]_NEXT is not null
            and (   (    BOUNDARY_INCLUSIVE_IS_0 = 0
                     and   [FROM_COLUMN]_NEXT
                         - [UNTIL_COLUMN] > TIME_GRANULARITY_IN_DAYS)
                 or (    BOUNDARY_INCLUSIVE_IS_0 != 0 -- to handle NULL
                     and [FROM_COLUMN]_NEXT > [UNTIL_COLUMN] -- for boundary excluding, time granularity does not make sense
                     and 1 = 1))
            and 1 = 1)
    ,OLAPS
     as (select 'Overlap' as FINDING
               ,[KEY_COLUMNS]
               ,[FROM_COLUMN]
               ,[UNTIL_COLUMN]
               ,[FROM_COLUMN]_NEXT
               ,[UNTIL_COLUMN]_NEXT
           from BASE
          where 1 = 1
            and [FROM_COLUMN]_NEXT is not null
            and (   (    BOUNDARY_INCLUSIVE_IS_0 = 0
                     and   [UNTIL_COLUMN]
                         + TIME_GRANULARITY_IN_DAYS >= [FROM_COLUMN]_NEXT)
                 or (    BOUNDARY_INCLUSIVE_IS_0 != 0 -- to handle NULL
                     and [UNTIL_COLUMN] > [FROM_COLUMN]_NEXT -- for boundary excluding, time granularity does not make sense
                     and 1 = 1))
            and 1 = 1)
select /*+ parallel(auto) */ *
  from OLAPS
union all
select /*+ parallel(auto) */ *
  from GAPS;

How to delete multiple records of a key?

If have come across various projects where especially staging tables had no Oracle enabled business key and not data cleansing process in place. It sorrowfully occurred that we were delivered data with multiple records of the same key characteristics and had to cleanse it manually - after those were loaded further. Cleansing can be done using following SQL skeleton.Be aware that we rely on none of the business key columns contain NULL!

To inspect

with BASE
     as (  select *
             from [TABLE NAME]
            where [FILTER])
   , TUPS
     as (  select [BUSINESS KEY COLUMNS]
             from BASE
         group by [BUSINESS KEY COLUMNS]
           having count(*) > 1)
  select /*+ parallel(4) */
         B.*
    from BASE B
         inner join TUPS on (B.[BUSINESS KEY COLUMNS]) in ((TUPS.[BUSINESS KEY COLUMNS]))
order by B.[BUSINESS KEY COLUMNS];

To delete

delete /*+ parallel(4) */
       from [TABLE NAME]
      where rowid in
               (
                  with NUM_RID
                       as (select rowid as RI
                                , [BUSINESS KEY COLUMNS]
                                , row_number()
                                  over(
                                       partition by [BUSINESS KEY COLUMNS]
                                       order by [SORTER COLUMN] [SORT ORDER]
                                      )
                                     as RN
                             from [TABLE NAME]
                            where [FILTER])
                  select RI
                    from NUM_RID
                   where RN > 1
               );

To terminate

merge into [TABLE NAME] DEST
using (
   select T.rowid
        , row_number()
          over(
                partition by [BUSINESS KEY COLUMNS]
                order by [SORTER COLUMN] [SORT ORDER]
              )
             as RN
     from [TABLE NAME] T
    where 1 = 1
      and [FILTER]
) SRC
on (dest.rowid = src.rowid and src.RN > 1)
when matched then
   update set dest.[VALIDITY TO COLUMN] = TO_DATE('-4701-01-01', 'SYYYY-MM-DD')
            , [MARKER CLAUSE];

How to find the shortest distance to a location? (Nearest neighbour problem)

Setup

We have data containing coordinates of points lets say dwellings (about 1.6 millions) and like wise of post offices (1300). To each dwelling we want to know the nearest post office.

Problem

Brute force would be to cross join the sets getting a Cartesian product to calculate the distance of each dwelling with each post office. Thus, the data set comprised of 2.08 * 10^9 records. This probably would be either very slow or the tablespaces would blow to a ORA-01652.

Proposed solution

This solution is a quite able thing. The problem I had with ORA-04036 was due to a counter erroneously not being increased making the square not increase such that recursion never exited... dooo. :-)

Repartitioning an exising table

Cool article on that.

Wrapper over the dict tables to make the content lower case

create or replace view DICT_MINUSCULE
as
   select lower(TABLE_NAME) as TAB, lower(COMMENTS) as COMS from DICTIONARY;

grant select on DICT_MINUSCULE to public;


create or replace view DICT_COLS_MINUSCULE
as
   select lower(TABLE_NAME) as TAB
        , lower(COLUMN_NAME) as COL
        , lower(COMMENTS) as COMS
     from DICT_COLUMNS;

grant select on DICT_COLS_MINUSCULE to public;

ORA-12704: Character Set Mismatch

If you try to compare or union varchar2 with nvarchar2 directly with each other you will get mentioned ORA-12704. Astonishingly enough I did get it under 12c also with following query!

with V
     as (  select 'VARCHAR2_TABLE' as SRC
                , VARCHAR2_ATTRIBUTE as ATTRIBUTE_VALUE
                , count(*) as NUM
                , count(distinct VARCHAR2_ATTRIBUTE) as NUM_DIST_TRACKINGNUMBER
             from VARCHAR2_TABLE
         group by VARCHAR2_ATTRIBUTE)
   , NV
     as (  select 'NVARCHAR2_TABLE' as SRC
                , cast(NVARCHAR2_ATTRIBUTE as varchar2(4000))
                     as ATTRIBUTE_VALUE
                , count(*) as NUM
                , count(distinct VKOR_BESTELLNUMMER) as NUM_DIST_TRACKINGNUMBER
             from NVARCHAR2_TABLE
         group by cast(NVARCHAR2_ATTRIBUTE as varchar2(4000)))
select * from V
union all
select * from NV;

I have no work around so far.

Quick regression test statement

WITH base_new
     AS (SELECT -- TODO select column list
           FROM table_with_new_data
          WHERE -- TODO adapt where clause
                     )
   , base_old
     AS (SELECT -- TODO copy column list from new data table
           FROM table_with_old_data
          WHERE -- TODO copy where clause from new data table
                     )
   , new
     AS (SELECT 'new' AS src, t.*
           FROM base_new t
         MINUS
         SELECT 'new' AS src, t.*
           FROM base_old t)
   , old
     AS (SELECT 'old' AS src, t.*
           FROM base_old t
         MINUS
         SELECT 'old' AS src, t.*
           FROM base_new t)
   , uni
     AS (SELECT * FROM old
         UNION ALL
         SELECT * FROM new)
  SELECT /*+ parallel(4) */
        *
    FROM uni
ORDER BY 2 ASC, 3 ASC, 1 ASC;

Does NULL get counted in a select?

Nope, at least as long as all the attributes in a count are NULL unless you do NOT make the attribute list a *

Should I byte or char tables?

At least since 11g you can create a table with string attributes either in byte or in character. If you do byte, then the length of the data reserved equals the value given, otherwise it is reserved 4 times the length given... to a maximum of 1000. Values greater than 1000 make a reservation of 4000 nonetheless. Run ...

create table THIEMO_TEST_BYTE (V varchar2(2000 byte));
create table THIEMO_TEST_CHAR (V varchar2(2000 char));
create table THIEMO_TEST_CHAR_2 (V varchar2(999 char));

... and get

Let's assume you wanted to put text that contains 2000 characters from japanese and russian into a VARCHAR2(2000 char) attribute. Sounds straight forwards but you are going to fail because those languages are not encoded in a single-byte codepage. You would probably use UTF-8 but there the more extraordinary (from the snobish point of view of the average westerner) characters are encoded with more than one byte... summing up to more than 4000 bytes in length.

• In grid-editing in DB Visualizer and TOAD into any of the 2000-legth-tables returns ORA-01461 [gibbering something about LONG data type]
• As script in DB Visualizer and in TOAD returns "ORA-01704: string literal too Long" [strictly speaking this is wrong as I forwarded only 2000 characters that where 6000 bytes Long]
• PowerCenter returns with "ORA-12899: value too large for column" when trying to insert into the byte table, but when trying to insert into the character table, it does not fail! The data gets clipped!! To me this is outright dangerous behavior!

byte

Advantages

• if data is too long an error get's thrown

Disadvantages

• you do not exactly know how many characters you can fit in a given attribute

char

Advantages

• you can exactly tell how many characters you can fit into an attribute up to the attribute length of 1000

Disadvantages

• under specific circumstances data gets silently clipped if it is larger than the data length
• you cannot tell how many characters you can fit into an attribute longer than 1000 characters

However

It should be possible to avoid those problems using nvarchar2 data type, see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9462837200346048883 and http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020