Strange negative results in select Queries

Problem

I lately ran into problems that a select returned an unexpected negative results und certain condions, e.g. I expected 1 but it returned -1.

select --
       1
  from dual;

Reason

It turns out that this is a behaviour seen only in clients using SQLPlus or the same libraries as SQLPlus or something of the sort. From https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/REMARK.html

A "–" at the end of a REMARK line is treated as a line continuation character.

To me, this is a flaw in the SQLPlus design as, to the best of my knowledge, the SQL standard does not inhibt empty line comments or defines a line continuation character (like \ in bash, if after \ there immediately follows line break). It is even worse as it does not work consistently this way. The following should, if - is line continuation character, throw a syntax error at both instances of the -- but works perfectly fine.

--
select 1
  from --
  dual;

Solution

Do not use empty line comments. Place at least one none whitespace character behind the --.

Why does the were clause "never" trigger the use of an index?

Problem

A DBA of mine recently told me that NULLs would not end up in indexes, that is if the index is a single column index. Likewise, an entry in compound indexes is omitted if all the columns are NULL. The implication seems to be that if your where clause asks for the presence of NULL, Oracle cannot use an index because the information whether an attribute is NULL or not is not known to the index.

That means that the record in the index is missing. The logic behind that it seems, that if we do not know the value we have no statement at all.

To me, this is quite bullshit.

• If I ask someone how much money is on his bank account and he answers he does not know it is a difference to if he does not answer at all. If the "We do not know" was a neglectable statement, I dare say, the SQL standard would not define expression like "IS NULL".
• To omit a record in the index if all NULL is not consistent to a tables getting a record if all columns are NULL.

Workaround

• Use only compound indexes with at least one not-nullable column, possibly as last column as inert weight.
• Use possibly function based indexes

Further reading at Ask Tom

Gotcha of regular expression

 Lately, I issued a query, of that I was sure, it would return records. But it did not, obviously. The query used a regexp_like in the where clause. It turns out that the filter column values sometimes contained linebreaks (which were not found) and sometimes not (which were found). Now, thinking about it, it is obvious but initially, I was rather confused. The following is a query example for the not-returning of records that surprised me.

select *
  from dual
 where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
    NULL;
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END;'
                  ,'^.*kprg.*$'
                  ,'i');

My oversight was that I used ^ and $, marking the beginning and ending of the string to examine. By default, the dot does not match with linebreaks, thus the regular expression would not match. To fix this, you have thee choices.

• Remove the ^ and $

  select *
    from dual
   where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
      NULL;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
  END;'
                    ,'.*kprg.*'
                    ,'i');
  

• Add the n to the match parameters. It makes the dot match linebreaks too.

  select *
    from dual
   where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
      NULL;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
  END;'
                    ,'^.*kprg.*$'
                    ,'in');

• Add the m to the match parameters. It makes the ^ and $ to match start and end of a line.

  select *
    from dual
   where regexp_like(q'BEGIN insert into own_datenstatus.uc4trigger (jobplan, created) values ('DATASTORE.JP.BEBU_MART_KPRG_MONTHLY',sysdate); commit;
      NULL;
  EXCEPTION
      WHEN OTHERS THEN
          NULL;
  END;'
                    ,'^.*kprg.*$'
                    ,'im');

All should find any row where the values contains a line with kprg. I am not aware of any differences with respect to the result. However, I suppose that the last is more performant than the others, because the searched string can be split into lines for the search.

Search tables of schemas for a string, v2

I improved the query I posted the 22th of November 2016. It provides better debugging and better performance. But it comes at a cost. For large tables, ORA-01489 easily occurs. The output has changed as a table gets checked in its entirety and not any longer column by column. 👽In the face of the first problem, you might want to have a dry run beforehand to get the problematic tables you should handle separately - look for the pattern …\(\d+\), and for the latter - sorry, get used to it 😉.

declare
   -- constants
   C_DO_DEBUG                      constant boolean := false;
   C_DO_DRY_RUN                    constant boolean := false;
   C_SEARCH_PATTERN                constant varchar2(32767 char) := '(begin|select|insert|update|delete|merge)'; -- case insensitive
   C_MINIMUM_DATAL_LENGTH          constant pls_integer := 1000;
   C_SCHEMA_INCLUSION_PATTERN      constant varchar2(32767 char)
      := '^(OWN|SST)_.*$';
   C_INCLUDE_VIEW_FLAG             constant char(1 byte) := null; -- null und '' bedeutet Nö, alles andere Jau
   C_DO_LIST_NO_HITS               constant boolean := false;
   C_TAB_NAME_EXCLUSION_PATTERN    constant varchar2(32767 char)
      := '(^(BCKP|BACKUP|BAK|BCK|BKP|TE?MP)[_#$]|[_#$](ERROR|ARCHIVE?)[_#$]|LOG|.*\d{8}$|.*[_$#](PLAN_SQL|AUDIT_TRAIL)|SQLTRACE)' ;
   C_TAB_NAME_INCLUSION_PATTERN    constant varchar2(32767 char)
      := '.*';
   C_COL_NAME_EXCLUSION_PATTERN    constant varchar2(32767 char)
      := '(^(BCKP|BACKUP|BAK|BCK|BKP)[_#$]|[_#$]ERROR[_#$]|LOG)';
   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 varchar2(1000 char) := 'auto';
   C_PLACEHOLDER_SCHEMA_NAME       constant char(6 char) := 'PHSN';
   C_PLACEHOLDER_TABLE_NAME        constant char(6 char) := 'PHTN';
   C_PLACEHOLDER_WHERE_CLAUSE      constant char(6 char) := 'PHWC';
   C_PLACEHOLDER_COLUMN_LIST       constant char(6 char) := 'PHCL';
   C_STATEMENT_STUB                constant clob
      := 'select /*+ parallel(' ||
         C_PARALLEL_DEGREE ||
         ') */' ||
         CHR(10) ||
         '      COUNT(*) as RESULT_COUNT,' ||
         CHR(10) ||
         C_PLACEHOLDER_COLUMN_LIST || ' as RESULT' ||
         CHR(10) ||
         '  from ' ||
         C_PLACEHOLDER_SCHEMA_NAME ||
         '.' ||
         C_PLACEHOLDER_TABLE_NAME ||
         CHR(10) ||
         ' where 1 = 0' ||
         CHR(10) ||
         C_PLACEHOLDER_WHERE_CLAUSE ||
         CHR(10) ||
         '    or 1 = 0';

   -- variables
   V_STATEMENT                              clob := null;
   V_RESULT_COUNT                           integer := 0;
   V_RESULT                                 varchar2(32767 byte) := null;
   
   -- subroutines
   procedure DEBUG (I_MSG in varchar2) is
   begin
      if C_DO_DEBUG then
         DBMS_OUTPUT.PUT_LINE(q'DEBUG - ' || I_MSG);
      end if;
   end;
begin
   dbms_output.put_line('"search_4_string.sql" started' || chr(10));
   
   if C_DO_DRY_RUN then
      DEBUG(q'C_DO_DRY_RUN: TRUE');
   else
      DEBUG(q'C_DO_DRY_RUN: FALSE');
   end if;
   DEBUG(q'C_SEARCH_PATTERN: ' || C_SEARCH_PATTERN || q'');
   DEBUG(q'C_SCHEMA_INCLUSION_PATTERN: ' || C_SCHEMA_INCLUSION_PATTERN || q'');
   DEBUG(q'C_INCLUDE_VIEW_FLAG: ' || C_INCLUDE_VIEW_FLAG || q'');
   if C_DO_LIST_NO_HITS then
      DEBUG(q'C_DO_LIST_NO_HITS: TRUE');
   else
      DEBUG(q'C_DO_LIST_NO_HITS: FALSE');
   end if;
   DEBUG(q'C_TAB_NAME_EXCLUSION_PATTERN: ' || C_TAB_NAME_EXCLUSION_PATTERN || q'');
   DEBUG(q'C_TAB_NAME_INCLUSION_PATTERN: ' || C_TAB_NAME_INCLUSION_PATTERN || q'');
   DEBUG(q'C_COL_NAME_EXCLUSION_PATTERN: ' || C_COL_NAME_EXCLUSION_PATTERN || q'');
   DEBUG(q'C_COL_NAME_INCLUSION_PATTERN: ' || C_COL_NAME_INCLUSION_PATTERN || q'');
   DEBUG(q'C_DATA_TYPE_EXCLUSION_PATTERN: ' || C_DATA_TYPE_EXCLUSION_PATTERN || q'');
   DEBUG(q'C_DATA_TYPE_INCLUSION_PATTERN: ' || C_DATA_TYPE_INCLUSION_PATTERN || q'');
   DEBUG(q'C_PARALLEL_DEGREE: ' || C_PARALLEL_DEGREE || q'');
   DEBUG(q'C_PLACEHOLDER_SCHEMA_NAME: ' || C_PLACEHOLDER_SCHEMA_NAME || q'');
   DEBUG(q'C_PLACEHOLDER_TABLE_NAME : ' || C_PLACEHOLDER_TABLE_NAME || q'');
   DEBUG(q'C_PLACEHOLDER_COLUMN_LIST: ' || C_PLACEHOLDER_COLUMN_LIST || q'');
   DEBUG(q'C_PLACEHOLDER_WHERE_CLAUSE: ' || C_PLACEHOLDER_WHERE_CLAUSE || q'');
   DEBUG(q'C_STATEMENT_STUB: ' || C_STATEMENT_STUB || q'');
   for REC
      in (
            with TC as
                    (select OWNER as SCHEMA_NAME
                          , TABLE_NAME
                          , COLUMN_NAME
                          , DATA_TYPE
                       from ALL_TAB_COLS
                      where 1 = 1
                        and 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 (coalesce(DATA_LENGTH
                                     ,0) >= C_MINIMUM_DATAL_LENGTH
                             or DATA_TYPE in ('CLOB'
                                             ,'NCLOB')) 
                        and HIDDEN_COLUMN = 'NO'
                        and 1 = 1)
              select --+ parallel(auto)
                     TC.SCHEMA_NAME
                   , TC.TABLE_NAME
                   , q'      ''
                     || TC.SCHEMA_NAME
                     ||  q'.'
                     || TC.TABLE_NAME
                     ||  q'''
                     ||  chr(10)
                     || listagg(q'      || chr(10) || '   '
                                || TC.COLUMN_NAME
                                || q': ' || sum(case when regexp_like('
                                || TC.COLUMN_NAME
                                || q', ''
                                || C_SEARCH_PATTERN
                                || q'', 'i') then 1 else 0 end)'
                               ,chr(10)
                               on overflow truncate '…' with count) as COLUMN_LIST
                   , listagg(q'    or '
                                || q'regexp_like('
                                || TC.COLUMN_NAME
                                || q', ''
                                || C_SEARCH_PATTERN
                                || q'', 'i')'
                               ,chr(10)
                               on overflow truncate '…' with count) as WHERE_CLAUSE
                from TC
                     left outer join ALL_VIEWS V
                        on TC.SCHEMA_NAME = V.OWNER
                       and TC.TABLE_NAME = V.VIEW_NAME
               where C_INCLUDE_VIEW_FLAG is not null or V.OWNER is null
            group by TC.SCHEMA_NAME
                   , TC.TABLE_NAME
            order by TC.SCHEMA_NAME asc
                   , TC.TABLE_NAME asc
         )
   loop
      begin
         if C_DO_LIST_NO_HITS then
            DBMS_OUTPUT.PUT_LINE(q'*** ' || REC.SCHEMA_NAME || q'.' || REC.TABLE_NAME || q' ***');
         end if;
         DEBUG(q'REC.COLUMN_LIST: ' || REC.COLUMN_LIST || q'');
         DEBUG(q'REC.WHERE_CLAUSE: ' || REC.WHERE_CLAUSE || q'');

         V_STATEMENT      :=
            REPLACE(
               REPLACE(
                  REPLACE(
                     REPLACE(
                             C_STATEMENT_STUB
                           , C_PLACEHOLDER_SCHEMA_NAME
                           , REC.SCHEMA_NAME
                            )
                   , C_PLACEHOLDER_TABLE_NAME
                   , REC.TABLE_NAME)
                , C_PLACEHOLDER_COLUMN_LIST
                , REC.COLUMN_LIST)
             , C_PLACEHOLDER_WHERE_CLAUSE
             , REC.WHERE_CLAUSE);

         if C_DO_DEBUG then
            DEBUG(
               q'Executing following statement'' ||
               CHR(10) ||
               V_STATEMENT);
         elsif C_DO_DRY_RUN then
            DBMS_OUTPUT.PUT_LINE(
               q'Executing following statement'' ||
               CHR(10) ||
               V_STATEMENT);
         end if;
         if not C_DO_DRY_RUN then
            execute immediate V_STATEMENT
               into V_RESULT_COUNT ,V_RESULT;
         end if;

         if C_DO_LIST_NO_HITS or V_RESULT_COUNT > 0 then
             DBMS_OUTPUT.PUT_LINE(V_RESULT);
         end if;
      exception
         when others
         then
            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;
   dbms_output.put_line(chr(10) || '"search_4_string.sql" ended');
end;
/

Impossible date 00000-00-00 returned by to_char

 Apparently, there are ways to actually enter such dates into Oracle database though the usual ways by SQLPlus is not one of them. Be it as it may, this post is not about that. It is about a quirk (May I call it Oracle bug?) at least in 19c.

The values of D3 and D5 are no valid dates. There is no year 0. D3_1 shows that the actual calculation is not that wrong.

The code, so you can reproduce easier.

select to_char(to_date('-0001-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'), 'syyyy-mm-dd hh24:mi:ss') as d1
      ,to_char(to_date(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 1/24/60/60, 'syyyy-mm-dd hh24:mi:ss') as d2
      ,to_char(add_months(to_date(' 0001-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'), -1), 'syyyy-mm-dd hh24:mi:ss') as d3
      ,add_months(to_date(' 0001-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'), -1) as d3_1
      ,to_char(to_date(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 1, 'syyyy-mm-dd hh24:mi:ss') as d4
      ,to_char(add_months(to_date(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'), -1), 'syyyy-mm-dd hh24:mi:ss') as d5
      ,to_char(to_date('-0001-12-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') - 1/24/60/60, 'syyyy-mm-dd hh24:mi:ss') as d6
      ,to_char(add_months(to_date(' 0001-01-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss'), -2), 'syyyy-mm-dd hh24:mi:ss') as d7
  from dual;

ORA-01756 in SQL+ a statement is not terminated correctly using q' bla '

Explanation

I consider this in this case as a bug. The message is misleading as the it is parser internally (SQL+) not properly set quotes. Indirectly, it is a correct message because of the shortcomings of the parser, the end of the statement gets identified incorrectly.

The problem probably arises because the parser cannot handle quoted strings that contain either

1. lines containing the semi-colon as last character like
   
   

   --SQL
   insert into STATEMENTS_COLLECTION (STATEMENTS) values (q'select * from DUAL;
   delete from DUAL where 1 = 0;'

2. empty lines, including lines only containing white space characters, though the latter has been tested only with space characters

Solution

style="text-align: left;">
1. Switch off the terminator
   

   -- SQL
   -- prevent ORA-01756
   set sqlterminator off
   
   /* do your magic statement here */
   /
   
   set sqlterminator on
   

2. Allow blank lines

   -- SQL
   -- prevent ORA-01756
   set sqlblanklines on
   
   /* do your magic statement here */
   /
   
   set sqlblanklines off

ORA-01722 Invalid number when having implicit conversions (in Toad)

-- SQL
select to_number('0.46') from dual;

The solution is the same as with the UTF-8 problem UTF-8 problem.