Déclenchement d’une requête SQL dynamique en utilisant une procédure PL/SQL stockée
RCI Informatique, Avril 2004
1 - Objectif
On souhaite pouvoir saisir dans un dialogue AppleScript la variable d’une requête SQL.
Le résultat sera obtenu sans passer par les aspects de formatage de SQL*Plus, mais en utilisant une procédure Oracle PL/SQL stockée. Le résultat sera affiché sous forme de feuille Microsoft Excel
2 - Donnée fournie et résultat obtenu
La donnée, c’est une question posée à l’utilisateur :
Le résultat, c’est la feuille Microsoft Excel suivante :
3 - Techniques utilisées
SQL*Plus est capable de déclencher une procédure stockée, en lui passant des arguments éventuels, avec la commande: exec nom_procedure (argument1, .... );
On peut en PL/SQL écrire directement dans un fichier texte, avec la bibliothèque de routines (package) nommée UTL_File.
4 - Le texte AppleScript
-- Déclenchement d'une requête SQL simple avec Oracle 9i pour Macintosh OS X
-- sous forme d'un script Apple Script destiné à lancer, avec SQL*Plus, une procédure "stockée" rédigée en PL/SQL
-- Cette procédure stockée comporte un argument variable, positionné par AppleScript suite à un dialogue
-- Ouverture du résultat de la requête dans une feuille Microsoft Excel
-- Copyright, tous droits réservés, RCI Informatique SA, 2004
-- www.rci-informatique.fr
-- www.index-informatique.net
-- Définition et initialisation d'une variable globale
global wT
set wT to ""
-- Le fichier temporaire de cet exemple
set MonFichier to "resultat_04.lst"
-- Positionnement dans le répertoire DBCreate
e("cd /Users/oracle/9iR2/orahome/DBCreate")
-- Activation des variables d'environnement Oracle
e(". DBENV")
-- Lancement de SQL Plus en se connectant avec SQL*Net TCP/IP
-- la commande à lancer est la suivante :
-- sqplus "scott/tiger@abmacipc"
e("sqlplus \"scott/tiger@abmacipc\"")
-- Effacement du fichier de sortie et du fichier de synchronisation
EffaceFichier(MonFichier)
EffaceFichier("fin.lst")
-- Le rôle de SQL*Plus est simplement de lancer le fichier "batch" nommé sql_04.sql
-- Ce fichier comportera une seule ligne: exec test (nnn); où nnn sera la valeur ayant été saisie par l'utilisateur
e("start /Users/temp/sql_04e.sql")
-- On va maintenant poser à l'utilisateur la question de la valeur limite de salaire
set w to display dialog "Quelle limite de salaire ?" default answer "2000"
-- Stockage de la réponse sous forme de chaine de caractères
set wSal to text returned of w
-- constitution de l'ordre (dynamique) contenu dans la fichier Batch
set w to "exec test (" & wSal & ");"
-- Ecriture de la chaîne de caractères w dans le fichier sql_04.sql
set wPath to "DisqueDur:Users:temp:sql_04e.sql"
set wFic to open for access file (wPath) with write permission
set L to get eof wFic
write w to wFic
close access wFic
-- Puis on quitte SQL Plus
e("exit")
-- Déclenchement de la suite de commandes Unix, en mode synchrone
doUnix(wT)
-- Synchronisation avec les commandes Unix, par attente de la présence du fichier "fin.lst"
AttenteFinSqlPlus()
-- Affichage du résultat de la requête à Oracle sous forme de feuille Microsoft Excel
tell application "Microsoft Excel"
Activate
-- Ouverture du document créé par SQL*Plus (en format Tableau HTML)
Open ("DisqueDur:Users:temp:" & MonFichier)
end tell
-- on quitte l'application Terminal
QuitterTerminal()
-- Définition de la procédure d'envoi à Unix d'une ou plusieurs commandes
-- contenues dans la chaîne de caractères x
on doUnix(x)
tell application "Terminal"
do script x
end tell
end doUnix
-- Constitution d'une suite de commandes séparées par des retours à la ligne
on e(x)
-- return correspond à CHR(13)
set wT to wT & return & x
end e
-- Suppression d'un fichier du répertoire /Users/temp
on EffaceFichier(xFic)
-- Tous droits réservés, RCI Informatique SA, 2004
tell application "Finder"
tell folder "temp" of folder "Users" of startup disk
try -- Le fichier peut ne pas exister
delete file xFic
end try
end tell -- folder
end tell -- finder
end EffaceFichier
-- Boucle d'attente de la présence du fichier "fin.lst"
on AttenteFinSqlPlus()
-- Tous droits réservés, RCI Informatique SA, 2004
tell application "Finder"
tell folder "temp" of folder "Users" of startup disk
repeat
if exists file "fin.lst" then return
end repeat
end tell -- folder
end tell -- finder
end AttenteFinSqlPlus
-- Pour quitter l'application Terminal
on QuitterTerminal()
tell application "Terminal"
quit
end tell
end QuitterTerminal
5 - Le source Oracle PL/SQL
procedure SCOTT.test (x in varchar2) is
-- Cette procedure PL/SQL Oracle, selectionne toutes les fiches
-- de la table EMP dont les salaires sont superieurs a une certaine limite
-- Le resultat est ecrit dans un fichier sous forme de texte tabule
-- Tous droits réservés RCI Informatique SA
-- www.rci-informatique.fr
-- www.index-informatique.net
wFH Utl_File.File_Type; -- Variable de la structure de fichier
io integer;
w varchar2 (5000);
tab varchar2(1) := chr(9); -- Tabulateur
-- Requete SQL dynamique en fonction de l'argument x fourni a l'appel de la procedure
Cursor C is select D.Dname, D.Loc, E.Ename, E.job, E.sal -- colonnes demandees
from Scott.DEPT D, -- informations des entreprises, avec l'alias D
Scott.EMP E -- celles des employes, avec l'alias E
where D.deptno = E.deptno -- la jointure !
and
E.sal >= to_number (x); -- clause de selection
-- Routines d'ecriture et de lecture dans des fichiers texte --
-- utilisant la variable wFH (globale à ce niveau)
procedure Newfile (xDir in varchar2, xFile in varchar2) is
begin
wFH := Utl_File.Fopen (xDir, xFile, 'w');
end;
procedure Writeln (xLine in varchar2) is
begin
Utl_File.Put_Line (wFH,xLine);
end;
procedure CloseFile is
begin
Utl_File.Fclose (wFH);
end;
-- Partie principale du programme
begin
NewFile ('/Users/temp', 'resultat_04.lst'); -- Ouverture en écriture d'un nouveau fichier
-- Ecriture d'une ligne de titre
Writeln ('Tous les employés dont le salaire est supérieur à ' || x); Writeln ('');
-- Ouverture, parcours et fermeture (implicites) du curseur
for m in C loop
-- Constitution d'une ligne de resultat avec des tabulations entre les champs
w := m.Dname || tab || m.Loc || tab || m.Ename || tab || m.job || tab || m.sal;
Writeln (w); -- Ecriture d'une ligne en sortie
end loop;
CloseFile; -- fermeture du fichier de sortie
-- Création du fichier fin.lst, destiné à indiquer la création du fichier de données
NewFile ('/Users/temp', 'fin.lst'); Writeln (''); CloseFile;
end;
