Funzione LISTAGG: “il risultato della concatenazione di stringhe è troppo lungo”

Sto usando Oracle SQL Developer versione 3.0.04. Ho provato a utilizzare la funzione LISTAGG per raggruppare i dati insieme ..

CREATE TABLE FINAL_LOG AS SELECT SESSION_DT, C_IP, CS_USER_AGENT, listagg(WEB_LINK, ' ') WITHIN GROUP(ORDER BY C_IP, CS_USER_AGENT) "WEB_LINKS" FROM webviews GROUP BY C_IP, CS_USER_AGENT, SESSION_DT ORDER BY SESSION_DT 

Tuttavia, continuo a ricevere l’errore,

Errore SQL: ORA-01489: il risultato della concatenazione di stringhe è troppo lungo

Sono abbastanza sicuro che l’output potrebbe essere superiore a 4000, poiché il WEB_LINK menzionato qui è un valore concatenato di url stem e query url.

C’è un modo per aggirarlo o c’è qualche altra alternativa?

Poiché la stringa di aggregati può essere più lunga di 4000 byte, non è ansible utilizzare la funzione LISTAGG . Potresti potenzialmente creare una funzione di aggregazione definita dall’utente che restituisce un CLOB piuttosto che un VARCHAR2 . C’è un esempio di un aggregato definito dall’utente che restituisce un CLOB nella discussione originale di askTom a cui Tim si collega da quella prima discussione.

 SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST FROM tablename; 

Ciò restituirà un valore di clob, quindi nessun limite sulle righe.

Stai superando il limite SQL di 4000 byte che si applica anche a LISTAGG .

 SQL> SELECT listagg(text, ',') WITHIN GROUP ( 2 ORDER BY NULL) 3 FROM 4 (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250 5 ) 6 / SELECT listagg(text, ',') WITHIN GROUP ( * ERROR at line 1: ORA-01489: result of string concatenation is too long 

Come soluzione alternativa, è ansible utilizzare XMLAGG .

Per esempio,

 SQL> SET LONG 2000000 SQL> SET pagesize 50000 SQL> SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()') 2 ).GetClobVal(),',') very_long_text 3 FROM 4 (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250 5 ) 6 / VERY_LONG_TEXT -------------------------------------------------------------------------------- one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen ,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twenty-one,twenty-two,twenty -three,twenty-four,twenty-five,twenty-six,twenty-seven,twenty-eight,twenty-nine, thirty,thirty-one,thirty-two,thirty-three,thirty-four,thirty-five,thirty-six,thi rty-seven,thirty-eight,thirty-nine,forty,forty-one,forty-two,forty-three,forty-f our,forty-five,forty-six,forty-seven,forty-eight,forty-nine,fifty,fifty-one,fift y-two,fifty-three,fifty-four,fifty-five,fifty-six,fifty-seven,fifty-eight,fifty- nine,sixty,sixty-one,sixty-two,sixty-three,sixty-four,sixty-five,sixty-six,sixty -seven,sixty-eight,sixty-nine,seventy,seventy-one,seventy-two,seventy-three,seve nty-four,seventy-five,seventy-six,seventy-seven,seventy-eight,seventy-nine,eight y,eighty-one,eighty-two,eighty-three,eighty-four,eighty-five,eighty-six,eighty-s even,eighty-eight,eighty-nine,ninety,ninety-one,ninety-two,ninety-three,ninety-f our,ninety-five,ninety-six,ninety-seven,ninety-eight,ninety-nine,one hundred,one hundred one,one hundred two,one hundred three,one hundred four,one hundred five ,one hundred six,one hundred seven,one hundred eight,one hundred nine,one hundre d ten,one hundred eleven,one hundred twelve,one hundred thirteen,one hundred fou rteen,one hundred fifteen,one hundred sixteen,one hundred seventeen,one hundred eighteen,one hundred nineteen,one hundred twenty,one hundred twenty-one,one hund red twenty-two,one hundred twenty-three,one hundred twenty-four,one hundred twen ty-five,one hundred twenty-six,one hundred twenty-seven,one hundred twenty-eight ,one hundred twenty-nine,one hundred thirty,one hundred thirty-one,one hundred t hirty-two,one hundred thirty-three,one hundred thirty-four,one hundred thirty-fi ve,one hundred thirty-six,one hundred thirty-seven,one hundred thirty-eight,one hundred thirty-nine,one hundred forty,one hundred forty-one,one hundred forty-tw o,one hundred forty-three,one hundred forty-four,one hundred forty-five,one hund red forty-six,one hundred forty-seven,one hundred forty-eight,one hundred forty- nine,one hundred fifty,one hundred fifty-one,one hundred fifty-two,one hundred f ifty-three,one hundred fifty-four,one hundred fifty-five,one hundred fifty-six,o ne hundred fifty-seven,one hundred fifty-eight,one hundred fifty-nine,one hundre d sixty,one hundred sixty-one,one hundred sixty-two,one hundred sixty-three,one hundred sixty-four,one hundred sixty-five,one hundred sixty-six,one hundred sixt y-seven,one hundred sixty-eight,one hundred sixty-nine,one hundred seventy,one h undred seventy-one,one hundred seventy-two,one hundred seventy-three,one hundred seventy-four,one hundred seventy-five,one hundred seventy-six,one hundred seven ty-seven,one hundred seventy-eight,one hundred seventy-nine,one hundred eighty,o ne hundred eighty-one,one hundred eighty-two,one hundred eighty-three,one hundre d eighty-four,one hundred eighty-five,one hundred eighty-six,one hundred eighty- seven,one hundred eighty-eight,one hundred eighty-nine,one hundred ninety,one hu ndred ninety-one,one hundred ninety-two,one hundred ninety-three,one hundred nin ety-four,one hundred ninety-five,one hundred ninety-six,one hundred ninety-seven ,one hundred ninety-eight,one hundred ninety-nine,two hundred,two hundred one,tw o hundred two,two hundred three,two hundred four,two hundred five,two hundred si x,two hundred seven,two hundred eight,two hundred nine,two hundred ten,two hundr ed eleven,two hundred twelve,two hundred thirteen,two hundred fourteen,two hundr ed fifteen,two hundred sixteen,two hundred seventeen,two hundred eighteen,two hu ndred nineteen,two hundred twenty,two hundred twenty-one,two hundred twenty-two, two hundred twenty-three,two hundred twenty-four,two hundred twenty-five,two hun dred twenty-six,two hundred twenty-seven,two hundred twenty-eight,two hundred tw enty-nine,two hundred thirty,two hundred thirty-one,two hundred thirty-two,two h undred thirty-three,two hundred thirty-four,two hundred thirty-five,two hundred thirty-six,two hundred thirty-seven,two hundred thirty-eight,two hundred thirty- nine,two hundred forty,two hundred forty-one,two hundred forty-two,two hundred f orty-three,two hundred forty-four,two hundred forty-five,two hundred forty-six,t wo hundred forty-seven,two hundred forty-eight,two hundred forty-nine 

Se si desidera concatenare più colonne che hanno 4000 byte , è ansible concatenare l'output XMLAGG di ciascuna colonna per evitare il limite SQL di 4000 byte.

Per esempio,

 WITH DATA AS ( SELECT 1 id, rpad('a1',4000,'*') col1, rpad('b1',4000,'*') col2 FROM dual UNION SELECT 2 id, rpad('a2',4000,'*') col1, rpad('b2',4000,'*') col2 FROM dual ) SELECT ID, rtrim(xmlagg(XMLELEMENT(e,col1,',').EXTRACT('//text()') ).GetClobVal(), ',') || rtrim(xmlagg(XMLELEMENT(e,col2,',').EXTRACT('//text()') ).GetClobVal(), ',') AS very_long_text FROM DATA GROUP BY ID ORDER BY ID; 

listagg stato recentemente coperto dallo standard ISO SQL (SQL: 2016). Come parte di ciò, ha anche ottenuto una clausola di on overflow , che è supportata da Oracle 12cR2.

 LISTAGG(,  ON OVERFLOW …) 

La clausola di on overflow supporta un’opzione truncate (in alternativa al comportamento predefinito di on overflow error ).

 ON OVERFLOW TRUNCATE [] WITH[OUT] COUNT 

Il valore predefinito è di tre periodi (…) e verrà aggiunto come ultimo elemento se si verifica il troncamento.

Se con il conteggio viene specificato e si verifica il troncamento, il numero di valori omessi viene messo tra parentesi e aggiunto al risultato.

Ulteriori informazioni sulla listagg on overflow clausola di on overflow : http://modern-sql.com/feature/listagg

Potrei tollerare il mio campo concatenato in più righe inferiori al limite di 4000 caratteri – ha fatto quanto segue:

 with PRECALC as (select floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH from MY_TABLE) select LISTAGG(MY_COLUMN,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), MY_COLUMN) from MY_TABLE, PRECALC group by floor(rownum/MAX_FIELD_LENGTH) ; 

Aggiungendo alla risposta accettata. Mi sono imbattuto in un problema simile e ho finito per utilizzare una funzione definita dall’utente che restituiva il clob invece di varchar2. Ecco la mia soluzione:

 CREATE OR REPLACE TYPE temp_data FORCE AS OBJECT ( temporary_data NVARCHAR2(4000) ) / CREATE OR REPLACE TYPE temp_data_table FORCE AS TABLE OF temp_data; / CREATE OR REPLACE FUNCTION my_agg_func (p_temp_data_table IN temp_data_table, p_delimiter IN NVARCHAR2) RETURN CLOB IS l_string CLOB; BEGIN FOR i IN p_temp_data_table.FIRST .. p_temp_data_table.LAST LOOP IF i != p_temp_data_table.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_temp_data_table(i).temporary_data; END LOOP; RETURN l_string; END my_agg_func; / 

Ora, invece di fare

 LISTAGG(column_to_aggregate, '#any_delimiter#') WITHIN GROUP (ORDER BY column_to_order_by) 

devo fare questo

 my_agg_func ( cast( collect( temp_data(column_to_aggregate) order by column_to_order_by ) as temp_data_table ), '#any_delimiter#' ) 

Una nuova funzionalità aggiunta in 12cR2 è la clausola ON OVERFLOW di LISTAGG . La query che include questa clausola sarà simile a:

 SELECT pid, LISTAGG(Desc, ' ' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY seq) AS desc FROM B GROUP BY pid; 

Quanto sopra limiterà l’output a 4000 caratteri ma non ORA-01489 errore ORA-01489 .

Queste sono alcune delle opzioni aggiuntive della clausola ON OVERFLOW :

  • ON OVERFLOW TRUNCATE 'Contd..' : Questo mostrerà 'Contd..' alla fine della stringa (l’impostazione predefinita è ... )
  • ON OVERFLOW TRUNCATE '' : Questo visualizzerà i 4000 caratteri senza alcuna stringa di terminazione.
  • ON OVERFLOW TRUNCATE WITH COUNT : questo mostrerà il numero totale di caratteri alla fine dopo la terminazione dei caratteri. Ad esempio: – ‘ ...(5512)
  • ON OVERFLOW ERROR : Se si prevede che LISTAGG non riesca con l’errore ORA-01489 (che è comunque predefinito).

Miglioramenti di LISTAGG in 12c R2

Gestione degli overflow in LISTAGG

Possiamo utilizzare la funzione di corrispondenza del modello SQL del database 12c, MATCH_RECOGNIZE, per restituire un elenco di valori che non superano il limite.

Esempio di codice e ulteriori spiegazioni nel link sottostante.

https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg

In alcuni scenari l’intenzione è di ottenere tutte le chiavi DISTINCT LISTAGG e l’overflow è causato dal fatto che LISTAGG concatena TUTTE le chiavi.

Ecco un piccolo esempio

 create table tab as select trunc(rownum/10) x, 'GRP'||to_char(mod(rownum,4)) y, mod(rownum,10) z from dual connect by level < 100; select x, LISTAGG(y, '; ') WITHIN GROUP (ORDER BY y) y_lst from tab group by x; X Y_LST ---------- ------------------------------------------------------------------ 0 GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3 1 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3 2 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3 3 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3 4 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3 5 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3 6 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3 7 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3 8 GRP0; GRP0; GRP0; GRP1; GRP1; GRP1; GRP2; GRP2; GRP3; GRP3 9 GRP0; GRP0; GRP1; GRP1; GRP2; GRP2; GRP2; GRP3; GRP3; GRP3 

Se i gruppi sono grandi, le chiavi ripetute raggiungono rapidamente la lunghezza massima consentita e si ottiene l' ORA-01489: result of string concatenation is too long .

Sfortunatamente non esiste un supporto per LISTAGG( DISTINCT y, '; ') ma come soluzione alternativa è ansible utilizzare il fatto che LISTAGG ignora i valori NULL. Usando il ROW_NUMBER considereremo solo la prima chiave.

 with rn as ( select x,y,z, row_number() over (partition by x,y order by y) rn from tab ) select x, LISTAGG( case when rn = 1 then y end, '; ') WITHIN GROUP (ORDER BY y) y_lst, sum(z) z from rn group by x order by x; X Y_LST Z ---------- ---------------------------------- ---------- 0 GRP0; GRP1; GRP2; GRP3 45 1 GRP0; GRP1; GRP2; GRP3 45 2 GRP0; GRP1; GRP2; GRP3 45 3 GRP0; GRP1; GRP2; GRP3 45 4 GRP0; GRP1; GRP2; GRP3 45 5 GRP0; GRP1; GRP2; GRP3 45 6 GRP0; GRP1; GRP2; GRP3 45 7 GRP0; GRP1; GRP2; GRP3 45 8 GRP0; GRP1; GRP2; GRP3 45 9 GRP0; GRP1; GRP2; GRP3 45 

Ovviamente lo stesso risultato può essere raggiunto usando GROUP BY x,y nella sottoquery. Il vantaggio di ROW_NUMBER è che tutte le altre funzioni di aggregazione possono essere utilizzate come illustrato con SUM(z) .

Siamo stati in grado di risolvere un problema simile qui utilizzando Oracle LISTAGG. C’è stato un punto in cui ciò che stavamo raggruppando ha superato il limite 4K, ma questo è stato facilmente risolto facendo in modo che il primo set di dati prendesse i primi 15 elementi da aggregare, ognuno dei quali ha un limite di 256K.

Maggiori informazioni: Abbiamo progetti, che hanno ordini di cambiamento, che a loro volta hanno delle spiegazioni. Perché il database è impostato per prendere il testo di cambiamento in blocchi di 256K limiti non è noto, ma è uno dei vincoli di progettazione. Quindi l’applicazione che alimenta cambia le spiegazioni nella tabella si ferma a 254K e inserisce, quindi ottiene il successivo set di testo e se> 254K genera un’altra riga, ecc. Quindi abbiamo un progetto in un ordine di modifica, 1: 1. Poi li abbiamo come 1: n per le spiegazioni. LISTAGG concatena tutti questi. Abbiamo valori RMRKS_SN, 1 per ogni commento e / o per ogni 254K di caratteri.

Il più grande RMRKS_SN è risultato essere 31, quindi ho eseguito il primo set di dati con SN da 0 a 15, il 2 ° set di dati da 16 a 30 e l’ultimo set di dati da 31 a 45 – hey, pianifichiamo qualcuno aggiungendo una MOLTA spiegazione a qualche cambiamento ordini!

Nel report SQL, Tablix si collega al primo set di dati. Per ottenere gli altri dati, ecco l’espressione:

= First (Fields! NON_STD_TXT.Value, “DataSet_EXPLAN”) & First (Fields! NON_STD_TXT.Value, “ds_EXPLAN_SN_16_TO_30”) & First (Fields! NON_STD_TXT.Value, “ds_EXPLAN_SN_31_TO_45”)

Per noi, dobbiamo avere DB Group per creare funzioni, ecc. A causa di vincoli di sicurezza. Quindi con un po ‘di creatività, non dovevamo fare un User Aggregate o un UDF.

Se la tua applicazione ha una sorta di SN da aggregare, questo metodo dovrebbe funzionare. Non so quale sia l’equivalente TSQL: siamo fortunati ad avere a che fare con Oracle per questo rapporto, per il quale LISTAGG è una manna dal cielo.

Il codice è:

 SELECT LT.C_O_NBR AS LT_CO_NUM, RT.C_O_NBR AS RT_CO_NUM, LT.STD_LN_ITM_NBR, RT.NON_STD_LN_ITM_NBR, RT.NON_STD_PRJ_NBR, LT.STD_PRJ_NBR, NVL(LT.PRPSL_LN_NBR, RT.PRPSL_LN_NBR) AS PRPSL_LN_NBR, LT.STD_CO_EXPL_TXT AS STD_TXT, LT.STD_CO_EXPLN_T, LT.STD_CO_EXPL_SN, RT.NON_STD_CO_EXPLN_T, LISTAGG(RT.RMRKS_TXT_FLD, '') WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT FROM ... WHERE RT.RMRKS_SN BETWEEN 0 AND 15 GROUP BY LT.C_O_NBR, RT.C_O_NBR, ... 

E negli altri 2 set di dati basta selezionare LISTAGG solo per le subquery in FROM:

 SELECT LISTAGG(RT.RMRKS_TXT_FLD, '') WITHIN GROUP(ORDER BY RT.RMRKS_SN) AS NON_STD_TXT 

A PARTIRE DAL …

 WHERE RT.RMRKS_SN BETWEEN 31 AND 45 

… e così via.