PL/SQL: Logger une requête SQL

Version imprimable

Bonjour,

Je suis actuellement en train de mettre en place une gestion de trace au sein de package PL/SQL.
J'ai déjà mis une procédure me permettant de logger dans un fichier externe des traces formattées pour aider à la qualification de problèmes client.

Bon maintenant la seconde étape pour pouvoir des qualifications complètes serait de pouvoir tracer le contenu de requêtes SQL dans un fichier.

En gros je voudrais un equivalent la procédure suivante mais pour une requête SQL comme un SELECT :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
  procedure writeLog ( message in string) is
    file UTL_FILE.FILE_TYPE;
  begin
    /*To write Logs in a file*/
    /*But you have to complete following command to have something:
          CREATE DIRECTORY SCLOG AS 'D:\tmpOra\';
          GRANT READ,WRITE ON DIRECTORY SCLOG TO USER;
    */
    begin
     IF not UTL_FILE.IS_OPEN(file) THEN
        file:= UTL_FILE.FOPEN('SCLOG','fichier.log','a');
     end if;
     IF UTL_FILE.IS_OPEN(file) THEN
       UTL_FILE.PUT(file,TO_CHAR(SYSDATE, '[DD/MM/YYYY HH24:MI:SS]')||' DEBUG'||': ');
       UTL_FILE.PUT_LINE(file,message);
       UTL_FILE.FFLUSH(file);
       utl_file.fclose(file);
     end if;
    exception
     when others then
       IF UTL_FILE.IS_OPEN(file) THEN
          UTL_FILE.PUT(file,TO_CHAR(SYSDATE, '[DD/MM/YYYY HH24:MI:SS]')||' ERROR'||': ');
          UTL_FILE.PUT_LINE(file,'ERROR ==> Problem to write into log file '|| dbms_utility.format_error_stack);
          UTL_FILE.FFLUSH(file);
          utl_file.fclose(file);
       end if;
    end;  
  end;

D'avance merci pour votre aide.

Cdt,
Razmoket

08/01/2008, 15h23
orafrance

tu ne peux pas, en fait faut mettre des "tags" pour savoir où chercher dans le code mais tu peux pas faire mieux...

Citation:

Envoyé par Razmoket

Bonjour,

Je suis actuellement en train de mettre en place une gestion de trace au sein de package PL/SQL.
J'ai déjà mis une procédure me permettant de logger dans un fichier externe des traces formattées pour aider à la qualification de problèmes client.

Cherchez aussi log4SQL dans Google !
Citation:
Bon maintenant la seconde étape pour pouvoir des qualifications complètes serait de pouvoir tracer le contenu de requêtes SQL dans un fichier.

En gros je voudrais un equivalent la procédure suivante mais pour une requête SQL comme un SELECT :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 procedure writeLog ( message in string) is file UTL_FILE.FILE_TYPE; begin /*To write Logs in a file*/ /*But you have to complete following command to have something: CREATE DIRECTORY SCLOG AS 'D:\tmpOra\'; GRANT READ,WRITE ON DIRECTORY SCLOG TO USER; */ begin IF not UTL_FILE.IS_OPEN(file) THEN file:= UTL_FILE.FOPEN('SCLOG','fichier.log','a'); end if; IF UTL_FILE.IS_OPEN(file) THEN UTL_FILE.PUT(file,TO_CHAR(SYSDATE, '[DD/MM/YYYY HH24:MI:SS]')||' DEBUG'||': '); UTL_FILE.PUT_LINE(file,message); UTL_FILE.FFLUSH(file); utl_file.fclose(file); end if; exception when others then IF UTL_FILE.IS_OPEN(file) THEN UTL_FILE.PUT(file,TO_CHAR(SYSDATE, '[DD/MM/YYYY HH24:MI:SS]')||' ERROR'||': '); UTL_FILE.PUT_LINE(file,'ERROR ==> Problem to write into log file '|| dbms_utility.format_error_stack); UTL_FILE.FFLUSH(file); utl_file.fclose(file); end if; end; end;
...
Que est-ce que tu veux tracer exactement, la requête qui s’exécute ?

Petit remarque : Cette procédure contient le bug numéro 1 du code PL/SQL : WHEN OTHERS THEN NULL (dans certains cas c’est vrai)

08/01/2008, 15h42
orafrance

Citation:

Envoyé par mnitu

Petit remarque : Cette procédure contient le bug numéro 1 du code PL/SQL : WHEN OTHERS THEN NULL (dans certains cas c’est vrai)

:mouarf2:

Ha non... on va pas refaire le débat hein :mouarf:
08/01/2008, 15h47
mnitu

Citation:

Envoyé par orafrance

:mouarf2:

Ha non... on va pas refaire le débat hein :mouarf:

Salut Orafrance et bonne année

Pas du tout ! J’attends de toi que tu m’explique toute simplement dans ce cas, pourquoi quand le fichier n’est pas ouverte on devrait ignorer les erreurs :)
08/01/2008, 16h02
orafrance

Merci bonne année à toi aussi ;)

Une raison pourrait être que le logging est un plug-in qui n'a pas d'impact fonctionnel et donc ne doit pas bloquer l'utilisateur ou mettre l'application en erreur et donc tu peux décider que si le logging est HS tu ignores le dysfonctionnement.
Ce serait quand même balaud de planter l'enregistrement d'une facture de 100 lignes parce que le lecteur contenant les logs est déconnecté :mrgreen:

Néanmoins, sys.dbms_system.ksdwrt est plus élégant pour écrire une alerte dans le fichier ad hoc mais cela nécessite des droits étendus j'imagine :?
08/01/2008, 16h54
mnitu

Citation:

Envoyé par orafrance

...
Une raison pourrait être que le logging est un plug-in qui n'a pas d'impact fonctionnel et donc ne doit pas bloquer l'utilisateur ou mettre l'application en erreur et donc tu peux décider que si le logging est HS tu ignores le dysfonctionnement.
Ce serait quand même balaud de planter l'enregistrement d'une facture de 100 lignes parce que le lecteur contenant les logs est déconnecté :mrgreen:
...

Sauf que ça va pas (crois moi c’est toujours le bug numéro 1).
Analyse le code dans la situation suivante : le fichier est ouvert mais l’instruction UTL_FILE.FFLUSH échoue. Cella provoque une exception et donc on passe dans le block WHEN Others. Le fichier est ouvert donc on va réessayer de exécuter UTL_FILE.FFLUSH qui va échouer une nouvelle fois, ce qui va provoquer une nouvelle exception non interceptée et qui va planter bien sûr la saisie de la facture de 100 lignes. Donc exactement le contraire de ce qu’on a essayé d’éviter :).
08/01/2008, 17h04
orafrance

j'ai pas regardé le code, je réponds à : "le bug numéro 1 du code PL/SQL : WHEN OTHERS THEN NULL" ;)

Dans le code, en effet il peut y avoir souci :?

Bonjour tout le monde et merci d'avoir regardé mon problème :)

Ensuite vis à vis des soucis contenu dans ma procédure, je suis bien d'accord qu'actuellement elle est loin d'être parfaite ni finie du fait de mon soucis de vis à vis des requêtes SQL.

Ensuite je rejoins totalement l'idée qu'une gestion de logging ne doit pas être bloquant dans le cas où cela plante (et dans mon cas les volumes sont énormément plus gros que 100 lignes ^_^ donc tout bloquage est d'autant plus impactant)

D'autre part, j'ai tenté avec des résultats mitigés de logger les requêtes executé avec la requête suivante:
Code:

1 2 3 4 5 6 SELECT q.sql_text INTO captured_sql FROM gv$sql q, gv$sql_cursor c, gv$session s WHERE s.audsid = audsid AND s.prev_sql_addr = q.address AND q.address = c.parent_handle;
M'enfin bon ... ma phylosophie a changé sur le sujet pour capturer les paramètres donner aux requêtes en les taggant pour les retrouver dans les packages. (et à la limite logger les resultats de calculs récupérés dans des traitements externes ou via des vues).

Et encore merci pour vos avis.

11/01/2008, 21h27
wondersonic

DBMS_SYSTEM, logon trigger, alter session set TRACEFILE_IDENTIFIER...

Hello,
je serai toi je créerai un logon trigger pour activer le tracage de la nouvelle session de manière à loguer tout ce qui est fait au sein de ta session (insert, update, select...) dans un fichier de traces Oracle (udump/*.trc).

Le logon trigger peut se déclencher pour tout les utilisateurs ou bien pour un user spécifique.

Ensuite, tu peux gérer la taille max du fichier de traces, ajouter un identifiant (V$SESSION.SID, V$SESSION.MACHINE...) au nom de fichier pour mieux l'identifier.

Enfin avec DBMS_SYSTEM, tu peux écrire la date et l'heure...

Bon courage !
12/01/2008, 17h00
pifor

La trace SQL trace tous les ordres SQL de la session mais fournit un fichier trace brut un peu difficile à exploiter (par exemple il donne le temps sous forme de secondes écoulées depis le 1er janvier 1970) qui est d'abord destiné à être analyser par TKPROF pour l'analyse des performances.

Le package debug de Tom Kyte est sans doute très intéressant mais il n'est malheureusement pas documenté.

Il y a aussi la possibilité de ne pas écrire de code et d'utiliser la fonctionnalité d'audit qui peut tracer le code SQL exécuté sur un objet donné. Exemple avec une table dans ce message OTN.