Execute Immediate paramétrable

**thevirgin** · 08/05/2012, 17h15

Bonjour tout le monde,

J'aimerais paramatrer le chargement des tables d'un schéma à partir d'un autre schéma de base de données.

Pour ce faire, j'ai prévu une table qui contient les noms des tables à charger ( nom_table1, nom_table2). Et grâce à un curseur qui boucle sur cette table, je fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Execute immediate( 'create table' || table1 'as select * from '||table2);

Or le problème, est que cette création ne se fait pas, sachant bien qu'elle marche si je l'exécute table par table !

PS : J'ai déjà utilisé le Execute immediate avec le drop et ça marche !

Merci d'avance,

A vous

**Vld44** · 08/05/2012, 18h16

salut

la commande n'est pas bonne;
1- la concaénation est bizarre, il manque une partie
2- il faut utiliser USING et les variables :1, :2 ... et non pas concaténer
3- tu as oublié les espaces autour de || table, enfin ça n'a plus d'importance si tu suis le point 2.

a+

**thevirgin** · 09/05/2012, 09h25

Merci pour votre réponse.

Si j'ai bien compris, voilà ce que je devrais faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 
FOR v_cur in  CUR_TABLE_A_CHARGER Loop
 
                begin 
                     v_req := 'Create Table :1 as select * from :2';
                     v_table1:= 'sch1.'||v_cur.table1;
                     v_table2 := 'sch2.'||v_cur.table2;
 
                     Execute immediate v_req using v_table1, v_table2;
 
                Exception when others then DBMS_OUTPUT.PUT_LINE ('Problème de chargement de la table :' ||v_cur.table1 ||sqlerrm);
 
                end;
 
        end loop;

Mais ça ne marche toujours pas !

Merci

**McM** · 09/05/2012, 10h11

La règle de base de l'execute immediate : Faire une sortie DBMS_OUTPUT de la chaine de caractère afin de voir le code exécuté..
Sur ton premier post, tu aurais vu

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
'create table' || table1 'as select * from '||table2
 
create tableTOTOas select * from TOTO2

2- il faut utiliser USING et les variables :1, :2 ... et non pas concaténer

NON. Ceci n'est à faire que pour les variables. On ne peut pas binder des noms de tables, des mots clés, etc..
Donc utiliser une concaténation

**thevirgin** · 09/05/2012, 11h11

Le Oui vous avez raison, il y avait un problème d'espace.

Mais la requête de s'exécute toujours pas !

**McM** · 09/05/2012, 11h25

As-tu vérifié la chaine exécutée ?

Poste le code complet parce que comme ça c'est difficile à dire.

**thevirgin** · 09/05/2012, 11h41

Voici ma procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
PROCEDURE  charger_schema is
 
    v_req           varchar2(300);
 
 
    --- Curseur qui ramène les noms des tables à charger 
    cursor  CUR_TABLES_A_CHARGER IS
    select  table1, table2 
                  from TABLES_A_CHARGER; 
 
 
 
  begin 
 
      FOR v_cur in  CUR_TABLES_A_CHARGER Loop
 
           begin 
                Execute immediate ('Drop Table  ' ||v_cur.table_sid);
 
 
            Exception when others then DBMS_OUTPUT.PUT_LINE ('Problème de la suppression de la table :' ||v_cur.table1 ||sqlerrm);
 
            end;
 
       end loop;
 
 
 
       FOR v_cur1 in  CUR_TABLES_A_CHARGER Loop
 
                begin 
 
                      v_req := 'Create Table  ' || v_cur1.table1 || '  as select * from  GAUP4.' || v_cur1.table2;
                      DBMS_OUTPUT.PUT_LINE (v_req);
 
                     Execute immediate (v_req) ;
                 DBMS_OUTPUT.NEW_LINE;
                Exception when others then DBMS_OUTPUT.PUT_LINE ('Problème de chargement de la table :' ||v_cur1.table1 ||sqlerrm);
 
                end;
 
        end loop;
 
 
 
 end    charger_schema;

Ce que je trouve bizarre, est que, quand j'exécute toute la procédure le drop marche et le create non !
Or, quand je prends la boucle qui fait le create et je l'éxécute toute seule, elle marche !

PS: DBMS_OUTPUT.PUT_LINE ne m'affiche rien, est ce normal ?

**McM** · 09/05/2012, 11h59

Si tu lances ta procédure sous sql+, il faut faire un set serveroutput on
Sous toad, faut cliquer sur l'onglet dbms_output
Sous sqldev je sais plus mais c'est en fonction de la session complète (mais je peux retrouver si tu es dessus)

Attention si la procédure sort en erreur, aucun dbms ne s'affiche (mais vu que tu fait un catch, pas de soucis.
Attention aussi, suivant ta version de base (<10g), le dbms_output est limité à 255 caractères.

Bon tente d'afficher tout sans faire les execute, afin de voir les codes générés.
Si tu n'arrives pas à afficher le dbms, tu peux carrément sortir en erreur avec un raise_application_error(-20001, 'erreur'|| sqlerrm)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
30
31
PROCEDURE  charger_schema 
IS
    v_req           varchar2(300);
 
    --- Curseur qui ramène les noms des tables à charger 
    CURSOR  CUR_TABLES_A_CHARGER IS
    SELECT  table1, table2 FROM TABLES_A_CHARGER; 
 
  BEGIN 
 
      FOR v_cur IN  CUR_TABLES_A_CHARGER 
      LOOP
          v_req :=  'Drop Table  ' ||v_cur.table_sid;
					DBMS_OUTPUT.put_line(v_req);
          /*BEGIN
          	Execute immediate v_req;
 			     	EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE ('Problème de la suppression de la table :' ||v_cur.table1 ||SQLERRM);
 					END;*/
      END LOOP;
 
      FOR v_cur1 IN  CUR_TABLES_A_CHARGER
      LOOP
          v_req := 'Create Table  ' || v_cur1.table1 || '  as select * from  GAUP4.' || v_cur1.table2;
          DBMS_OUTPUT.PUT_LINE (v_req);
          /*BEGIN
          	Execute immediate v_req;
          EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE ('Problème de chargement de la table :' ||v_cur1.table1 ||SQLERRM);
 					END;*/
      END LOOP;
 
END    charger_schema;

**thevirgin** · 09/05/2012, 12h46

Merci pour votre disponibilité

Je travaille sur Toad, la version de la base est 10g et l'onglet DBMS_OUTPUT est vide !

J'ai fait ce que vous m'avez proposée ( juste afficher sans exécuter) mais rien ne s'affiche.

**McM** · 09/05/2012, 13h16

Sous Toad, faut cliquer sur le rond rouge (première icone de l'onglet dbms_output) pour le mettre à ON (vert)

**Vld44** · 09/05/2012, 14h27

autant pour moi sur le binding de table!

**thevirgin** · 09/05/2012, 14h28

Oui vous aviez raison

maintenant toutes les requêtes s'affichent et elles sont correctes.

J'ai en pris par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
Create Table ACTIVITE_PROFESSION  as select  *  from  ACTIVITE_PROFESSION_ASS

et je l'ai exécuté et ça a marché.

Mais la procédure ne marche pas.

Merci

**Scriuiw** · 09/05/2012, 15h09

Mais la procédure ne marche pas.

C'est-à-dire ?
Quel est le message d'erreur ?

Peut-être une histoire de droit (A confirmer avec le message d'erreur).

Selon vos dire cette instruction fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE TABLE ACTIVITE_PROFESSION  AS SELECT  *  FROM  ACTIVITE_PROFESSION_ASS

Pouvez-vous exécuter dans ce cas celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DECLARE
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ACTIVITE_PROFESSION  AS SELECT  *  FROM  ACTIVITE_PROFESSION_ASS';
END;
/

Et nous dire qu'elle en est le résultat

**thevirgin** · 09/05/2012, 15h28

Quand j'exécute la procédure charger_schema ( voir le code ci-dessus ) aucune table ne se crée et aucun message d'erreur ne s'affiche ! ( par contre le drop se fait )

Le code que vous m'avez demandée de tester s'exécute sans problème et la table se crée.

**Scriuiw** · 09/05/2012, 15h48

Quand j'exécute la procédure charger_schema ( voir le code ci-dessus ) aucune table ne se crée et aucun message d'erreur ne s'affiche ! ( par contre le drop se fait )

Comment se fait-il que le DROP se fasse si le CREATE ne fonctionne pas ?
D'autre part, si la table n'existe pas le DROP doit renvoyer une erreur.
Est-ce le cas ?

**thevirgin** · 09/05/2012, 16h45

Aucune table ne se créé car le problème est le suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORA-01031: privilèges insuffisants

Mais ce que je trouve anormal, est que ce problème ne se pose que si j'exécute le create à partir de ma procédure ( qui est dans un package ) .

Est ce que cela veut dire que le package manque de privilèges ??? ( peut être que je dis n'importe quoi mais SVP expliquez moi si vous savez la raison )

Merci Beaucoup pour votre aide

**Scriuiw** · 09/05/2012, 16h54

Extrait du tutorial sur les Rôles et Privilège par Sheik Yerbouti

Les privilèges assignés à un utilisateur via un rôle ne permettent pas la création de vues, de procédures, de fonctions de packages ni de clés étrangères par l'intermédiaire de code PL/SQL dynamique (DBMS_SQL, execute immediate)
Pour créer ces objets via PL/SQL, l'utilisateur doit avoir reçu directement les privilèges requis via l'instruction GRANT

Il faut donc que tu assignes le droit CREATE TABLE et DROP TABLE à ton User.

Je pensais qu'en exécutant la procédure anonyme on aurait pu détecter ce type de problème

Le code que vous m'avez demandée de tester s'exécute sans problème et la table se crée.

**thevirgin** · 09/05/2012, 17h28

Oui vous aviez raison

Merci beaucoup à tous pour votre aide

Execute Immediate paramétrable

PL/SQL Oracle

Discussions similaires

Partager

Partager