در این متن به تغییرات و بهبودهای تابع LISTAGG در اوراکل 12cR2، 18c و 19c می پردازیم این تابع در نسخه 11gR2 ارائه شد.

عبارت ON OVERFLOW(نسخه 12cR2)

مقدار برگشتی تابع LISTAGG از نوع VARCHAR2 می باشد از این رو اگر اندازه مقدار برگشتی از حداکثر سایز تعریف شده برای نوع داده VARCHAR2(یعنی 4K و یا 32K) بیشتر باشد، دستور با خطای ORA-01489 متوقف خواهد شد:

SQL> select grantee,listagg(privilege,’,’) within group (order by privilege)from dba_sys_privs where grantee in (‘SYS’) group by grantee;

ORA-01489: result of string concatenation is too long

برای جلوگیری از این خطا، اوراکل در نسخه 12cR2 عبارت ON OVERFLOW TRUNCATE را به syntax تابع listagg اضافه کرده است که در صورت استفاده از ان، خروجی دستور listagg تا جایی که از محدودیت تعریف شده برای نوع داده varchar2 تجاوز نمی کند اطلاعات را به کاربر نمایش می دهد و در انتهای متن هم تعداد کاراکترهایی که در خروجی حذف شده اند نمایش داده خواهند شد:

SQL> select grantee, listagg(privilege, ‘,’ ON OVERFLOW TRUNCATE) within group(order by privilege)

  from dba_sys_privs

 where grantee in (‘SYS’)

 group by grantee;

قسمتی از خروجی این پرس و جو:

ADMINISTER ANY SQL TUNING SET,ADMINISTER DATABASE TRIGGER,…(37)

همانطور که می بینید، 37 کاراکتر در خروجی مستثنا شدند و قبل از نمایش عدد، از کاراکتر … استفاده شده است البته این جزییات قابل تغییر هستند برای مثال، با استفاده از عبارت WITHOUT COUNT در کنار ON OVERFLOW TRUNCATE، می توان از نمایش عدد 37 جلوگیری کرد:

listagg(privilege, ‘,’ ON OVERFLOW TRUNCATE  WITHOUT COUNT)

همچنین می توان با اعمال تغییر زیر، به جای نمایش کاراکتر … از کاراکتر *****  استفاده کرد:

listagg(privilege, ‘,’ ON OVERFLOW TRUNCATE ‘ ***** ‘ WITHOUT COUNT)

توجه: اگر در فکر ان هستیم که خروجی فوق به صورت کامل و تحت هر شرایطی بدون خطا به کاربر نمایش داده شود، می توانیم از توابع دیگری چون xmlagg استفاده کنیم:

SQL> select grantee,xmlagg(xmlelement(m, privilege || ‘,’).extract(‘//text()’) order by privilege)

  from dba_sys_privs

 where grantee in (‘SYS’)

 group by grantee;

نکته: با توجه آنکه در دیتابیس ما پارامتر max_string_size به مقدار STANDARD تنظیم شده است، سایز VARCHAR2 برابر با 4K است بنابرین با تغییر این مقدار به EXTENDED می توان 37 کارکتر را هم در خروجی نمایش داد(بدون استفاده از عبارت ON OVERFLOW TRUNCATE):

SQL>select grantee,listagg(privilege,’,’) within group (order by privilege)from dba_sys_privs where grantee in (‘SYS’) group by grantee;

قسمتی از خروجی این پرس و جو:

SYS         ADMINISTER ANY SQL TUNING SET,….

 

اختیاری شدن استفاده از عبارت WITHIN GROUP در اوراکل 18c

تا قبل از اوراکل نسخه 18c استفاده از عبارت (within group (order by cols به همراه تابع listagg اجباری بوده و از نسخه 18c به بعد می توان از این عبارت صرف نظر کرد:

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 31 12:02:37 2020

SQL> select grantee,listagg(privilege,’,’)  from dba_sys_privs where grantee in (‘VAHID’) group by grantee;

ORA-02000: missing WITHIN keyword

SQL> select grantee,listagg(privilege,’,’) within group (order by privilege) PRIVILEGE  from dba_sys_privs where grantee in (‘VAHID’) group by grantee;

GRANT PRIVILEGE

—– ————————————————–

VAHID CREATE SESSION,SELECT ANY TABLE

 

SQL*Plus: Release 18.0.0.0.0 – Production on Mon Aug 31 11:49:33 2020

SQL>  select grantee,listagg(privilege,’,’)  PRIVILEGE  from dba_sys_privs where grantee in (‘VAHID’) group by grantee;

GRANT PRIVILEGE

———      —————————————————–

VAHID     UNLIMITED TABLESPACE,CREATE SESSION

 

LISTAGG DISTINCT در اوراکل 19c

در اوراکل نسخه 19c قابلیت حذف مقادیر تکراری در تابع listagg به وجود آمد این ویژگی با اضافه کردن عبارت distinct قبل از نام ستون قابل استفاده خواهد بود. مثال زیر را ببینید:

SQL> create table ali.mytbl as select * from dual;

Table created.

SQL>  create table vahid.mytbl as select * from dual;

Table created.

SQL> create table ali.mtbl as select * from dual;

Table created.

SQL> create table vahid.mtbl as select * from dual;

Table created.

اوراکل نسخه 18c:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production

SQL> select listagg(table_name,’,’ ) table_name from dba_tables where owner in (‘VAHID’,’ALI’);

TABLE_NAME

———————-

MYTBL,MYTBL,MTBL,MTBL

SQL> select listagg(distinct table_name,’,’ ) table_name from dba_tables where owner in (‘VAHID’,’ALI’);

ORA-30482: DISTINCT option not allowed for this function

اوراکل نسخه 19c:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

SQL> select listagg(table_name,’,’ ) table_name from dba_tables where owner in (‘VAHID’,’ALI’);

TABLE_NAME

————

MYTBL,MYTBL,MTBL,MTBL

 

SQL> select listagg(distinct table_name,’,’ ) table_name from dba_tables where owner in (‘VAHID’,’ALI’);

TABLE_NAME

————

MTBL,MYTBL