[PL/SQL] connect en SQL dynamique

[mbgh1302]

Bonjour,
je souhaite créer n utilisateurs avec pour chacun la même base.
Je procède comme suit:
Je créé un utilisateur user_i, je me connecte avec ses ids et je lance un script pour la base.

Voici ma boucle:

Code :

1
2
3
4
5
6
7
8
9
10
 
FOR i IN 1..n loop
 dbms_output.put_line('CREATION DE L''UTILISATEUR '||i);
 execute immediate 'conn system/manager';
 execute immediate 'DROP USER user'||i' CASCADE';
 execute immediate 'CREATE USER user'||i' IDENTIFIED BY pass'||i;
 execute immediate 'GRANT CONNECT, DBA, RESOURCE TO user'||i;
 execute immediate 'conn user'||i||'/pass'||i;
 execute immediate 'start /opt/oracle/scripts/db.sql';
end loop;

J'ai l'erreur suivante:

Code :

1
2
3
4
5
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 23

à priori l'erreur vient du execute immediate 'connect system/manager'
Quelqu'un pourrait me dire comment contourner cela ?

J'ai trouvé ceci sur le web mais ça ne m'a pas trop convaincu :
http://www.thescripts.com/forum/thread65749.html

Merci d'avance.

[scheu]

Ca me paraitrait surprenant si on pouvait changer de user dans un même bloc PL/SQL
Tu peux éventuellement enregistrer ton script dans un fichier temporaire (avec spool ou utl_file) et l'exécuter ensuite via un sqlplus

[Delwyn]

Tu peux mettre la totalité du code ?

J'ai l'impression qu'il plante simplement sur le mot clé declare qui ne doit pas etre au bon endroit.

[mbgh1302]

Bonjour,
merci pour la réponse.
Effectivement, c'est ce que j'avais fait en attendant de trouver une solution...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
spool script.sql
declare
...
begin
...
FOR i IN 1..n loop
 dbms_output.put_line('CREATION DE L''UTILISATEUR '||i);
 dbms_output.put_line ('conn system/manager');
 dbms_output.put_line ('DROP USER user'||i' CASCADE');
 dbms_output.put_line ('CREATE USER user'||i' IDENTIFIED BY pass'||i);
 dbms_output.put_line ('GRANT CONNECT, DBA, RESOURCE TO user'||i);
 dbms_output.put_line ('conn user'||i||'/pass'||i);
 dbms_output.put_line ('start /opt/oracle/scripts/db.sql');
end loop;
...
end;
/
spool off;
----------
start script.sql

Mais j'aimerai pouvoir le faire directement à partir de PL/SQL...
Tout ceci s'inscrit dans un module et gérer des bouts de fichiers (i.e. scripts) va me compliquer encore la tâche.

Thanks ...

[mbgh1302]

Envoyé par Delwyn

Tu peux mettre la totalité du code ?
J'ai l'impression qu'il plante simplement sur le mot clé declare qui ne doit pas etre au bon endroit.

Cela m'étonnerait, j'ai déjà vérifié cette piste : J'ai viré les lignes de connexion avec execute immediate et tout marche normalement !

J'ai aussi testé ce code et il renvoi la même erreur :

Code :

1
2
3
4
5
6
7
8
9
connect system/manager;
DROP user user1 cascade;
CREATE user user1 IDENTIFIED BY id1;
GRANT dba, connect, resource TO user1;
declare
begin
execute immediate 'connect user1/id1';
end;
/

ce qui donne :

Code :

1
2
3
4
5
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 3

Merci quand même.
...

[Delwyn]

Envoyé par mbgh1302

Bonjour,
merci pour la réponse.
Effectivement, c'est ce que j'avais fait en attendant de trouver une solution...

Code :

Mais j'aimerai pouvoir le faire directement à partir de PL/SQL...
Tout ceci s'inscrit dans un module et gérer des bouts de fichiers (i.e. scripts) va me compliquer encore la tâche.

Thanks ...

Bonjour,

Il semblerait que tu ais oublié des pipes || après i sur les lignes :

Code :

1
2
 dbms_output.put_line ('DROP USER user'||i' CASCADE');
 dbms_output.put_line ('CREATE USER user'||i' IDENTIFIED BY pass'||i);

Par contre tu risques de pulveriser le buffer, y'a un thread plus bas qui donne une approche (utl_file).

Le code qui fonctionne chez moi :

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
 
SET serveroutput ON;
 
DECLARE
   I   NUMBER;
   N   NUMBER;
BEGIN
   N := 10;
 
   FOR I IN 1 .. N
   LOOP
      DBMS_OUTPUT.PUT_LINE ('CREATION DE L''UTILISATEUR ' || I);
      DBMS_OUTPUT.PUT_LINE ('CREATION DE L''UTILISATEUR ' || I);
      DBMS_OUTPUT.PUT_LINE ('conn system/manager');
      DBMS_OUTPUT.PUT_LINE ('DROP USER user' || I || ' CASCADE');
      DBMS_OUTPUT.PUT_LINE ('CREATE USER user' || I || ' IDENTIFIED BY pass'
                            || I
                           );
      DBMS_OUTPUT.PUT_LINE ('GRANT CONNECT, DBA, RESOURCE TO user' || I);
      DBMS_OUTPUT.PUT_LINE ('conn user' || I || '/pass' || I);
      DBMS_OUTPUT.PUT_LINE ('start /opt/oracle/scripts/db.sql');
   END LOOP;
END;
/

[mbgh1302]

Envoyé par Delwyn

Bonjour,
Il semblerait que tu ais oublié des pipes || après i sur les lignes :

Bonjour, oui c'est vrai j'ai oublié les pipes dans le post
mais dans le script elles y sont.
Le script fonctionne chez moi aussi.

[mnitu]

Pourriez-vous aller chercher dans SQL Reference la syntaxe de l'instruction connect ? Est-ce que vous l'avez trouvé ?

[Delwyn]

J'avais pas tout lu navré =)

On disais que je cherchais pas vraiment au bonne endroit.

Ceci-dit le fait d'avoir le mot clé declare sans déclarer de variable me choque un peu. Et peut-être oracle aussi.

Pour exemple si l'on utilise une structure de test if sans instruction oracle nous jette :

Code :

1
2
3
 
IF L_VAR = 0 THEN
END IF;

Ca ne compile pas ! Non mon bout de code n'est pas stupide ! Essaye de virer le mot clé declare pour voir, sait-on jamais.

[orafrance]

vous croyez pas qu'un connect en plein milieu d'une session PL/SQL ça va poser un problème ? Sachant en plus que conn est une commande SQL*Plus et pas SQL

ce code ne pourra évidemment jamais fonctionner