ORACLE 19c – LISTAGG Enhancement

07 Tuesday Jan 2020

Posted by troroblog in ORACLE, ORACLE 19c, SQL

With ORACLE 19c another gap in the listagg feature was filled. Now we are able to do a native distinct operation on the values in the list, so that the list no longer contains duplicates.

In prior releases we had to deduplicate the values before using them in the listagg, which was rather dreadful when doing other group functions in the same query.

LISTAGG Deduplication

Imagine we would have to list all product subcategories of the SH.Products table group by the product category.

select PROD_CATEGORY
     , listagg(PROD_SUBCATEGORY,' | ') as SubCategories
  from SH.PRODUCTS
 group by PROD_CATEGORY;

PROD_CATEGORY                 SUBCATEGORIES
----------------------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Photo                         Camcorders | Camera Batteries | Camera Batteries | Camera Batteries | Camera Batteries | Camera Batteries | Camera Media | Camera Media | Camera Media | Cameras
Electronics                   Game Consoles | Home Audio | Home Audio | Y Box Accessories | Y Box Accessories | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games | Y Box Games                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
Software/Other                Accessories | Accessories | Accessories | Accessories | Accessories | Bulk Pack Diskettes | Bulk Pack Diskettes | Documentation | Documentation | Documentation | Documentation | Documentation | Documentation | Operating Systems | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable CDs | Recordable DVD Discs | Recordable DVD Discs | Recordable DVD Discs | Recordable DVD Discs | Recordable DVD Discs                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
Peripherals and Accessories   Accessories | Accessories | Accessories | Accessories | Accessories | CD-ROM | CD-ROM | CD-ROM | CD-ROM | CD-ROM | CD-ROM | Memory | Memory | Modems/Fax | Modems/Fax | Monitors | Monitors | Printer Supplies | Printer Supplies | Printer Supplies | Printer Supplies                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
Hardware                      Desktop PCs | Portable PCs

As we can see, there are duplicates in the subcategory lists. So we had to do a deduplication in the past.

select PROD_CATEGORY
     , listagg(PROD_SUBCATEGORY,' | ') as SubCategories
  from (select distinct PROD_CATEGORY, PROD_SUBCATEGORY
          from SH.PRODUCTS)
 group by PROD_CATEGORY;

PROD_CATEGORY                SUBCATEGORIES
---------------------------  -------------------------------------------------------------------------------------------------------------
Electronics                  Y Box Accessories | Game Consoles | Home Audio | Y Box Games
Software/Other               Bulk Pack Diskettes | Recordable CDs | Recordable DVD Discs | Accessories | Documentation | Operating Systems
Photo                        Camera Batteries | Camera Media | Camcorders | Cameras
Peripherals and Accessories  CD-ROM | Accessories | Modems/Fax | Monitors | Printer Supplies | Memory
Hardware                     Portable PCs | Desktop PCs

Now, imagine we would like to add the sum of the list_prices to this query … to get the data consistent, we had to do the sum on the list_prices in two steps. First per distinct prod_category/prod_subcategory to get a deduplicated list of subcategories and then on the category level using the pre-calculated sums.

select PROD_CATEGORY
     , listagg(PROD_SUBCATEGORY,' | ') as SubCategories
     , sum(SumProdListPrice)           as SumProdListPrice
  from (select PROD_CATEGORY
             , PROD_SUBCATEGORY
             , sum(PROD_LIST_PRICE) as SumProdListPrice
          from SH.PRODUCTS
         group by PROD_CATEGORY
                , PROD_SUBCATEGORY) p1
 group by PROD_CATEGORY;

Oracle 19c spelling

Now this got much easier with Oracle 19c, the only thing we have to do is adding distinct to the listagg function

select PROD_CATEGORY
     , listagg(distinct PROD_SUBCATEGORY,' | ') as SubCategories
     , sum(PROD_LIST_PRICE)                     as SumProdListPrice
  from SH.PRODUCTS
 group by PROD_CATEGORY;

If you need to have the list ordered, then you have to use the within group syntax.

select PROD_CATEGORY 
     , listagg(distinct PROD_SUBCATEGORY,' | ') within group (order by PROD_SUBCATEGORY) as SubCategories  
     , sum(PROD_LIST_PRICE) as SumProdListPrice
  from SH.PRODUCTS
 group by PROD_CATEGORY;

WITHIN GROUP clause

Starting with version 19c the WITHIN GROUP clause is optional and only needed if the list has to be ordered.

Conclusion

Adding the possibility to remove duplicates from the list using the distinct keyword looks like a tinyness. But as most of the time duplicates in the list are not wanted this tinyness is really useful.

ORACLE 18c – ALTER SEQUENCE RESTART

20 Tuesday Feb 2018

Posted by troroblog in ORACLE, ORACLE 18c, SQL

≈ Leave a comment

Tags

Sequence, SQL

How many times have we seen the request to “set back” or “set forward” a sequence to its original value. Reasons for doing this have been e.g. having a sequence, that start every time period (e.g. month) new or a heavy import has been done and the sequence should now be set to the max-value+1 of the rows inserted to the table.

There have been various ways to solve this kind of issues.

To set the sequence back normally we dropped and recreated it (and hopefully did not forget the grants).
To set the sequence to a new high value, we just built a loop or changed the stepwidth to get the sequence to the desired value.

All those workarounds are no longer needed with ORACLE 18c as there is a new option that we can use to handle both cases.

Test 1:

After having consumed 500 number from the sequence, we would like to set it back to it’s original value (1).

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
      1

So this works as expected.

Test 2:

After having consumed 500 number from the sequence, we would like to set it forward to 1500.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
   1500

Excellent….

Test 3:

I expect the ALTER SEQUENCE statement to commit open transactions…but just to be sure.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

CREATE TABLE t (x NUMBER);

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501

INSERT INTO t VALUES (1);
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

ROLLBACK;

SELECT *
 FROM t;

X
-
1

ok, would have been nice if not, but it’s what was expected.

Conclusion

Still this is a nice feature available in ORACLE 18c but not in the documentation (SQL

There you will find:

To restart the sequence at a different number, you must drop and re-create it.

and

You will also miss the RESTART option in the syntax diagram of the ALTER SEQUENCE command…

Update (6.1.2020)

After having filed an SR with ORACLE pointing out that the RESTART option is missing in the documentation, I got the following answer:

It (ALTER SEQUENCE … RESTART) was created for internal operations –
Requested to use (ALTER SEQUENCE) as per documentation.

Which means for me, that the restart option is not supported but as oracle is using it internally …

MERGE – DELETE

29 Friday Dec 2017

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Or: “why we should read the manual from time to time…and not blindly follow our intuition…”.

Basically I thought that I know the MERGE command by heart. So, no big deal to write a sql statement that handles changes in the source table and moves them to a target table.

Even removing rows from target, when they are logically deleted in the source should not be a problem as there is an update delete clause in the merge command. So everything went fine…but somehow it did not work the way I expected the merge command to work.

Test case:

CREATE TABLE empl (empno     NUMBER
                  ,ename     VARCHAR(30 CHAR)
                  ,hiredate  DATE
                  ,leavedate DATE)
/

CREATE TABLE empl_current (empno  NUMBER
                          ,ename  VARCHAR2(30 CHAR))
/

INSERT INTO empl VALUES (1,'MUELLER',DATE '2017-01-01', NULL);
INSERT INTO empl VALUES (2,'MEIER'  ,DATE '2017-02-01', NULL);
INSERT INTO empl VALUES (3,'HUBER'  ,DATE '2017-03-01', NULL);
INSERT INTO empl VALUES (4,'HOFER'  ,DATE '2017-04-01', NULL);
INSERT INTO empl VALUES (5,'SCHMIDT',DATE '2017-05-01', DATE '2017-05-31');

COMMIT;

Step 1:

Write a merge that transfers all employees from empl to empl_current if they have not left the company

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
              FROM empl
             WHERE leavedate IS NULL
                OR leavedate > SYSDATE) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename;

Checking empl_current should now show employees 1 to 4 but not 5 as 5 has left the company already.

SELECT * FROM empl_current; -- should show 1 - 4

EMPNO  ENAME
------ ---------------------
     1 MUELLER
     2 MEIER
     3 HUBER
     4 HOFER

Ok, all fine so far.

Step 2:

Now doing some updates on the source table and check whether the MERGE does what it should do.

UPDATE empl
   SET ename = 'Meier'
 WHERE empno = 2; 

UPDATE empl
   SET leavedate = DATE '2017-06-30'
 WHERE empno = 3; 

COMMIT;

So the previous merge would now handle the changes on employee 2 but not the one of employee 3 as I do not have a delete clause yet.

So I need to add a delete clause to the merge command.

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl
             WHERE leavedate IS NULL
                OR leavedate > SYSDATE) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL
                              AND leavedate <= sysdate;

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier     ---> changed 
     3 HUBER     ---> still here (?)
     4 HOFER

Now, the reason for HUBER (3) still being here is, that it cannot be deleted, as it is not a match as the row is not part of the source dataset due to the filter I applied….

Step 3:

The source data set should also contain rows, that should not be inserted but eventually deleted.

So changing the source filter.

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl             
           --  WHERE leavedate IS NULL    /* remove this line */
           --     OR leavedate > SYSDATE  /* and this too */
           ) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL
                               AND leavedate <= sysdate;


SELECT * FROM empl_current; 


EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER
     5 SCHMIDT   ---> ???

Ok, Huber has gone but we have Schmidt now in the target table despite the fact that he has left the company…so what we need to add is a filter on the “NOT MATCHED” insert statement to not add rows that we are not (or no longer) interested in…

Step 4:

Improve the filter on the insert statement in the “MATCHED” case. So no rows get inserted that we are not interested in.

MERGE INTO empl_current tar     
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                       WHERE leavedate IS NULL
                          OR leavedate > SYSDATE
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL 
                              AND leavedate <= SYSDATE;

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER

This is, where I placed a trap…

I came this far with my first attempt … but I added an trap by just following my intuition and not reading the manual.

The reason for getting trapped was, that I asked myself the question:

“Why should I update a row, that I am going to delete anyway?” I think UPDATE and DELETE should exclude each other so we are only updating when not deleting and only deleting when not updating….

Thefore my MERGE looked like this:

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                       WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                       WHERE delete_flag = 'N'
                      DELETE WHERE delete_flag = 'Y';

I added a delete flag, which made it easier to insert only unmatched rows that have not yet been deleted and it was also not to complicated to distinguish between update an delete too.

So add another testcase to get rid of empno 4…

UPDATE empl
   SET leavedate = DATE '2017-08-31'
 WHERE empno = 4; 

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                      WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                       WHERE delete_flag = 'N'
                      DELETE WHERE delete_flag = 'Y';

SELECT * FROM empl_current;                       

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER  --- ????

Hmmm…

So, why is employee 4 still in the target table? I checked my statement over and over again and finally had a look to the documentation which says:

ORACLE Database SQL Language Reference

merge_update_clause

The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET … WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

That was somehow surprising…but, after removing the WHERE clause on the update, everything worked again…

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                      WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE delete_flag = 'Y';

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier

Conclusion

Testing is everything … from time to time you stumle upon things you thought you know by heart … and then you will be thankful for the great documentation job oracle did.

ORACLE 12.2 – External Table Enhancements – Overriding Parameters

26 Wednesday Jul 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL

≈ 1 Comment

Another cool feature of 12.2 is the possibility to override defined parametrs of an external table when accessing it. The following properties may be overwritten:

default directory
location (file-name)
access parameters like badfile, logfile and discardfile
reject limit

This enables us to change e.g. the name of the file to be read without having to do an ALTER TABLE statement (and therefore doing an impicit commit). Furthermore the modification done this way is effective for the current statement only.

So imagine we have an external table defined like this:

CREATE TABLE employee_import (    
   department_id  NUMBER(2,0)  
  ,first_name     VARCHAR2(20 BYTE)  
  ,last_name      VARCHAR2(20 BYTE)  
  ,email          VARCHAR2(25 BYTE)  
  ,phone_number   VARCHAR2(20 BYTE)  
  ,hire_date      DATE
  ,job_id         VARCHAR2(10 BYTE)
  ,salary         NUMBER(8,2)  
  ,commission_pct NUMBER(2,2)
) 
ORGANIZATION EXTERNAL (    
   TYPE ORACLE_LOADER   
   DEFAULT DIRECTORY LTROT_VM_DIR   
   ACCESS PARAMETERS (       
      RECORDS DELIMITED BY NEWLINE      
      LOGFILE  LTROT_VM_DIR:'emp20_wrong.log'      
      BADFILE  LTROT_VM_DIR:'emp20_wrong.bad'      
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL (         
         department_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date date 'DD.MM.YYYY'
        ,job_id
        ,salary
        ,commission_pct
      )
   )
   LOCATION('emp20.txt')
)
REJECT LIMIT UNLIMITED ;

Now, if we would like to access a different os file (having the same structure) than “emp20.txt” we can easily achieve this with the following query:

SELECT *
 FROM employee_import 
 EXTERNAL MODIFY (LOCATION ('em10.txt'));

The parameters that can be changed this way are:

Default Directory
Location
Access Parameters
Reject Limit

If the external is partitioned only table level (not partition level) clauses may be overwritten.

Use-Case

Now, looking at the list of parameters that can be changed it should be possible to change the name of the bad-/log-file when loading the emp-os file and afterwards having an external table where i can read the bad-/log-file passing the names that i used before….

Something like this:

Generic External Table to read log/bad files

CREATE TABLE generic_file (
    message VARCHAR2(4000 BYTE)
) 
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
   DEFAULT DIRECTORY LTROT_VM_DIR
   ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      NOLOGFILE
      NOBADFILE
      NODISCARDFILE
      FIELDS (
         message CHAR(4000)
      )
   )
   LOCATION('emp20.bad')
)
REJECT LIMIT UNLIMITED;

So when I read the emp os-file I alter the name of the bad and the log file using the external modify clause:

SELECT * 
  FROM employee_import
EXTERNAL MODIFY (
   ACCESS PARAMETERS (
      BADFILE 'abadfile.bad'
      LOGFILE 'alogfile.log'));

And after that I should be able to access the bad-/log-file using my generic external table.

SELECT * 
  FROM generic_file 
EXTERNAL MODIFY (
   LOCATION('abadfile.bad'));

Unfortunately I cannot see any data, as the log-/bad-files are locked and empty (Linux Environment) so I need to commit first to release the locks. Afterwards I can read the log-file as well as the bad-file.

SELECT * 
 FROM generic_file 
EXTERNAL MODIFY (
 LOCATION('alogfile.log'))
 WHERE message IS NOT NULL;

MESSAGE
---------------------------------------------------------------------------
 LOG file opened at 07/26/17 07:25:18
Field Definitions for table EMPLOYEE_IMPORT
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Rows with all null fields are accepted
 Fields in Data Source:
 DEPARTMENT_ID CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 FIRST_NAME CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 LAST_NAME CHAR (255)
 Terminated by ","
...

Conclusion

Nice!

NULLIF – A tiny, powerfull and underrated SQL Function

19 Monday Jun 2017

Posted by troroblog in ORACLE, SQL, Tipps and Tricks, Underestimated Features

≈ Leave a comment

Tags

ORACLE, SQL

NULLIF has been introducde quite a while ago (Oracle 9) but still this function is not well known to the community.

SYNTAX : NULLIF(expr1,expr2)

Nullif compares expr1 and expr2. If they are equal, then NULL is returned otherwise expr1. We cannot specify the literal NULL for expr1.

So where can this function be used?

Eliminiate Division by Zero:

WITH sales (prod, thisyear, lastyear)
        AS (SELECT 'Monitor' ,  50, 25 FROM dual UNION ALL
            SELECT 'Keyboard', 125,  0 FROM dual UNION ALL
            SELECT 'Mouse'   ,  35, 40 FROM dual UNION ALL 
            SELECT 'Desktop' ,   0, 25 FROM dual UNION ALL
            SELECT 'Laptop'  ,  10,  3 FROM dual)
SELECT prod, thisYear, lastYear
     , ROUND(thisYear * 100 / NULLIF(lastYear,0),2) AS SalesPerc
  FROM sales
/

PROD       THISYEAR   LASTYEAR  SALESPERC
-------- ---------- ---------- ----------
Monitor          50         25        200
Keyboard        125          0
Mouse            35         40       87.5
Desktop           0         25          0
Laptop           10          3     333.33

Remove not needed concatenation seperators:

Add first letter of the middle name followed by a dot if a middle name exists.

WITH pers (firstname, middle, lastname)
       AS (SELECT 'Kurt'  ,'Heinrich','Meier' FROM dual UNION ALL
           SELECT 'Hubert',NULL      ,'Huber' FROM dual)
SELECT firstname || NULLIF(' '||SUBSTR(middle,1,1) || '.',' .') 
    || ' ' || lastname AS fullname
  FROM pers
/    

FULLNAME
---------------
Kurt H. Meier
Hubert Huber

Suppress values above (or below) a given margin:

Do not show salaries greater/equal 3000.

SELECT ename, NULLIF(sal,GREATEST(sal,3000)) AS sal
  FROM emp
/    

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT
KING
TURNER           1500
ADAMS            1100
JAMES             950
FORD
MILLER           1300

As you can see there are multiple opportunities to make use of this smart little function.

ORACLE 12.2 – create table for exchange with clause

30 Friday Dec 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, Partitioning, SQL

≈ Leave a comment

With ORACLE 12.2 the create table statement has been enriched by the new “for exchange with” clause. This enables us to create a exchange table that is a clone of the partitioned table which is the target of a later exchange table command

The new clause takes care of datatypes, data scale and precision, NOT NULL constraints, hidden column, virtual columns, unused columns, etc.

Indexes, constraints (other than NOT NULL) are not cloned.

Preparation:

-- =============================================================================
-- Create Partitioned Table
-- =============================================================================
CREATE TABLE EMP_PART ( 
   EMPNO    NUMBER(4,0)       NOT NULL
  ,ENAME    VARCHAR2(10 CHAR) NOT NULL
  ,JOB      VARCHAR2(9 CHAR)  NOT NULL
  ,MGR      NUMBER(4,0)
  ,HIREDATE DATE              NOT NULL
  ,SAL      NUMBER(7,2)
  ,COMM     NUMBER(7,2)
  ,DEPTNO   NUMBER(2,0)       NOT NULL
  ,CONSTRAINT chk_sal_comm_less_1000 CHECK (NVL(sal,0) + NVL(comm,0) < 10000)
  ,CONSTRAINT chk_empno CHECK (empno between 1000 and 9999)
) 
PARTITION BY LIST (deptno) (
   PARTITION p_accounting VALUES (10)
  ,PARTITION p_research   VALUES (20)
  ,PARTITION p_sales      VALUES (30)
  ,PARTITION p_default    VALUES (DEFAULT)
);

-- =============================================================================
-- Create Local Index
-- =============================================================================
CREATE INDEX emp_part_idx ON EMP_PART (hiredate)
   LOCAL (
      PARTITION p_accounting_hiredate_idx
     ,PARTITION p_research_hiredate_idx
     ,PARTITION p_sales_hiredate_idx
     ,PARTITION p_default_hiredate_idx
);

-- =============================================================================
-- Add a Primary Key
-- =============================================================================
ALTER TABLE emp_part 
   ADD CONSTRAINT emp_part_pk
   PRIMARY KEY (empno);

-- =============================================================================
-- Fill Data
-- =============================================================================
INSERT INTO emp_part (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
  FROM emp
 WHERE deptno != 20;
 
COMMIT;

-- =============================================================================
-- add a virtual column
-- =============================================================================
ALTER TABLE emp_part
   ADD total_income GENERATED ALWAYS AS (NVL(SAL,0) + NVL(COMM,0));

-- =============================================================================
-- set a column to be hidden
-- =============================================================================
ALTER TABLE emp_part MODIFY deptno INVISIBLE;

-- =============================================================================
-- set a column unused
-- =============================================================================
ALTER TABLE emp_part SET UNUSED COLUMN job;

If we wanted to load the data of department 20 using an exchange partition command with an existing table holding all those rows we somehow had to make sure that the table used for the exchange has the exact same structure, indexes, constraints, etc. as the partitioned table. The new clause in the create table command help us solving this (at least some of the issues).

Create exchange table:

-- =============================================================================
-- Create exchange table
-- =============================================================================
CREATE TABLE emp_part_exchange
  FOR EXCHANGE WITH TABLE emp_part;

-- =============================================================================
-- Fill exchange table w/o job as it is unused
-- =============================================================================
INSERT INTO emp_part_exchange(EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO
 FROM emp
 WHERE deptno = 20;

-- =============================================================================
-- Check the table
-- =============================================================================
SELECT column_name
      ,data_type || 
       CASE data_type
          WHEN 'VARCHAR2' THEN '(' || data_length || ')'
          WHEN 'NUMBER' THEN NULLIF('(' || data_precision || ',' || data_scale || ')','(,)')
          WHEN 'DATE' THEN NULL
       END AS data_type
      ,nullable
      ,hidden_column
      ,virtual_column
  FROM user_tab_cols
 WHERE table_name = 'EMP_PART_EXCHANGE'
 ORDER BY segment_column_id; 

COLUMN_NAME                    DATA_TYPE       NULL HIDDEN  VIRTUAL
------------------------------ --------------- ---- ------- --------
EMPNO                          NUMBER(4,0)     N    NO      NO 
ENAME                          VARCHAR2(10)    N    NO      NO 
SYS_C00003_16123011:26:29$     VARCHAR2(9)     Y    YES     NO 
MGR                            NUMBER(4,0)     Y    NO      NO 
HIREDATE                       DATE            N    NO      NO 
SAL                            NUMBER (7,2)    Y    NO      NO 
COMM                           NUMBER (7,2)    Y    NO      NO 
DEPTNO                         NUMBER (2,0)    N    YES     NO 
TOTAL_INCOME                   NUMBER (,)      Y    NO      YES 


SELECT constraint_name, constraint_type, search_condition_vc
  FROM user_constraints
 WHERE table_name = 'EMP_PART_EXCHANGE';

CONSTRAINT_NAME                TYPE SEARCH_CONDITION_VC 
------------------------------ ---- --------------------------------------------------
SYS_C008877                    C    "DEPTNO" IS NOT NULL 
SYS_C008878                    C    "EMPNO" IS NOT NULL 
SYS_C008879                    C    "ENAME" IS NOT NULL 
SYS_C008880                    C    "HIREDATE" IS NOT NULL 

SELECT index_name, index_type, partitioned
  FROM user_indexes
 WHERE table_name = 'EMP_PART_EXCHANGE';

no rows selected

Exchange Partition?

So if we did an exchange partition at this point we would fail if the exchange table is not empty…

-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Error starting at line : 1 in command -
ALTER TABLE emp_part 
 EXCHANGE PARTITION p_research 
 WITH TABLE emp_part_exchange
Error report -
SQL Error: ORA-14118: CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION
14118. 00000 - "CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the
 ALTER TABLE EXCHANGE PARTITION statement have CHECK constraint
 defined on them.
*Action: Ensure that the two tables do not have CHECK constraint
 defined on any column

Additional work to be done:

We still need to create constraints on the exchange table manually…

-- =============================================================================
-- Check constraint are not created but needed when exchanging the partition
-- =============================================================================
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_sal_comm_less_1000_ep 
   CHECK (NVL(sal,0) + NVL(comm,0) < 10000);
 
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_empno_ep 
   CHECK (empno between 1000 and 9999);

-- =============================================================================
-- Add primary key constraint as it is not created 
-- =============================================================================
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT emp_part_pk_ep
   PRIMARY KEY (empno);

After this is done the exchange partition is working.

-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Table EMP_PART altered.

Conclusion:

The create table for exchange clause is nice but if would be even better if constraints would also be created on the clone. Furthermore the possibility to do a CTAS in combination with the for exchange clause would be on my wishlist.

Ignore Nulls with LAST_VALUE

16 Sunday Oct 2016

Posted by troroblog in ORACLE, SQL, Tipps and Tricks

≈ Leave a comment

Tags

Analytic Functions, FIRST_VALUE, LAST_VALUE, SQL

A powerfull feature of the LAST_VALUE/FIRST_VALUE analytic function is the IGNORE NULL clause. This feature allows us to inherit a column value from a prio row with a specific attribute.

Just lately I came accross the following problem on ORACLE SQL & PL/SQL support forum:

The Data:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
 FROM data
/

The task was to show with every line the last rejection date prior to the current row as an additional column.

SKP_ID	CODE_STATUS	DATE_APP	LAST_REJECTED
111	REJECTED	01-JAN-16	–
123	APPROVED	01-FEB-16	01-JAN-16
201	APPROVED	01-MAR-16	01-JAN-16
302	REJECTED	01-APR-16	01-JAN-16
403	REJECTED	01-MAY-16	01-APR-16
450	APPROVED	01-JUN-16	01-MAY-16
475	APPROVED	01-JUL-16	01-MAY-16
480	APPROVED	01-AUG-16	01-MAY-16
490	REJECTED	01-SEP-16	01-MAY-16

To achieve this the LAST_VALUE analytic function with the IGNORE NULLS addition is very handy as it allows to ignore those rows that we are not interested in (the approved ones).

Solution:

In a first step we nullify all rows in the generated last_rejected column that are of no interest:

DECODE(code_status,'REJECTED',date_app)

The next step searches the last entry in the result set where the column value of the generated column is not null.

LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
      OVER (ORDER BY skp_id)

And the last step makes sure, that we only evaluate to prior of the current row but not including the current row.

ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING

The whole statement looks like this:

WITH data (skp_id, code_status, date_app) 
       AS (SELECT 111, 'REJECTED', DATE '2016-01-01' FROM dual UNION ALL
           SELECT 123, 'APPROVED', DATE '2016-02-01' FROM dual UNION ALL
           SELECT 201, 'APPROVED', DATE '2016-03-01' FROM dual UNION ALL
           SELECT 302, 'REJECTED', DATE '2016-04-01' FROM dual UNION ALL
           SELECT 403, 'REJECTED', DATE '2016-05-01' FROM dual UNION ALL
           SELECT 450, 'APPROVED', DATE '2016-06-01' FROM dual UNION ALL
           SELECT 475, 'APPROVED', DATE '2016-07-01' FROM dual UNION ALL
           SELECT 480, 'APPROVED', DATE '2016-08-01' FROM dual UNION ALL
           SELECT 490, 'REJECTED', DATE '2016-09-01' FROM dual) 
SELECT skp_id
     , code_status
     , date_app
     , LAST_VALUE (DECODE(code_status,'REJECTED',date_app) IGNORE NULLS) 
             OVER (ORDER BY skp_id 
                   ROWS BETWEEN UNBOUNDED PRECEDING 
                            AND 1 PRECEDING) as last_rejected
 FROM data
/

Analytic functions are not really new – the came with 8.1.6 – but still they do not get the attraction they deserve.

ORACLE 12.2 – long names

03 Monday Oct 2016

Posted by troroblog in ORACLE 12c Release 2, SQL

≈ 2 Comments

Tags

ORACLE 12c Release 2, SQL

ORACLE 12.2 enables us to name our objects with names up to 128 byte long. Even if this has been something we were looking forward to for a very long time there are still many things to consider before deciding to cross the old 30 byte border.

Are there places in your PL/SQL code where you placed some VARCHAR2(30) variables to hold oracle object names?
What is the size of the columns in your log tables that you use to store the name of the object you are logging information about?
What about the variables you use in your PL/SQL code to put your dynamic SQL statements together? Are those variables wide enough?

How about this one:

If you are using some dynamic SQL that evaluates a dynamic select list using SYS.DBMS_SQL.DESCRIBE_COLUMNS … then this is a point where you will have to do some changes…

EXAMPLE: Problems using DBMS_SQL

CREATE TABLE Table_with_a_name_that_is_so_much_longer_than_usual (
   column_with_a_very_long_name_as_it_is_possible_in_other_databases NUMBER(5)
  ,another_column_with_also_a_very_very_long_name                    VARCHAR2(100)
);

Table TABLE_WITH_A_NAME_THAT_IS_SO_MUCH_LONGER_THAN_USUAL created.

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns(c       => l_handle
                                ,col_cnt => l_column_counter 
                                ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 10
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
 occurred. For example, this error occurs if an attempt is made to
 assign the value NULL to a variable declared NOT NULL, or if an
 attempt is made to assign an integer larger than 99 to a variable
 declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
 that values do not violate constraints.

The problem is, that desc_rec in DBMS_SQL is still limited to 30 character length for the column names of the result.

To support long names ORACLE has implemented a new function and new data structures in DBMS_SQL.

EXAMPLE: Solution

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab2;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns2(c       => l_handle
                                 ,col_cnt => l_column_counter 
                                 ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Column Name : COLUMN_WITH_A_VERY_LONG_NAME_AS_IT_IS_POSSIBLE_IN_OTHER_DATABASES
Column Name : ANOTHER_COLUMN_WITH_ALSO_A_VERY_VERY_LONG_NAME

Conclusion

Having the possibility to give decent names to objects – e.g. naming an index addresses_employees_usages_fk_index – instead of searching for abbreviation that are at least a little bit meaningfull is a nice thing. But before starting to cross the 30 byte border you have to deeply inspect your code.

ORACLE 12.2 – Data conversion enhancements – 2

01 Saturday Oct 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL

≈ Leave a comment

Tags

Data Conversion, DATE, ORACLE 12c Release 2, VALIDATE_CONVERSION

A new SQL function in ORACLE 12.2 is validate_conversion which enables us to check whether a conversion is possible or not receiving a “boolean” (0/1) value as result.

Example checking conversion of a string value into a date

In case of a string to date conversion error we would like to return the 1st of January of 2016.

WITH data (str) AS (SELECT '10.15.2015' FROM dual union all
                    SELECT '15.10.2015' FROM dual union all
                    SELECT '30.02.2015' FROM dual
                   )
SELECT str
     , VALIDATE_CONVERSION(str as date,'DD.MM.YYYY') conversion_possible
 FROM data
/

STR        CONVERSION_POSSIBLE
---------- -------------------
10.15.2015                   0
15.10.2015                   1
30.02.2015                   0

When checking conversion of a string into a date it is also possible to specifcy the language of the date string.

WITH data (str) AS (SELECT '15. March 2015' FROM dual union all
                    SELECT '15. März 2015' FROM dual union all
                    SELECT '15. Mars 2015' FROM dual
                   )
SELECT str
     , VALIDATE_CONVERSION(str as date,'DD. Month YYYY','NLS_DATE_LANGUAGE=GERMAN') conversion_possible
  FROM data
/

STR            CONVERSION_POSSIBLE
-------------- -------------------
15. March 2015                   0
15. März 2015                    1
15. Mars 2015                    0

Conclusion

Another great little helper especially when loading data from 3rd parties. How often have we struggled just because a single value in a single record just did not fit the specifications. Using validate_conversion we can easily find those values, exclude them, log them and so on.

ORACLE 12.2 -Data conversion enhancements

30 Friday Sep 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL, Tipps and Tricks

≈ Leave a comment

Tags

CAST, Data Conversion, ORACLE 12c Release 2, SQL, TO_CHAR, TO_DATE, TO_NUMBER

ORACLE 12.2 has a new option to handle conversion errors when using functions like CAST, TO_NUMBER, TO_CHAR, TO_DATE, etc. Using this option we may avoid conversion errors an return a default value instead.

The examples use TO_DATE but the option is useable in the other conversion functions accordingly

EXAMPLE TO_DATE

In case of a string to date conversion error we would like to return the 1st of January of 2016.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
SELECT str
 , TO_DATE(str DEFAULT '01.01.2016' ON CONVERSION ERROR,'DD.MM.YYYY') AS conversion
 FROM data
/

STR CONVERTED 
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 01.01.2016
gugus      01.01.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016

6 rows selected.

Using SYSDATE as default

Using SYSDATE as the default value instead of a literal value works as expected.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
SELECT str
 , TO_DATE(str DEFAULT SYSDATE ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/ 

STR CONVERTED 
---------- ----------
15.01.2016 15.01.2016
30.06.2016 30.06.2016
30.02.2016 30.09.2016
gugus      30.09.2016
07.03.2016 07.03.2016
15.10.2016 15.10.2016

6 rows selected.

Default to start of the year…

So next step would be to invoke a function on sysdate, to get the first day of the current year as the default value for an occuring conversion error.

WITH data (str) AS (SELECT '15.01.2016' FROM dual UNION ALL
                    SELECT '30.06.2016' FROM dual UNION ALL
                    SELECT '30.02.2016' FROM dual UNION ALL
                    SELECT 'gugus' FROM dual UNION ALL
                    SELECT '07.03.2016' FROM dual UNION ALL
                    SELECT '15.10.2016' FROM dual)
 SELECT str
      , TO_DATE(str DEFAULT TRUNC(SYSDATE,'YEAR') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/ 

Error at Command Line : 8 Column : 28
 Error report -
 SQL Error: ORA-43907: This argument must be a literal or bind variable.

So, I am a little bit puzzled…we can use SYSDATE but not having a function around it…

Choosing another column value as the default

Now, how about choosing another column of the same table as the default value?

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual)
 SELECT str
      , dateval
      , TO_DATE(str DEFAULT dateval ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/
 
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.

Ok, perhaps it needs to be a string using the same format mask?

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual)
 SELECT str
      , dateval
      , TO_DATE(str DEFAULT TO_CHAR(dateval,'DD.MM.YYYY') ON CONVERSION ERROR,'DD.MM.YYYY') AS converted
 FROM data
/
 
Error at Command Line : 9 Column : 29
Error report -
SQL Error: ORA-43907: This argument must be a literal or bind variable.

So, how can this be solved?

Now, we could return NULL in case of a conversion error and then replace a null value by the value of the second column…like this:

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus'     , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL
                            SELECT NULL        , date '2015-07-21' FROM DUAL)
SELECT str
     , dateval
     , NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval) AS converted
 FROM data
/

STR        DATEVAL    CONVERTED 
---------- ---------- ----------
15.01.2016 15.07.2015 15.01.2016
30.06.2016 16.07.2015 30.06.2016
30.02.2016 17.07.2015 17.07.2015
gugus      18.07.2015 18.07.2015
07.03.2016 19.07.2015 07.03.2016
15.10.2016 20.07.2015 15.10.2016
           21.07.2015 21.07.2015

7 rows selected.

but…I only wanted to replace values that are not convertable by the second value, not null values…

WITH data (str,dateval) AS (SELECT '15.01.2016', date '2015-07-15' FROM dual UNION ALL
                            SELECT '30.06.2016', date '2015-07-16' FROM dual UNION ALL
                            SELECT '30.02.2016', date '2015-07-17' FROM dual UNION ALL
                            SELECT 'gugus' , date '2015-07-18' FROM dual UNION ALL
                            SELECT '07.03.2016', date '2015-07-19' FROM dual UNION ALL
                            SELECT '15.10.2016', date '2015-07-20' FROM dual UNION ALL
                            SELECT NULL , date '2015-07-21' FROM DUAL)
SELECT str
     , dateval
     , CASE 
          WHEN str IS NULL THEN NULL
          ELSE NVL(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY'),dateval)
       END AS converted
 FROM data
/

A little bit complicated, but this way I get what I want.

Handling different date formats in one query

Now another test case, having an input stream using different date formats…

WITH data (str) AS (SELECT '15.03.2016' FROM dual UNION ALL
                    SELECT '2005/03/15' FROM dual UNION ALL
                    SELECT '15-MRZ-2016' FROM dual UNION ALL
                    SELECT '15-MAR-2016' FROM dual UNION ALL
                    SELECT '15. MARCH 2016' FROM dual UNION ALL
                    SELECT '15-MAALIS-2016' FROM DUAL UNION ALL
                    SELECT '15-BRE-2016' FROM DUAL)
SELECT str
     , COALESCE(TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD.MM.YYYY')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'YYYY/MM/DD')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=GERMAN')
               ,TO_DATE(str DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE=FINNISH')
       ) AS converted
 FROM data
/ 

STR            CONVERTED 
-------------- ----------
15.03.2016     15.03.2016
2005/03/15     15.03.2005
15-MRZ-2016    15.03.2016
15-MAR-2016    15.03.2016
15. MARCH 2016 15.03.2016
15-MAALIS-2016 15.03.2016
15-BRE-2016 

7 rows selected.

Conclusion

The DEFAULT … ON CONVERSION ERROR is a very powerful feature, which will help us solving conversion errors very elegant. Of course we will still not be able to do conversions when the input value is not unambiguous (A string value of ‘03.05.2016’ could be the 3rd of May as well as the 5th of March) but still a very welcome feature.