"J'obtiens une ORA-1489 sous Oracle 11g en utilisant la fonction listagg pour afficher des commentaires s'il y en a trop. Je n'ai pas besoin de toute la liste, un gros aperçu suffit."
Réponse :
La fonction LISTAGG retourne un varchar, type limité à 4000 octets en SQL avec Oracle Database 11.2. Il est possible avec Oracle Database 12c+ d'ajouter à listagg une clause ON OVERFLOW gérant les dépassements. Mais, s'il ne faut de toute façon qu'un aperçu, il est envisageable de ne pas aller plus loin que les 4000 octets. La solution peut être de faire quelque chose de ce style, cela fonctionnera en 11g comme en 12c :
Code : Tout sélectionner
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
create table t1(id integer primary key, com varchar2(1333 char));
Table created.
insert into t1 select rownum, DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(1,100))) from dual connect by level <= 500000;
500000 rows created.
select listagg(com, '; ') within group (order by id), lengthb(listagg(com, '; ') within group (order by id))
from
(select id, com
from
(select
id,
com,
2*dense_rank() over (order by id) - 2 + sum(lengthb(com)) over (order by id) sn
from
(select * from t1 where com is not null
-- inserer les filtres supplementaires
and rownum <= 1334) v0
) v1
where sn <= 4000) v2;
notes :
- sn représente la taille en octets des commentaires agrégés, lengthb est utilisé plutôt que length, la limitation du type varchar est en octets et pas en caractères et 1 caractère peut prendre plus de 1 octet dans les bases en AL32UTF8
- le rownum <= 1334 est pour éviter de balayer toute la table, une chaîne vide est nulle avec Oracle donc une chaîne non nulle fait forcément au moins 1 caractère. En comptant le séparateur faisant 2 octets, il est inutile de s'intéresser à plus de 1334 lignes.
Code : Tout sélectionner
Live SQL 18.1.2, running Oracle Database 18c Enterprise Edition - 18.1.0.0.0
create table t1(id integer primary key, com varchar2(1333 char));
Table created.
insert into t1 select rownum, DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(1,100))) from dual connect by level <= 50000;
50000 rows created.
select listagg(com, '; ') within group (order by id), lengthb(listagg(com, '; ') within group (order by id))
from
(select id, com
from
(select
id,
com,
2*dense_rank() over (order by id) - 2 + sum(lengthb(com)) over (order by id) sn
from
(
select * from t1 where com is not null
-- inserer les filtres supplementaires
fetch first 1334 rows only) v0
) v1
where sn <= 4000) v2;
note :
- 50000 lignes seulement pour tenir dans l'espace fourni par Oracle sur l'environnement de démonstration livesql.oracle.com
- petite variation par rapport à la version 11g de la requête, la syntaxe standard "fetch first N rows only" peut être utilisée à la place de la syntaxe propriétaire Oracle Database basée sur rownum
Code : Tout sélectionner
select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.24 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 ligne)
create table t1(id integer primary key, com varchar);
NOTICE: CREATE TABLE / PRIMARY KEY créera un index implicite « t1_pkey » pour la table « t1 »
CREATE TABLE
insert into t1 select generate_series(1, 500000), md5(random()::text);
INSERT 0 500000
select string_agg(com, '; ' order by id), length(string_agg(com, '; ' order by id))
from
(select id, com
from
(select
id,
com,
2*dense_rank() over (order by id) - 2 + sum(length(com)) over (order by id) sn
from
(select * from t1
where com is not null and com <> ''
-- inserer les filtres supplementaires
fetch first 1334 rows only) v0
) v1
where sn <= 4000) v2;
note :
- string_agg à la place de listagg
- une chaîne vide n'est PAS nulle avec PostgreSQL donc nous écartons ici les chaînes vides en plus des valeurs nulles
Je prends une ligne respectant les critères dans l’ordre de mon choix puis je fais une itération sur la ligne suivante dans la liste des lignes respectant les critères jusqu’à dépasser les 4000. C’est assez proche de ce que nous ferions si nous devions le faire à la main en fait.
Attention, il faut Oracle Database 12.1+ ou PostgreSQL 9.3+, ce type de jointure ne fonctionne pas officiellement avec Oracle Database 11.2 donc il faut un peu patienter :
Code : Tout sélectionner
select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)
with recursive boucle_com(id, com, longueur) as
(
(select id, com, length(com) longueur from t1 order by id asc fetch first 1 row only)
union all
(select t.id, t.com, bc.longueur + 2 + length(t.com)
from boucle_com bc
join lateral
(select id, com, length(com) longueur from t1 where t1.id > bc.id order by id asc fetch first 1 row only) t
on (true)
where bc.longueur + 2 + length(t.com) <= 4000)
)
select string_agg(com, '; ' order by id), length(string_agg(com, '; ' order by id)) from boucle_com;