Ce que je veux faire
Dans mon post précédent, j’ai créé une fonction qui me permet d’afficher sous forme de tableau à deux dimensions diverses informations tirées d’une table. Nommée pompeusement transposition, le requête permet entre autres d’afficher en ligne ou en colonne, le topn sql_id consommateurs sur un wait event et leur évolution sur un certain nombre de clichés.
Maintenant, je voudrais exploiter ces données dans du code PL/SQL, j’ai donc besoin de créer du code qui saura s’adapter au nombre de colonnes remontées. Un premier code permet d’afficher les valeurs
declare
requete clob;
nb_cols number;
tab_cols t_varchar;
auto_plsql clob;
ttext varchar2(32767);
begin
p_trsp('sql_id'
, 'snap_id'
, 'dba_hist_sqlstat'
, 'EXECUTIONS_DELTA'
, 'sys'
, '834'
, '840'
, 18
, 'yes'
, requete
, nb_cols
, tab_cols);
auto_plsql :='begin
for c in (';
dbms_lob.append(auto_plsql,requete);
ttext:=')
loop
dbms_output.put(to_char(c.snap_id)||'' '');
';
dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
for i in 1..nb_cols
loop
ttext:='dbms_output.put(to_char(c.c_'||trim(to_char(i))||',''9999'')||'' '');
';
dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
end loop;
ttext:='dbms_output.put_line('' '');
end loop;
end;';
dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
execute immediate auto_plsql;
end;
/
Ce dui donne :
834 0 1803 1730 1430 1024 0 414 0 0 339 312 298 269 0 318 213 209 256 835 2085 1493 1316 905 952 723 291 714 0 229 234 233 232 241 184 187 181 161 836 2068 1490 1308 862 944 720 291 708 708 229 228 228 228 241 175 184 180 134 837 1962 1466 1265 870 892 712 292 0 0 228 228 228 228 237 170 181 176 133 838 1644 1490 1096 700 732 718 291 0 0 229 231 231 231 241 175 184 172 132 839 1909 1494 1275 1064 864 721 328 648 648 229 235 233 231 241 193 184 178 154 840 2028 1490 1288 858 924 723 291 0 693 230 231 231 231 241 175 184 180 133
Et si on limite topn à 4 (donc 4 colonnes + snap id) on obtient :
834 0 1803 1730 1430 835 2085 1493 1316 905 836 2068 1490 1308 862 837 1962 1466 1265 870 838 1644 1490 1096 700 839 1909 1494 1275 1064 840 2028 1490 1288 858
Et si on va un poil plus loin pour traiter le résultat des colonnes dans du PL/SQL …
CREATE OR REPLACE
PACKAGE OJO_RPT
AS
PROCEDURE report(
debut IN varchar2,
fin IN varchar2,
topn IN NUMBER);
END OJO_RPT;
/
CREATE OR REPLACE
PACKAGE body OJO_RPT
AS
-- private proc
type t_varchar is table of varchar2(1000);
procedure p_trsp ( lig in varchar2 -- Colonne qui sera utilisee en tete de ligne
, col in varchar2 -- Colonne qui sera utilisee en tete de colonne
, ltab in varchar2 -- Table source
, pivot in varchar2 -- Colonne qui sera utilisee pour remplir le tabeau
, owner in varchar2 -- Proprietaire de la table
, mini in varchar2 -- Valeur minimale pour col
, maxi in varchar2 -- Valeur maximale pour col
, topn in number -- Nombre de lig affichees ordonnes par cumul de pivot descendant
, trsp in varchar2 := 'No' -- Transposition de la table (lig en tete de colonne et col en tete de ligne)
, req out clob
, ncol out number
, t_ncl out t_varchar ) is
v_col varchar2(32767);
v_where varchar2(32767); -- clause where pour mini et maxi
v_wtopn varchar2(1000); -- clause where pour topn
v_owner varchar2(255);
c1 sys_refcursor;
v_poreq varchar2(32767);
i number;
begin
t_ncl:=t_varchar();
-- Gestion de la variable topn, si null on prend tout
if topn is null
then
v_wtopn:=' ';
else
v_wtopn:=' where rownum <= '||topn;
end if;
-- Gestion de la variable owner, si null on prend ls schema appelant
if owner is null
then
select sys_context('USERENV','CURRENT_SCHEMA') into v_owner from sys.dual;
else
v_owner:=owner;
end if;
-- Gestion des variables mini et maxi, si null on prend tout
if ( mini is not null or maxi is not null )
then
v_where:=' where ';
if mini is not null
then
v_where:=v_where||col||' >
= '||mini ;
if maxi is not null
then
v_where:=v_where||' and '||col||' <= '||maxi ;
end if;
else
v_where:=v_where||col||' <= '||maxi ;
end if;
end if;
-- Si on ne transpose pas lig et col
if upper(trsp) = 'NO'
then
req:='select * from (Select '||lig||' c_0
, sum('||pivot||') total ';
i:=1;
open c1 for 'select distinct to_char('||col||') from '||v_owner||'.'||ltab||v_where||' order by 1' ;
loop
fetch c1 into v_col;
exit when c1%notfound;
v_poreq:='
, sum(decode(to_char('||col||'),'''||v_col||''','||pivot||',0)) c_'||trim(to_char(i)) ;
dbms_lob.writeappend(req,length(v_poreq),v_poreq);
t_ncl.extend();
t_ncl(i):=trim(to_char(v_col));
i:=i+1;
end loop;
ncol:=c1%rowcount;
close c1;
v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||lig||'
order by 2 desc)'||v_wtopn ;
dbms_lob.writeappend(req,length(v_poreq),v_poreq);
-- Si on transpose lig et col
else
req:='Select '||col||' c_0';
i:=1;
open c1 for 'select ent from (select distinct to_char('||lig||') ent, sum('||pivot||') tot from '||v_owner||'.'||ltab||v_where||' group by to_char('||lig||') order by 2 desc)'||v_wtopn ;
loop
fetch c1 into v_col;
exit when c1%notfound;
v_poreq:='
, sum(decode(to_char('||lig||'),'''||v_col||''','||pivot||',0)) c_'||trim(to_char(i)) ;
dbms_lob.writeappend(req,length(v_poreq),v_poreq);
t_ncl.extend();
t_ncl(i):=trim(to_char(v_col));
i:=i+1;
end loop;
ncol:=c1%rowcount;
close c1;
v_poreq:='
from '||v_owner||'.'||ltab||v_where||'
group by '||col||'
order by 1 asc';
dbms_lob.writeappend(req,length(v_poreq),v_poreq);
end if;
end p_trsp;
procedure trt( t in t_varchar ) is
vt varchar2(32767);
c sys_refcursor;
begin
open c for 'select * from table(t)';
loop
fetch c into vt;
exit when c%notfound;
dbms_output.put_line(vt);
end loop;
end trt;
-- public proc
PROCEDURE report(
debut IN varchar2,
fin IN varchar2,
topn IN NUMBER) is
requete clob;
nb_cols number;
tab_cols t_varchar;
auto_plsql clob;
ttext varchar2(32767);
begin
for c0 in (select column_name from dba_tab_columns where table_name='DBA_HIST_SQLSTAT' and owner='SYS' and column_name like '%DELTA')
loop
p_trsp('sql_id'
, 'snap_id'
, 'dba_hist_sqlstat'
, c0.column_name
, 'sys'
, debut
, fin
, topn
, 'no'
, requete
, nb_cols
, tab_cols);
auto_plsql :='begin
for c in (';
dbms_lob.append(auto_plsql,requete);
ttext:=')
loop
dbms_output.put(to_char(c.c_0)||'' '');
';
dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
for i in 1..nb_cols
loop
ttext:='dbms_output.put(to_char(c.c_'||trim(to_char(i))||',''99999999'')||'' '');
';
dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
end loop;
ttext:='dbms_output.put_line('' '');
end loop;
end;';
dbms_lob.writeappend(auto_plsql,length(ttext), ttext);
dbms_output.put_line(c0.column_name);
execute immediate auto_plsql;
end loop;
end report;
END OJO_RPT;
/
Le résultat de cette requête étant une succession de tableaux, dont je donne ici les trois premiers :
FETCHES_DELTA cm5vu20fhtnq1 6549 5494 5490 5401 5490 5497 5490 8t43xdhf4d9x2 0 2085 2068 1962 1644 1909 2028 0k8522rmdzg4k 1976 1604 1600 1575 1600 1606 1600 089dbukv1aanh 1730 1316 1308 1265 1096 1275 1288 aykvshm7zsabd 0 0 2640 2618 0 0 0 5ms6rbzdnq16t 0 723 720 712 718 721 723 83taa7kaw59c1 3587 0 0 0 0 0 0 5kyb5bvnu3s04 414 291 291 292 291 328 291 7qqnad1j615m7 0 714 708 0 0 648 0 5hfunyv38vwfp 0 0 708 0 0 648 693 END_OF_FETCH_COUNT_DELTA 8t43xdhf4d9x2 0 2085 2068 1962 1644 1909 2028 cm5vu20fhtnq1 1803 1493 1490 1466 1490 1494 1490 089dbukv1aanh 1730 1316 1308 1265 1096 1275 1288 8vwv6hx92ymmm 1430 905 862 870 700 1064 858 6v7n0y2bq89n8 1024 952 944 892 732 864 924 5ms6rbzdnq16t 0 723 720 712 718 721 723 5kyb5bvnu3s04 414 291 291 292 291 328 291 7qqnad1j615m7 0 714 708 0 0 648 0 5hfunyv38vwfp 0 0 708 0 0 648 693 43c5ykm1mcp2a 312 234 228 228 231 235 231 SORTS_DELTA 5ms6rbzdnq16t 0 723 720 712 718 721 723 fsbqktj5vw6n9 213 187 184 181 184 184 184 4jrfrtx4u6zcx 0 122 122 128 122 150 122 aykvshm7zsabd 0 0 360 357 0 0 0 db78fxqxwxt7r 447 0 0 0 0 0 0 btwkwwx56w4z6 0 60 60 59 60 60 60 75u022kdra1fz 0 60 60 59 60 60 60 7ng34ruy5awxq 338 0 0 0 0 0 0 grb1cf30q5r7m 314 0 0 0 0 0 0 gvt8zu1k1tsff 0 122 0 0 0 150 0