Tag Archives: New Feature

ORACLE 18c – Qualified Expressions

21 Wednesday Feb 2018

Posted by in ORACLE, ORACLE 18c, PL/SQL

≈ Leave a comment

Tags

, ,

Qualified expressions improve readability and maintainability of our PL/SQL code. 

While initialising nested tables was quite easy in the past, initialising associative arrays was quite a pain, if it was not done using a query. 

The following examples show how qualified expressions simplify the coding using an associative array, where the key is the name of a city and the content is the population.

Before 18c

DECLARE
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
   city_population city_population_type;
BEGIN
   <<Init>>
   BEGIN
      city_population('Zürich')   := 400000;
      city_population('Genf')     := 195000;
      city_population('Basel')    := 175000;
      city_population('Bern')     := 140000;
      city_population('Lausanne') := 135000;
   END Init;
 
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

18c using qualified expression

DECLARE   
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);   
   city_population city_population_type;
BEGIN   
   <<Init>>   
   BEGIN       
      city_population := city_population_type('Zürich'   => 400000
                                             ,'Genf'     => 195000
                                             ,'Basel'    => 175000
                                             ,'Bern'     => 140000
                                             ,'Lausanne' => 135000);
   END Init;      
  
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

Qualified expression can also be used when working with record structures (and of course also when record structures are part of a associative array).

Record structures with qualified expressions

DECLARE
   TYPE emp_type IS RECORD(empno    emp.empno%type
                          ,ename    emp.ename%type
                          ,job      emp.job%type
                          ,mgr      emp.mgr%type 
                          ,hiredate emp.hiredate%type
                          ,sal      emp.sal%type
                          ,comm     emp.comm%type 
                          ,deptno   emp.deptno%type);

   TYPE emp_tab_type IS TABLE OF emp_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_type(ename => 'Smith'
                                          , Job => 'Clerk')
                          ,7782 => emp_type(ename => 'Clark'
                                          , Job => 'Manager'
                                          , Sal => 3000)
                          ,7902 => emp_type(ename => 'Ford'
                                          , Sal => 3000
                                          , Deptno => 20));
 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Now…normally we would make use of %ROWTYPE when we need a record structure that reflects a table row…

DECLARE
   TYPE emp_tab_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => ?(ename => 'Smith'
                                   , Job => 'Clerk')
                          ,7782 => ?(ename => 'Clark'
                                   , Job => 'Manager'
                                   , Sal => 3000)
                          ,7902 => ?(ename => 'Ford'
                                   , Sal => 3000
                                   , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Hmm….what “constructor” should we use if the associative array holds a rowtype? But…perhaps a subtype could help.

DECLARE
   SUBTYPE emp_row_type IS emp%ROWTYPE;
   TYPE emp_tab_type IS TABLE OF emp_row_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_row_type(ename => 'Smith'
                                              , Job => 'Clerk')
                          ,7782 => emp_row_type(ename => 'Clark'
                                              , Job => 'Manager'
                                              , Sal => 3000)
                          ,7902 => emp_row_type(ename => 'Ford'
                                              , Sal => 3000
                                              , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

ORA-06550: line 6, column 36: 
PLS-00222: no function with name 'EMP_ROW_TYPE' exists in this scope

Too bad…that would have been nice too.

Conclusion

Qualified expression will help us to write code that is more readable and easier. It’s a nice new feature that I will use for sure.

And perhaps, someday, the %ROWTYPE thing will be possible too.

 

 

 

ORACLE 12.2 – LISTAGG Enhancements

30 Friday Sep 2016

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

≈ Leave a comment

Tags

, , ,

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.