• 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: ORACLE 12c Release 2

ORACLE 12.2 – long names

03 Monday Oct 2016

Posted by troroblog in Uncategorized

≈ 3 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 – deprecated pragma for PL/SQL units

02 Sunday Oct 2016

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

Compiler Error, Compiler Warning, ORACLE 12c Release 2, PL/SQL, PRAGMA

With ORACLE 12.2 it is possible to mark program units as deprecated along with a message using a new pragma. The message will show up when the deprecated program unit is referenced and the referencing program unit is compiled (and compiler warnings are enabled).

This enables us to gently replace code by advising the user to move to an alternative program unit.

 


EXAMPLE: Standalond procedure

ALTER SESSION SET PLSQL_WARNINGS = 'Enable:All'
/

CREATE OR REPLACE PROCEDURE p IS
   PRAGMA deprecate(p,'p is deprecated please use p2 instead.');
BEGIN
   sys.dbms_output.put_line('p');
END p;
/

Warning(2,4): PLW-06019: entity P is deprecated

When compiling a deprecated program unit we get an PLW-06019 warning.


EXAMPLE: Packaged procedure

CREATE OR REPLACE PACKAGE pkg IS
   PROCEDURE p;
     PRAGMA deprecate(p,'pkg.p is deprecated please use pkg.p2 instead.');
 
   PROCEDURE p2;
END;
/

Warning(2,4): PLW-06019: entity P is deprecated

CREATE OR REPLACE PACKAGE BODY pkg IS
   PROCEDURE p IS
   BEGIN
      sys.dbms_output.put_line('pkg.p');
   END p;

   PROCEDURE p2 IS
   BEGIN
      sys.dbms_output.put_line('pkg.p2');
   END p2;
END pkg;
/

The pragma specification for packaged program units has to be placed in the specification.

 


Referencing a deprecated program unit

So, what happens if you are using pkg.p or p?

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Warning(3,4): PLW-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Warning(6,8): PLW-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

A different warning (PLW-06020 instead of PLW-06019) shows up including the message we placed in the pragma.


Forcing PLW-06020 to be an error

If we would like to force the user of pkg.p/p to move to pkg.p2/p2 we could now easily defined PLW-06020 to be a compiler error.

ALTER SESSION SET PLSQL_WARNINGS = 'ERROR:6020'
/

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Error(8,4): PLS-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Error(11,8): PLS-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

So the q procedure does not compile anymore.


Conclusion

The deprecated pragma is a good way to let others know that a program unit should not be used anymore…if:

  • compiler warnings are enable
  • compiler warnings are looked at

The possibility to turn a warning into an error is also helpful to force the replacement of deprecated program units (in dev environment).

The problem I see is, that not all deprecated program units have the same expiry date and therefor setting a warning type to error for this case is somehow not sufficient. What I really would like to have is a third (optional) parameter in the pragma specification where I could specify by which date THIS deprecation turns into an error.

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 Tipps and Tricks

≈ Leave a comment

Tags

CAST, Data Conversion, ORACLE, 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.

 

ORACLE 12.2 – LISTAGG Enhancements

30 Friday Sep 2016

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

≈ Leave a comment

Tags

LISTAGG, New Feature, ORACLE 12c Release 2, SQL

As in earlier versions LISTAGG still returns a VARCHAR2 datatype with the known limitations (4 or 32 K). But instead of ending in an error on a size overflow we now have the chance to tell the function to truncate the return string in case it gets to long.

The syntax has changed slightly between the beta version of ORACLE 12.2 and the productive one, there some changes had to be done to this post.


LISTAGG default behaviour

The default behaviour is still the same resulting in an Oracle error on size overflow.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X') 
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id, LISTAGG(str,'-') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ORA-01489: result of string concatenation is too long

ON OVERFLOW TRUNCATE

The new option on overflow truncated leeds to a trunctated string having three dots at the end followed by the number of truncated characters in brackets.

The result lists only the end of the strings created.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- ------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX-...(1069)

Define overflow characters

We are free to define the text that should be shown as the overflow indicator. The text we want to see in case of an overflow is defined following the “ON OVERFLOW TRUNCATE” option.

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ') WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1068)
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] (1069)

Omit the counting

The number of truncated characters can be omitted by adding WITHOUT COUNT (default is with count).

WITH test (id, str) as (SELECT MOD(rownum,7), RPAD('Text',10,'X')
 FROM dual
 CONNECT BY ROWNUM < 10000)
SELECT id
     , LISTAGG(str,'-' ON OVERFLOW TRUNCATE ' [more] ' WITHOUT COUNT) WITHIN GROUP(ORDER BY 1) AS string_created
 FROM test 
 GROUP BY id
/ 

ID STRING_CREATED
-- -----------------------------------------------
 0 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 1 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 
 2 TextXXXXXX-TextXXXXXX-TextXXXXXX- [more] 

Conclusion

Truncating the result of a LISTAGG function to avoid the overflow error is a pretty cool feature of Oracle 12.2 SQL. If you need to concatenate strings beyond the size limit of the VARCHAR2 datatype you will still need to write your own CLOB aggregate function but if it is enough to know, that there was more in the string before having concatenated it the on overflow truncate is really handy. 

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...