LISTAGG de la fonction: “résultat de la concaténation de chaîne est trop longue”

Depuis les agrégats de la chaîne peut être plus de 4000 octets, vous ne pouvez pas utiliser la LISTAGG fonction. Vous pourriez éventuellement créer un définis par l'utilisateur fonction d'agrégation qui renvoie un CLOB plutôt qu'un VARCHAR2. Il est un exemple d'un agrégat défini par l'utilisateur qui renvoie un CLOB dans le original askTom discussion que Tim liens à partir de cette première discussion.

OriginalL'auteur Justin Cave

Vous dépassez la SQL limite de 4000 octets qui s'applique à LISTAGG ainsi.

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

Comme une solution de contournement, vous pouvez utiliser XMLAGG.

Par exemple,

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

---

Si vous voulez concaténer plusieurs colonnes qui ont 4000 octets, vous pouvez concaténer les XMLAGG de sortie de chaque colonne pour éviter le SQL limite de 4000 octets.

Par exemple,

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;

OriginalL'auteur Lalit Kumar B

Une nouvelle fonctionnalité ajoutée dans 12cR2 est le ON OVERFLOW clause de LISTAGG.
La requête, y compris la présente clause ressemblerait à:

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

Permettra de limiter les résultats à 4000 caractères, mais ne sera pas jeter le ORA-01489 erreur.

Ces sont quelques-uns des autres options de ON OVERFLOW clause:

• ON OVERFLOW TRUNCATE 'Contd..' : Cela permet d'afficher 'Contd..' à
  la fin de la chaîne (valeur par Défaut est ... )
• ON OVERFLOW TRUNCATE '' : Cela permet d'afficher les 4000 caractères
  sans aucune terminaison de chaîne.
• ON OVERFLOW TRUNCATE WITH COUNT : Cela permet d'afficher le total
  nombre de caractères à la fin, après les caractères de fin.
  Par exemple:- '...(5512)'
• ON OVERFLOW ERROR : Si vous pensez que le LISTAGG échec avec l'
  ORA-01489 d'erreur ( Qui est par défaut de toute façon ).

LISTAGG Améliorations dans 12c R2

OriginalL'auteur Kaushik Nayak

Ajoutant à la accepté de répondre.
J'ai rencontré un problème similaire et a fini à l'aide d'une fonction définie par l'utilisateur qui a renvoyé clob au lieu de varchar2. Voici ma solution:

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;
/

Maintenant, au lieu de faire

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

Que j'ai à faire ce

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

OriginalL'auteur Chiranjib

Nous avons été en mesure de résoudre un problème similaire ici de l'utilisation d'Oracle LISTAGG. Il y avait un point où ce que nous étions au groupement de dépassement de la 4K, mais cela a été résolu facilement par le fait d'avoir le premier jeu de données de prendre les 15 premiers éléments de l'agrégat, qui ont chacune un 256K limite.

Plus d'info: Nous avons des projets, qui ont des ordres de modification, qui à son tour avoir des explications. Pourquoi la base de données est mis en place pour modifier le texte en blocs de 256 ko limites n'est pas connue, mais c'est l'une des contraintes de conception. Pour que l'application qui alimente le changement des explications dans le tableau s'arrête à 254K et inserts, puis obtient la prochaine série de texte et si > 254K génère une autre ligne, etc. Donc, nous avons un projet à un changement de l'ordre de 1:1. Ensuite, nous avons comme 1:n pour les explications. LISTAGG concatène toutes ces. Nous avons RMRKS_SN valeurs, 1 pour chaque remarque et/ou pour chaque 254K de caractères.

Le plus grand RMRKS_SN a été trouvé pour le 31, j'ai donc fait le premier jeu de données en tirant SN 0 à 15, le 2ème dataset de 16 à 30 ans et le dernier dataset 31 à 45 -- hey, let's plan sur quelqu'un ajout de BEAUCOUP d'explications à certains ordres de changement!

Dans le générateur de rapport, le tableau Matriciel des liens pour le premier jeu de données. Pour obtenir les autres données, voici l'expression:

=Premier(Champs!NON_STD_TXT.Valeur, "DataSet_EXPLAN") & la Première(les Champs!NON_STD_TXT.Valeur, "ds_EXPLAN_SN_16_TO_30") & la Première(les Champs!NON_STD_TXT.Valeur, "ds_EXPLAN_SN_31_TO_45")

Pour nous, nous devons avoir le Groupe DB de créer des fonctions, etc. en raison de contraintes de sécurité. Donc, avec un peu de créativité, nous n'avons pas à faire à un Utilisateur d'Agrégats ou d'une UDF.

Si votre application a une sorte de SN regrouper les données, cette méthode devrait fonctionner. Je ne sais pas ce que l'équivalent TSQL est -- nous avons la chance d'avoir affaire à Oracle pour ce rapport, qui LISTAGG est une Aubaine.

Le code est:

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,
...

Et dans les 2 autres ensembles de données il suffit de sélectionner le LISTAGG seulement pour les sous-requêtes dans la DE:

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

DE ...

WHERE RT.RMRKS_SN BETWEEN 31 AND 45

...

... et ainsi de suite.

OriginalL'auteur Michael Durthaler

Court de l'aide 12c de débordement à l'aide de la CLOB et substr travaillera également

rtrim(dbms_lob.substr(XMLAGG(XMLELEMENT(E,column_name,',').EXTRAIT de('//text()') ORDER BY column_name).GetClobVal(),1000,1),',')

OriginalL'auteur nosqlforme