Le META SQL est un moyen très pratique pour le développeur ou le DBA d’automatiser certaines taches répétitives fastidieuses.
Le principe est d’écrire un petit script qui génère l’ensemble des ordres SQL désirés, en s’appyant sur le dictionaire de données. Il suffira ensuite de lancer ce script et le tour est joué.
Si l’on souhaite par exemple vider toutes les tables d’un utilisateur sans détruire leur contenu, et éviter de faire une liste de ses tables à partir du dictionnaire et ensuite saisir une liste interminable de ‘TRUNCATE TABLE … », la solution c’est le SQL dynamique.

On peut concaténer des constantes chaines de caractères à des variables ramenées par un SELECT en SQL. On utilisera cette possibilité pour faire du SQL dynamique.
Ainsi on peut écrire :

SQL> SELECT 'Bonjour mon cher '|| nom_client FROM tab_clients;

le résultat est du style :

Bonjour mon cher MARTIN
Bonjour mon cher DUPONT
Bonjour mon cher DURAND
...

De manière un peu plus sophistiquée je peux créer de la sorte une suite d’ordres SQL de destruction, de mes tables. La liste de mes tables est données par l’ordre SQL suivant (si je suis connecté dans mon compte évidemment…) :

SQL> SELECT table_name from USER_TABLES;

le résultat est  cette fois :

SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
ANNUAIRE
BONUS
DEPT
EMP
4 ligne(s) sélectionnée(s).

si je concatène maintenant l’ordre ‘DROP TABLE’ et le caractère ‘;’ à la fin j’obtiens :

SQL> select 'DROP TABLE '||table_name||';'
from user_tables;
'DROP TABLE'||TABLE_NAME||';'
------------------------------------------
DROP TABLE ANNUAIRE;
DROP TABLE BONUS;
DROP TABLE DEPT;
DROP TABLE EMP;
4 ligne(s) sélectionnée(s).

si je supprime maintenant les lignes superflues par les commandes adéquates d’environnement SQL*Plus:

SET PAGESIZE 0
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAUSE OFF

j’obtiens cette fois :

SQL>select 'DROP TABLE '||table_name||';'
from user_tables;
DROP TABLE ANNUAIRE;
DROP TABLE BONUS;
DROP TABLE DEPT;
DROP TABLE EMP;

il ne reste plus qu’a faire un spool de cette sortie dans un fichier drop.sql , et d’executer ensuite le fichier (start ou @drop.sql sous sqlplus). Le script complet SQL dynamique est le suivant :

SET PAGESIZE 0
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAUSE OFF
SPOOL drop.sql
select 'DROP TABLE '||table_name||';'
from user_tables;
SPOOL OFF
@DROP.SQL

Pour plus de détails voir les exemples ci-après …

suppression de tous les objets d’un user

 REM DROP_OBJET.SQL
 REM a lancer sous sqlplus a partir du compte SYSTEM
 REM SET PAUSE OFF
 SET VERIFY OFF
 PROMPT Entrez le nom de l'utilisateur : ACCEPT NOM
 PROMPT Entrez le type d'objet (TABLE, VIEW, SYNONYM, ...) :
 ACCEPT OBJET
 SET PAGES 0
 SET ECHO OFF
 SET TERMOUT OFF
 SET FEEDBACK OFF
 REM spool Unix, chemin windows\temp a substituer par exemple
 SPOOL /tmp/drop.tmp
 SELECT 'DROP '||'&OBJET'||' '||'&NOM'||'.'||OBJECT_NAME||';'
 FROM DBA_OBJECTS
 WHERE OWNER='&NOM'
 /
 SPOOL OFF
 PROMPT DROP des &OBJET de &NOM en cours...
 REM remplacer par le chemin adéquat si Windows
 START /tmp/drop.tmp
 REM mettre de DEL qui va bien si Windows...
 !rm /tmp/drop.tmp

vidage de toutes les tables d’un user

 REM TRUNC_TABS_USER.SQL
 REM a lancer sous SQLPlus a partir du compte SYSTEM
 REM
 SET PAUSE OFF
 SET VERIFY OFF
 PROMPT Entrez le nom de l'utilisateur : ACCEPT NOM
 SET PAGES 0
 SET ECHO OFF
 SET TERMOUT OFF
 SET FEEDBACK OFF
 REM fichier temporaire Unix, changer le chemin si windows...
 SPOOL /tmp/trunc.tmp
 SELECT 'TRUNCATE TABLE '||'&NOM'||'.'||TABLE_NAME||';'
 FROM DBA_TABLES
 WHERE OWNER='&NOM'
 /
 SPOOL OFF
 PROMPT TRUNCATE des tables de &NOM en cours...
 START /tmp/trunc.tmp
 REM suppression tempon Unix, substituer DEL si Windows...
 !rm /tmp/trunc.tmp