مقدمه :

تابع LISTAGG در نسخه 11GR2 معرفی شد که هدف آن تجمیع چند رشته ( چند رکورد ) در یک رشته می باشد. اوراکل در نسخه 12CR2 قابلیت جدیدی را به این تابع اضافه کرده که در مواقعی خروجی چند رشته طول زیادی داشته باشد و این تابع با خطا مواجه می شود، قابل استفاده می باشد. (ON OVERFLOW Handling)

تابع LISTAGG در اوراکل 12cr2

ایجاد مشکل و حل آن :

COLUMN employees FORMAT A40

SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)

WHERE  deptno = 30

GROUP BY deptno

ORDER BY deptno;

FROM   emp

       *

ERROR at line 2:

ORA-01489: result of string concatenation is too long

همانطور که قابل مشاهده است، خروجی بالا با خطا مواجه شده است که علت آن طول زیاد رشته خروجی می باشد. در نسخه 12CR2 قابلیت ON OVERFLOW اضافه شده که به صورت پیش فرض ON OVERFLOW ERROR می باشد.

COLUMN employees FORMAT A40SELECT deptno, LISTAGG(ename, ‘,’ ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY ename) AS employeesFROM   emp       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)WHERE  deptno = 30GROUP BY deptnoORDER BY deptno;

 FROM   emp       *ERROR at line 2:ORA-01489: result of string concatenation is too long

SQL>

حل مشکل با قابلیت ON OVERFLOW :

برای حل این مشکل باید از قابلیت ON OVERFLOW TRUNCATE استفاده کرد. به مثال زیر توجه کنید.

COLUMN employees FORMAT A70

SELECT deptno, LISTAGG(ename, ‘,’ ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ename) AS employeesFROM   emp       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)WHERE  deptno = 30GROUP BY deptnoORDER BY deptno;

DEPTNO EMPLOYEES———- ———————————————————————-        30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,[removed]           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,…(5339)

 SQL>

در این حالت می توان حتی عبارت (…) را تغییر داد

COLUMN employees FORMAT A70

SELECT deptno, LISTAGG(ename, ‘,’ ON OVERFLOW TRUNCATE ‘~~~’) WITHIN GROUP (ORDER BY ename) AS employeesFROM   emp       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)WHERE  deptno = 30GROUP BY deptnoORDER BY deptno;

DEPTNO EMPLOYEES———- ———————————————————————-        30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,[removed]           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,~~~(5339)

SQL>

ما حتی می توانیم مقدار مربوط به تعداد را که در انتها رشته اضافه شده را حذف کنیم.

COLUMN employees FORMAT A70

 SELECT deptno, LISTAGG(ename, ‘,’ ON OVERFLOW TRUNCATE ‘…’ WITHOUT COUNT) WITHIN GROUP (ORDER BY ename) AS employeesFROM   emp       CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)WHERE  deptno = 30GROUP BY deptnoORDER BY deptno;

DEPTNO EMPLOYEES———- ———————————————————————-        30 ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLE           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,[removed]           N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,AL           LEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,…

 SQL>