Execute Immediate paramétrable

Version imprimable

08/05/2012, 17h15
thevirgin

Execute Immediate paramétrable

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:

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
08/05/2012, 18h16
Vld44

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+

Merci pour votre réponse.

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

Code:

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

09/05/2012, 10h11
McM
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:

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

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
09/05/2012, 11h11
thevirgin

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

Mais la requête de s'exécute toujours pas ! :(
09/05/2012, 11h25
McM

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

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

Voici ma procédure :

Code:

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 ?

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:

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;

09/05/2012, 12h46
thevirgin

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.
09/05/2012, 13h16
McM

Sous Toad, faut cliquer sur le rond rouge (première icone de l'onglet dbms_output) pour le mettre à ON (vert)
09/05/2012, 14h27
Vld44

autant pour moi sur le binding de table!
09/05/2012, 14h28
thevirgin
Oui vous aviez raison :) maintenant toutes les requêtes s'affichent et elles sont correctes.

J'ai en pris par exemple :
Code:

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
09/05/2012, 15h09
Scriuiw
Citation:

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:

CREATE TABLE ACTIVITE_PROFESSION AS SELECT * FROM ACTIVITE_PROFESSION_ASS

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

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
09/05/2012, 15h28
thevirgin

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.
09/05/2012, 15h48
Scriuiw

Citation:

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 ?
09/05/2012, 16h45
thevirgin

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

Code:

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 :)
09/05/2012, 16h54
Scriuiw

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

Citation:

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

Citation:

Le code que vous m'avez demandée de tester s'exécute sans problème et la table se crée.
09/05/2012, 17h28
thevirgin

Oui vous aviez raison :ccool:

Merci beaucoup à tous pour votre aide :)