• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: VALIDATE_CONVERSION

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.   

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...