Insert OR Create dans une table

Version imprimable

18/01/2008, 06h51
Momodedf

Insert OR Create dans une table
Bonjour,

Je cherche à créer une procédure permentant d'insérer des valeurs dans une table, mais avant de vérifier l'existence de cette table et la créer si besoin.

J'avais penser à créer une exception permetant de créer la table si lors de l'insertion le server renvoyait une erreur, mais j'ai deux problemes à ce niveau :
1° je ne trouve pas l'exception levée pour un table not found.
2° est-il possible de rappeler la procedure à partir d'une exception de cette meme procedure.

Syntétiquement voilà e que je pensais faire (la syntaxe n'est pas respéctée c'est juste pour expliquer) :
Code:

1 2 3 4 5 6 7 PROCEDURE insert_or_create insert into .......; EXCEPTION when table not found : create table .....; call insert_or create; end;
18/01/2008, 08h46
wondersonic

SQL Dynamique

Pour pouvoir compiler correctement une procédure, tout les objets utilisés (tables, sequences, vues...) doivent exister. Ta table, si tu l'utilises dans un insert "simple" et qu'elle n'existe pas encore empêchera ta procédure de compiler.

Il te faut passer par du SQL dynamique (execute immediate, dbms_sql...).

De plus, tu devras octroyer le droit CREATE TABLE à ton utilisateur de manière explicite (pas au travers d'un rôle).

Pour vérifier qu'une table existe, compte par exemple le nombre de lignes renvoyées par un select sur la vue user_tables avec une clause WHERE adéquate.

Sinon... cherche ailleurs sur ce forum car cette question revient souvent.
18/01/2008, 10h09
Momodedf

Est-ce le seul moyen ?

En définitive le but est de créer un table si elle n'existe pas et d'insérer une nouvelle ligne dedans.

tu peux faire un truc comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE
   dummy   INTEGER;
BEGIN
 BEGIN
   SELECT 1
     INTO dummy
     FROM user_tables
    WHERE table_name = 'TATABLE';
 
  EXCEPTION
     WHEN NO_DATA_FOUND
      THEN
         EXECUTE IMMEDIATE 'CREATE TABLE tatable ...';
  END;
 
 INSERT INTO tatable...
END;
/

18/01/2008, 11h17
Momodedf

Oui c'est ce que j'ai décidé de faire en définitive, mais j'étais en train de chercher le nom de user_tables.
Et je trouve pu mes cours sur le dictionnaire de donnée ^^

Merci beaucoup.

Voilà aprés quelques péripéties j'en suis arrivé à ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE add_suspect ( employeeId employees.employee_id%TYPE) IS nb_table number(10); BEGIN SELECT 1 INTO nb_table FROM user_tables WHERE table_name = 'SUSPECTS'; EXECUTE IMMEDIATE 'INSERT INTO suspects SELECT employee_id, last_name, first_name, hire_date, salary, job_id FROM employees WHERE employee_id = ' || employeeId; EXCEPTION WHEN NO_DATA_FOUND THEN EXECUTE IMMEDIATE 'CREATE TABLE suspects(employee_id NUMBER(6), last_name VARCHAR2(7), first_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), job_id VARCHAR(10))'; END add_suspect; / show errors
Le probleme c'est que lors de l'appel, s'il la table n'existe pas elle est bien créé mais l'INSERT n'est pas fait.
Est-ce il possible de continuer procedure uen fois l'exception levée ?
Et si non est-ce que je peux dans l'exception relancer la procedure avec les parametre qu'elle avait reçu en entrée lors du premier appel ?

18/01/2008, 17h39
mnitu

Citation:

Envoyé par Momodedf

...
Le probleme c'est que lors de l'appel, s'il la table n'existe pas elle est bien créé mais l'INSERT n'est pas fait.
Est-ce il possible de continuer procedure uen fois l'exception levée ?
Et si non est-ce que je peux dans l'exception relancer la procedure avec les parametre qu'elle avait reçu en entrée lors du premier appel ?

Ca va faire très bisare un appel recursive dans le bloc d'exception. Tu peut simplement ajouter l'insert une deuxième fois après avoir créer la table. Et si cela ne te plait pas tu devrai commencer par tester l'existence de la table avant de inserer.

Bonjour,

Un truc comme ca serait possible ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE add_suspect (
   employeeId    employees.employee_id%TYPE) IS
   nb_table number(10);
 
BEGIN
	SELECT count(*) INTO nb_table FROM user_tables WHERE table_name = 'SUSPECTS';
 
	IF nb_table = 0 
	then
		EXECUTE IMMEDIATE 'CREATE TABLE suspects(employee_id NUMBER(6), last_name VARCHAR2(7),
		first_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), job_id VARCHAR(10))';
	END IF;
 
	INSERT INTO suspects 
		SELECT emp.employee_id, emp.last_name, emp.first_name, emp.hire_date, emp.salary, emp.job_id 
		FROM employees  emp WHERE emp.employee_id = employeeId;
 
 
END add_suspect;
/
SHOW errors

21/01/2008, 09h51
orafrance

tu peux pas tester ? :roll:

Voilà merci pour vos réponses, j'ai réussi.
Voilà le code final (ya pas de create or replace devant procedure parceque c'est une procedure overridé dans un package).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
	PROCEDURE add_suspect (lastName employees.last_name%TYPE, employeeId employees.employee_id%TYPE) IS
	   nb_table NUMBER(10);
	BEGIN
		SELECT 1 INTO nb_table FROM user_tables WHERE table_name = 'SUSPECTS';
		EXECUTE IMMEDIATE 'INSERT INTO suspects SELECT employee_id, last_name, first_name, 
		hire_date, salary, job_id FROM employees WHERE last_name = :1 AND employee_id = :2'
									USING lastName, employeeId;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN 
		EXECUTE IMMEDIATE 'CREATE TABLE suspects(employee_id employees.last_name%TYPE, last_name VARCHAR2(25),
		first_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), job_id VARCHAR(10))';
		EXECUTE IMMEDIATE 'INSERT INTO suspects SELECT employee_id, last_name, first_name, 
		hire_date, salary, job_id FROM employees WHERE last_name = :1 AND employee_id = :2'
									USING lastName, employeeId;
	END add_suspect;

Tant mieux si ça marche mais je précise pour ceux qui tomberait sur ce post que c'est une très mauvaise pratique, il aurait fallu écrire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
	PROCEDURE add_suspect (lastName employees.last_name%TYPE, employeeId employees.employee_id%TYPE) IS
	   nb_table NUMBER(10);
	BEGIN
		SELECT 1 INTO nb_table FROM user_tables WHERE table_name = 'SUSPECTS';
		INSERT INTO suspects SELECT employee_id, last_name, first_name, 
		hire_date, salary, job_id FROM employees WHERE last_name = lastName AND employee_id = employeeId;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN 
		EXECUTE IMMEDIATE 'CREATE TABLE suspects(employee_id employees.last_name%TYPE, last_name VARCHAR2(25),
		first_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), job_id VARCHAR(10))';
		INSERT INTO suspects SELECT employee_id, last_name, first_name, 
		hire_date, salary, job_id FROM employees WHERE last_name = lastName AND employee_id = employeeId;
	END add_suspect;

ou même :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
PROCEDURE add_suspect (lastName employees.last_name%TYPE, employeeId employees.employee_id%TYPE) IS
	   nb_table NUMBER(10);
	BEGIN
          BEGIN
		SELECT 1 INTO nb_table FROM user_tables WHERE table_name = 'SUSPECTS';
	EXCEPTION
		WHEN NO_DATA_FOUND THEN 
		EXECUTE IMMEDIATE 'CREATE TABLE suspects(employee_id employees.last_name%TYPE, last_name VARCHAR2(25),
		first_name VARCHAR2(25), hire_date DATE, salary NUMBER(8,2), job_id VARCHAR(10))';
        END;
 
		INSERT INTO suspects SELECT employee_id, last_name, first_name, 
		hire_date, salary, job_id FROM employees WHERE last_name = lastName AND employee_id = employeeId;
	END add_suspect;

pas de EXECUTE IMMEDIATE pour du DML sauf si les noms de tables ou colonnes sont paramétrables.

24/01/2008, 13h03
Momodedf

Ah ben j'ai du mal comprendre cette phrase alors :

Citation:

Pour pouvoir compiler correctement une procédure, tout les objets utilisés (tables, sequences, vues...) doivent exister. Ta table, si tu l'utilises dans un insert "simple" et qu'elle n'existe pas encore empêchera ta procédure de compiler.

Il te faut passer par du SQL dynamique (execute immediate, dbms_sql...).
24/01/2008, 14h56
orafrance

ha oui, en effet, j'avais pas relevé ce "détail" :(