Discussion :

[dahmane2007]

Bonjour,

Je voudrais savoir comment mettre une instruction SQL PLUS dans un block PL/SQL ?.

Je m'explique:
En mode SQL PLUS, pour afficher le code source d'une proc, j'utilise la syntaxe suivante:

Code :

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

1
2
3
SELECT OBJECT_NAME 
          FROM USER_OBJECTS 
          WHERE OBJECT_TYPE='PROCEDURE'

Avec cette instruction, je pourrai afficher le code source d'une procédure; mais je veux utiliser ce code dans une procédure paramétrée que je donne comme paramètre un objet (soit une procédure ou fonction).

J'ai essayé ceci, la compilation déroule bien, mais il y a des erreurs quand je lance la 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
CREATE OR REPLACE PROCEDURE AFF_CODE_SOURCE(NOM_OBJET IN VARCHAR2)
IS
 
TXTOBJET VARCHAR2(30);
AUCUNE EXCEPTION;
 
BEGIN
 
   SELECT TEXT INTO TXTOBJET FROM USER_SOURCE
   WHERE  NAME=NOM_OBJET;
 
EXCEPTION
WHEN AUCUNE THEN 
   DBMS_OUTPUT.PUT_LINE(' ERREUR ... AUCUNE FONCTION OU PROCÉDURE N''A ÉTÉ CRÉE ');
 
END AFF_CODE_SOURCE;
 
-- BLOCK PL/SQL
BEGIN
-- APPEL DE LA PROCÉDURE
   AFF_CODE_SOURCE('REVENU_DEPT');
END;

Merci d'avance.

[NGasparotto]

mais il y a des erreurs quand je lance la procédure

Ah oui ? Lesquelles ?
Pas de message d'erreur, pas d'aide


Nicolas.

[fatsora]

Code sql :

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
CREATE OR REPLACE PROCEDURE AFF_CODE_SOURCE(NOM_OBJET IN VARCHAR2)
IS
 
TXTOBJET VARCHAR2(30);
AUCUNE EXCEPTION;
 
BEGIN
 
   SELECT TEXT INTO TXTOBJET FROM USER_SOURCE
   WHERE  NAME=NOM_OBJET;
 
EXCEPTION
WHEN AUCUNE THEN 
   DBMS_OUTPUT.PUT_LINE(' ERREUR ... AUCUNE FONCTION OU PROCÉDURE N''A ÉTÉ CRÉE ');
 
END AFF_CODE_SOURCE;

Code :

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

TXTOBJET VARCHAR2(30);]

trop petit !!

et

Code :

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

AUCUNE EXCEPTION; ]

autant mettre tout de suite NO_DATA_FOUND !!

de plus

Quelle version Oracle tu as ?

Il y a des erreurs en effet, mais tu devrais les donner ...

si tu as une version > = 9I tu as dbms_metadata
sinon avec user_source il y a aussi plein d'exemple

http://asktom.oracle.com/pls/asktom/...D:454220160386

[dahmane2007]

Voici les erreurs durant le lancement de la procédure avec:

Code :

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

EXECUTE NOMPROC('NOM PROCEDURE');

Code :

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

1
2
3
4
ERREUR à la ligne 1 :
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "user.AFF_CODE_SOURCE", line 9
ORA-06512: at line 1

En fait, j'utilise la version 10g d'Oracle.
Merci d'avance.

[NGasparotto]

Puisque ta requete retourne plus d'une ligne, tu dois gerer un curseur.

Nicolas.

[fatsora]

Bonjour,

Code sql :

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

1
2
3
4
 
   SELECT TEXT INTO TXTOBJET FROM USER_SOURCE
   WHERE  NAME= 'XXX'
   AND TYPE='PROCEDURE'

ca eviterait deja les name egaux mais de type differents

Sinon en 10G il y a DBMS_METADATA , deja disponible depuis 9I

[NGasparotto]

Bonjour,

Code sql :

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

1
2
3
4
 
   SELECT TEXT INTO TXTOBJET FROM USER_SOURCE
   WHERE  NAME= 'XXX'
   AND TYPE='PROCEDURE'

ca eviterait deja les name egaux mais de type differents

De plus USER_SOURCE contient une ligne par ligne de code pour la procedure/fonction/package retournee par la requete, d'ou la necessite du curseur.

Sinon en 10G il y a DBMS_METADATA , deja disponible depuis 9I

Je me trompe peut-etre, il me semble que le but ici n'est pas tant de retrouver le code source mais plutot de manipuler PL/SQL.

Nicolas.

[dahmane2007]

Merci pour vos réponses. En fait, le but de manipuler le code pl/sql en intégrant la commande SQL PLUS SELECT. J'ai utilisé un curseur avec un FETCH, mais il me ramène seulement le dernier mot (END.

Cette commande marche très bien en SQL PLUS (il me donne le code source de la procédure), mais dans un block pl/sql non, il ne marche pas.
Autre chose, comment utiliser DBMS_METADA.

Merci d'avance.

[NGasparotto]

Il te ramenes uniquement END ou cela ? Quelle est ton code ?
Tu devrais trier par LINE.

DBMS_METADATA et un package contenu des functions permetant de recuperer le code "source" (DDL) d'un objet, quel qu'il soit, table, vue...
C'est documente, bien entendu.
Le plus connu est :
select dbms_metadata.get_ddl('TABLE','TABLE_NAME',user) from dual;

Nicolas.

[dahmane2007]

Voici mon code:

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
CREATE OR REPLACE PROCEDURE AFF_CODE_SOURCE(NOM_OBJET IN VARCHAR2)
IS
 
CURSOR CUR_OBJET 
       IS
          SELECT TEXT FROM USER_SOURCE
          WHERE NAME=NOM_OBJET
          ORDER BY LINE;
 
--
TXTOBJET VARCHAR2(4000);
--AUCUNE EXCEPTION;
 
BEGIN
   OPEN CUR_OBJET;
   LOOP
   FETCH CUR_OBJET INTO TXTOBJET;
   EXIT WHEN CUR_OBJET%NOTFOUND;
   END LOOP;
 
   CLOSE CUR_OBJET;
   DBMS_OUTPUT.PUT_LINE(' CODE SOURCE ' || TXTOBJET);
 
 
   EXCEPTION
   WHEN NO_DATA_FOUND THEN 
   DBMS_OUTPUT.PUT_LINE(' ERREUR ... AUCUNE FONCTION OU PROCÉDURE N''A ÉTÉ CRÉE ');
 
END AFF_CODE_SOURCE;
 
 
BEGIN
 
   AFF_CODE_SOURCE('nom procedure');
END;

Voici ce qui me donne comme résultat:

Code :

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

1
2
SQL> EXECUTE AFF_CODE_SOURCE('nom proc');
CODE SOURCE END;

[fatsora]

C'est uniquement pour la partie didactique !!

car le fond est plus que discutable .

Code sql :

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

1
2
3
4
5
CREATE OR REPLACE procedure SCOTT.P1
as begin
dbms_output.put_line('P1');
end;
/

Code sql :

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
 
CREATE OR REPLACE PROCEDURE SCOTT.AFF_CODE_SOURCE(NOM_OBJET IN VARCHAR2)
IS
 TEXT_fin varchar2(32767);
 
CURSOR CUR_OBJET
       IS
          SELECT TEXT FROM USER_SOURCE
          WHERE NAME=NOM_OBJET
          ORDER BY LINE;
 
--
TXTOBJET VARCHAR2(4000);
--AUCUNE EXCEPTION;
 
BEGIN
   OPEN CUR_OBJET;
     DBMS_OUTPUT.PUT_LINE(' CODE SOURCE ');
   LOOP
   FETCH CUR_OBJET INTO TXTOBJET;
   EXIT WHEN CUR_OBJET%NOTFOUND;
 
 
 text_fin := text_fin ||txtobjet ;
 
 -- DBMS_OUTPUT.PUT_LINE( TXTOBJET);
       END LOOP;
     CLOSE CUR_OBJET;
 
 DBMS_OUTPUT.PUT_LINE( text_fin);
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE(' ERREUR ... AUCUNE FONCTION OU PROCEDURE N''A ETE CREE ');
 
END AFF_CODE_SOURCE;
/

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
 exec aff_code_source('P1');
CODE SOURCE
procedure P1
as begin
dbms_output.put_line('P1');
end;
 
PL/SQL procedure successfully completed.

[dahmane2007]

Merci infiniment. Ca marche très bien ton code.

[khalid_montreal]

Bonjour,
d'autre solution :

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
create or replace procedure visualiser_code_source(nom_objet varchar2)
is
/*i pour vérifier si nom_objet existe ou non 
si i garde la valeur 0 donc nom_objet n'existe pas 
si non nom_objet existe
*/
i int := 0;
--erreur_objet se déclenche si nom_objet n'existe pas
erreur_objet exception;
--cursor pour récupérer le code source de nom_objet
cursor cur is select user_source.text
from user_source
where user_source.name = upper(nom_objet);
begin
-- affichage des lignes source de nom_objet
for ligne in cur loop
dbms_output.put_line(ligne.text);
i := i +1;
end loop;
-- définition de l'exception erreur_objet 
if i = 0 then raise erreur_objet; end if;
exception
when erreur_objet then raise_application_error(-20398,'Objet '|| nom_objet ||' n''existe pas ');
end visualiser_code_source;

Salutations,
khalid