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

تابع LISTAGG در نسخه 11GR2 معرفی شد که هدف آن تجمیع چند رشته ( چند رکورد ) در یک رشته می باشد. اوراکل در نسخه 12CR2 قابلیت جدیدی را به این تابع اضافه کرده که در مواقعی خروجی چند رشته طول زیادی داشته باشد و این تابع با خطا مواجه می شود، قابل استفاده می باشد. (ON OVERFLOW Handling)ایجاد مشکل و حل آن :COLUMN employees FORMAT A40SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM empCROSS 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همانطور که قابل مشاهده است، خروجی بالا با خطا مواجه شده است که علت آن طول زیاد رشته خروجی می باشد. در نسخه 12CR2 قابلیت ON OVERFLOW اضافه شده که به صورت پیش فرض ON OVERFLOW ERRORمی باشد.COLUMN employees FORMAT A40SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY ename) AS employeesFROM empCROSS 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 longSQL>حل مشکل با قابلیت ON OVERFLOW :برای حل این مشکل باید از قابلیت ON OVERFLOW TRUNCATEاستفاده کرد. به مثال زیر توجه کنید.COLUMN employees FORMAT A70SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY ename) AS employeesFROM empCROSS 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,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,[removed]N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...(5339)SQL>در این حالت می توان حتی عبارت (...) را تغییر دادCOLUMN employees FORMAT A70SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '~~~') WITHIN GROUP (ORDER BY ename) AS employeesFROM empCROSS 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,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,[removed]N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,~~~(5339)SQL>ما حتی می توانیم مقدار مربوط به تعداد را که در انتها رشته اضافه شده را حذف کنیم.COLUMN employees FORMAT A70SELECT deptno, LISTAGG(ename, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY ename) AS employeesFROM empCROSS 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,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,[removed]N,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,ALLEN,...SQL>