Le blog de Pacman

- Moi : "Salut, ça te dérange si je crée des JOB sur la base de test ? Ca n'ira jamais en prod"

- Mon DBA : "Bah pas de problème, je t'ai attribué le rôle DBA, fais toi plais'. Cela dit, si c'est pour des rapports XXXX, ils sont déjà déposés en automatique [...je vous passe les détails]"

- Moi : "Non, non, c'est pour Rémy, qui m'a demandé de lui rappeler tous les jours qu'il doit commander le repas"

- Mon DBA : "Euh... ouais. Dans ce cas, j'ai une 11GR2 de test pour préparer la migration, je te crée un compte dessus"

Bref, je me retrouve avec une 11gR2 et je vais faire joujou avec dbms_schudeler et utl_mail.

1) La structure

Bon, histoire de faire un peu de paramétrage sans faire d'usine à gaz non plus, on se limite : 

Aux données personnes : 

PACMANN>CREATE TABLE personne (

  2  id_personne NUMBER NOT NULL PRIMARY KEY,

  3  nom VARCHAR2(50) NOT NULL,

  4  prenom VARCHAR2(50) NOT NULL,

  5  email VARCHAR2(50));

Table created.

Aux notifications, identifiées fonctionnellement par un code

PACMANN>CREATE TABLE message (

  2  id_message NUMBER NOT NULL PRIMARY KEY,

  3  objet VARCHAR2(100) NOT NULL,

  4  corps VARCHAR2(4000),

  5  code_message VARCHAR2(50) NOT NULL UNIQUE);

Table created.

Une table de lien pour abonner les gens aux notifications : 

PACMANN>CREATE TABLE message_destinataire (

  2  id_message NUMBER NOT NULL,

  3  id_personne NUMBER NOT NULL,

  4  CONSTRAINT pk_mess_dest PRIMARY KEY (id_message, id_personne),

  5  CONSTRAINT fk_md_message FOREIGN KEY (id_message) REFERENCES message(id_message),

  6  CONSTRAINT fk_md_personne FOREIGN KEY (id_personne) REFERENCES personne(id_personne));

Table created.

2) Les données

J'insère Rémy et moi. Anonymisés bien sûr dans le post de blog.

On note aussi que la syntaxe INSERT ALL est tout à fait superflue ici, l'esprit étant d'insérer dans plusieurs fois (potentiellement dans des tables différentes) à partir d'une même source.

(Ici dual, puisque comme dit, il s'agit juste d'épater les newbs)

PACMANN>INSERT ALL

  2  INTO personne VALUES(1, 'PACMANN', 'Pacmann', 'yyyy@yyyy.com')

  3  INTO personne VALUES(2, 'ANONYMOUS', 'Rémy', 'xxxx@xxxx.com')

  4  SELECT * FROM dual;

2 rows created.

PACMANN>commit;

Commit complete.

Dans le message, on voit apparaître une variable $1, qui permettra ensuite de personnaliser les messages à l'exécution.

PACMANN>INSERT INTO message

  2  VALUES (1, 'Notification repas',

  3  'N''oublies pas de commander ton repas de midi, $1 !',

  4  'NOTIF_REPAS_MIDI');

1 row created.

PACMANN>commit;

Commit complete.

PACMANN>INSERT INTO message_destinataire

  2  SELECT id_message, id_personne

  3  FROM message

  4    CROSS JOIN personne;

2 rows created.

PACMANN>commit;

Commit complete.

3) Le package d'envoi des mails : 

Dans le package de notification, la procédure send_notif_perso envoie une notification à tous les abonnés.

Comme dit, il suffit d'inclure des $1 et $2 pour personnaliser les messages avec les noms et prénoms des abonnés.

J'avais hésité à faire un envoi global à tous les abonnés, ce qui m'aurait permis, pour la première fois de ma vie, d'utiliser listagg pour constituer la chaîne aggrégée des destinataires... tant pis

(C'est pour cela que la procédure s'appelle send_notif_perso, ça appelle un send_notif_global)

PACMANN>CREATE OR REPLACE PACKAGE pkg_notif AS

  2  PROCEDURE send_notif_perso(p_code_message IN VARCHAR2);

  3  END pkg_notif;

  4  /

Package created.

PACMANN>CREATE OR REPLACE PACKAGE BODY pkg_notif

  2  AS

  3

  4    PROCEDURE send_notif_perso(p_code_message IN VARCHAR2) IS

  5

  6    BEGIN

  7        FOR crow IN (

  8            SELECT c.nom, c.prenom, c.email, a.objet, a.corps

  9            FROM message a

 10              INNER JOIN message_destinataire b on a.id_message = b.id_message

 11              INNER JOIN personne c on b.id_personne = c.id_personne

 12            WHERE a.code_message = p_code_message)

 13        LOOP

 14

 15          UTL_MAIL.SEND (

 16              sender => 'Mr_Notif',

 17              recipients => crow.email,

 18              subject => crow.objet,

 19              message => replace(replace(crow.corps, '$1', crow.prenom), '$2', crow.nom));

 20

 21        END LOOP;

 22    END send_notif_perso;

 23

 24  END pkg_notif;

 25  /

Package body created.

4) La planification

Puis on enpaquette tout ça dans un job, qu'on programme du lundi au vendredi à 10h30, sachant que les commandes se ferment à 11h.

PACMANN>BEGIN

  2  DBMS_SCHEDULER.create_job (

  3    job_name        => 'notification_repas_midi',

  4    job_type        => 'PLSQL_BLOCK',

  5    job_action      => 'BEGIN pkg_notif.send_notif_perso(''NOTIF_REPAS_MIDI''); END;',

  6    start_date      => sysdate,

  7    repeat_interval => 'freq=weekly;byday=mon,tue,wed,thu,fri;byhour=10;byminute=30',

  8    end_date        => NULL,

  9    enabled         => TRUE,

 10    comments        => 'La notif quotidienne pour ne pas oublier de commander le repas de midi !');

 11  END;

 12  /

PL/SQL procedure successfully completed.

Un petit lancement manuel du job pour vérifier que ça marche :

PACMANN>exec DBMS_SCHEDULER.RUN_JOB('notification_repas_midi');

PL/SQL procedure successfully completed.

Il n'aura plus d'excuses pour mourir de faim, Rémy. Enfin si, si le job est shooté, si la base joujou est dézinguée, ... bref, si ça arrive, cela sera clairement son destin.

5) Quelques compléments

J'ai un peu lutté sur quelques points : 

- UTL_MAIL non installé sur la base. En sys : 

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb

alter system set smtp_out_server = ton_serveur scope=both

- A partir de 11g, les utilitaires du type UTL_MAIL sont restreint par des ACL (access control list), j'ai bidouillé par l'intermédiaire de ce site : 

http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

(A noter que la première fois, j'ai connement exécuté les exemples sans remplacer par le nom de mon smtp... erghl)

- Comme je manquais de patience avec la doc oracle, je suis allé chercher les exemples de définitions de planifications chez Donald (ne le dites pas trop fort, ma fille va vouloir y aller aussi)

http://www.dba-oracle.com/t_dbms_scheduler_examples.htm

- Comme j'ai des goûts de chiotte, je change régulièrement de mise en forme pour les requêtes que je poste ici. Vous pouvez laisser un message pour me dire que j'ai vraiment des goûts de chiotte.

Last week, i was quite confused when i read on a forum that Oracle SQL does not support boolean type. Posted question looked like this : 

I was extremely confident that it could be working this way : 

... or also like this :  

... but it ends up with the same error message : 

ORA-00920: invalid relational operator

Why would i expect such a syntax to work ?

I often read that regexp_like returns a boolean. While it clearly returns a boolean in PL/SQL, it does not look like it does in SQL...

By the way, just have a look into the standard package : 

I presume there is a specific implementation of these functions for SQL use, probably through another object wrapping the basic regexp_like function, while the standard function only works in PL/SQL : 

... but it does not in SQL !

Unlike for REGEXP_SUBSTR, Oracle documentation actually does not describe REGEXP_LIKE as a function that returns a boolean, but rather as a condition that evaluates to true or false.

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions001.htm

A condition could be said to be of a logical datatype, although Oracle Database does not formally support such a datatype.

Oracle SQL implements boolean algebra without booleans (by replacing them with condition evaluations !)... kind of creepy :)

Un des grands problèmes dans ma vie, c'est ma passion pour le NATURAL FULL OUTER JOIN. Parce qu'il y a plein de mots, c'est full, le côté naturel, les joints et tout ça.

Et cette lubie m'a encore piégée hier lorsque je faisais une simple comparaison de données avant / après traitement.

Allez, démonstration par étape : 

1) Deux tables correspondant aux données avant / après

SQL>CREATE TABLE tab_avant AS

  2  SELECT level l

  3  FROM dual

  4  CONNECT BY level <= 5;

Table created.

Elapsed: 00:00:00.09

SQL>CREATE TABLE tab_apres AS

  2  SELECT level + 2 l

  3  FROM dual

  4  CONNECT BY level <= 5;

Table created.

Elapsed: 00:00:00.03

2) Pour comparer, minus ou natural machin : 

La version minus me barbe un peu, parce qu'il faut écrire A - B et B - A : 

SQL>SELECT '+ap', a.*

  2  FROM tab_apres a

  3  MINUS

  4  SELECT '+ap', b.*

  5  FROM tab_avant b

  6  UNION ALL

  7  SELECT '+av', c.*

  8  FROM tab_avant c

  9  MINUS

 10  SELECT '+av', d.*

 11  FROM tab_apres d;

'+A          L

--- ----------

+ap          6

+ap          7

+av          1

+av          2

En revanche, la version natural full outer join me semble beaucoup plus drôle :

SQL>SELECT case when b.rowid is null then '+av' when a.rowid is null then '+ap' end, l

  2  FROM tab_avant a

  3    NATURAL FULL OUTER JOIN tab_apres b

  4  WHERE a.rowid is null OR b.rowid is null;

CAS          L

--- ----------

+av          1

+av          2

+ap          6

+ap          7

Elapsed: 00:00:00.03

"Natural" pour dire qu'on match sur toutes les colonnes de même nom (à n'utiliser que pour rigoler sous peine de se faire virer), outer join parce qu'on prend aussi ce qui ne matche pas, et full parce que c'est outer des deux côtés.

3) Une défaite supplémentaire

Seulement voilà, il y a des choses qui ne sont pas comparables... ajoutons une colonne vide dans chaque table : 

SQL>ALTER TABLE tab_avant ADD c number;

Table altered.

Elapsed: 00:00:00.06

SQL>ALTER TABLE tab_apres ADD c number;

Table altered.

Elapsed: 00:00:00.03

... et relançons la comparaison :

SQL>SELECT case when b.rowid is null then '+av' when a.rowid is null then '+ap' end, l, c

  2  FROM tab_avant a

  3    NATURAL FULL OUTER JOIN tab_apres b

  4  WHERE a.rowid is null OR b.rowid is null;

CAS          L          C

--- ---------- ----------

+av          1

+av          4

+av          3

+av          2

+av          5

+ap          6

+ap          7

+ap          5

+ap          4

+ap          3

10 rows selected.

Patatra, ça devient n'importe quoi. Le "natural" ne doit pas faire oublier qu'on teste "colonne a" = "colonne b" dans la jointure.

Et la comparaison NULL = NULL est toujours aussi peu constructive...

On n'a pas ce problème avec minus : 

SQL>SELECT '+ap', a.*

  2   FROM tab_apres a

  3   MINUS

  4   SELECT '+ap', b.*

  5   FROM tab_avant b

  6   UNION ALL

  7   SELECT '+av', c.*

  8   FROM tab_avant c

  9   MINUS

 10   SELECT '+av', d.*

 11   FROM tab_apres d;

'+A          L          C

--- ---------- ----------

+ap          6

+ap          7

+av          1

+av          2

Elapsed: 00:00:00.04

Z'imaginez même pas quels trucs tordus j'étais allé chercher pour expliquer les différences...

Comment est-ce qu'après une journée d'archéologie PL/SQLienne je me suis demandé pourquoi j'ai étudié une proc qui n'est jamais appelée ?

1) Création d'une proc inutile

SQL> r ismagic

  1  create or replace procedure ismagic

  2  IS

  3  BEGIN

  4          --Etape n°31

  5          dbms_output.put_line('Magic is all around');

  6  END;

  7*

Procedure created.

2) Lire la proc dans SQLDeveloper

3) L'astuce ? 

Ne serait-ce que par esprit de contradiction, le premier avril est le parfait moment pour aborder le sujet le moins drôle de l'univers :voir les données avant modifications, et les comparer aux données présentes !

0) Un jeu d'essai simple :

SQL> CREATE TABLE testflash AS
  2  SELECT level id, mod(level, 5) n
  3  FROM DUAL
  4  CONNECT BY level <= 2000;

Table créée

... et quelques modifications :

SQL> UPDATE testflash
  2  SET n = 99
  3  WHERE id = 1000;

1 ligne mise à jour.

SQL> INSERT INTO testflash VALUES(2001, 99);

1 ligne créée.

SQL> DELETE FROM testflash WHERE id = 2000;

1 ligne supprimée.

SQL> commit;

Validation effectuée.

On vérifie que les modifications sont bien là :

SQL> SELECT *
  2  FROM testflash
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000         99
      2001         99

1) AS OF TIMESTAMP :

Par principe, Oracle est capable de livrer une vue dans le passée des données, afin de garantir la consistence de la lecture. C'est fait de manière automatique, par exemple lorsque ta requête ultra longue ne voit pas les modifications qui sont commitées par ailleurs depuis le début de son executions.

En 9i FLASHBACK QUERY permet d'exploiter la mécanique pour demander explicitement, à un instant donné, de lire les données du passé. (Je passe les détails parce ça m'intéresse pas plus que ça)

Et en 9iR2, on va un peu plus loin en permettant, à l'intérieur d'une requête, de préciser quelles tables doivent être lues dans le passé. C'est la clause AS OF TIMESTAMP ajoutée après le nom de la table :

SQL> SELECT *
  2  FROM testflash AS OF TIMESTAMP sysdate - 1/24/12
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000          0
      2000          0

Tadam ! Ce sont les données avant modifications.

1/24/12 pour dire que c'était "il y a 5 minutes", mais on peu bien sûr spécifier directement une vraie date (et c'est ce qu'on va faire dans la suite) :

SQL> SELECT *
  2  FROM testflash AS OF TIMESTAMP to_date('20120401 13:00', 'yyyymmdd hh24:mi')
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000          0
      2000          0

2) Full outer Compare !

Et donc maintenant, pour voir exactement ce qui a changé, une jointure full externe sur la table avant / après permet de faire un diff sympa :

SQL> SELECT CASE WHEN a.id is NULL THEN 'added'
  2              WHEN b.id IS NULL THEN 'removed'
  3     ELSE 'modified' END status,
  4         a.id, a.n, b.id newid, b.n newn
  5  FROM testflash a
  6    FULL OUTER JOIN testflash AS OF TIMESTAMP to_date('20120401 13:00', 'yyyymmdd hh24:mi') b
  7     ON a.id = b.id
  8  WHERE a.id IS NULL OR b.id IS NULL OR a.n <> b.n;

STATUS           ID          N      NEWID       NEWN
-------- ---------- ---------- ---------- ----------
modified       1000         99       1000          0
removed        2001         99
added                                2000          0

3) Entre autres

Il y a quelques pré-requis :

- ëtre post 9.2 bien sûr

- l'utilisateur doit avoir le grant execute sur le package dbms_flashback

- les données doivent être disponibles, un peu dans l'esprit ORA-1555

- pas possible lorsqu'il y a eu un DDL entre temps sur la table (notamment TRUNCATE !)

TIens sinon, la semaine dernière chuis passé OCM !

(On n'échappe pas aux traditions stupides...)

Allez, zou, je vais aller m'occuper de ma gamine qui a bu entre temps toute l'eau de Javel...

(Pas la peine d'appeler la DASS, c'est encore un poisson, hein !)