تابع LISTAGG در نسخه 11GR2 معرفی شد که هدف آن تجمیع چند رشته ( چند رکورد ) در یک رشته می باشد. اوراکل در نسخه 12CR2 قابلیت جدیدی را به این تابع اضافه کرده که در مواقعی خروجی چند رشته طول زیادی داشته باشد و این تابع با خطا مواجه می شود، قابل استفاده می باشد. (ON OVERFLOW Handling)
ایجاد مشکل و حل آن :
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 A40
SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW ERROR) 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
SQL>
حل مشکل با قابلیت ON OVERFLOW :
برای حل این مشکل باید از قابلیت ON OVERFLOW TRUNCATEاستفاده کرد. به مثال زیر توجه کنید.
COLUMN employees FORMAT A70
SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE) 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;
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 employees
FROM emp
CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE deptno = 30
GROUP BY deptno
ORDER 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 employees
FROM emp
CROSS JOIN (SELECT level FROM dual CONNECT BY level <= 1000)
WHERE deptno = 30
GROUP BY deptno
ORDER 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>
