[Oracle 9] Problème SQL dynamique

Version imprimable

Bonjour,

pour récupérer un curseur en paramètre de sortie dans ma procédure stockée j'ai créé un code dynamique.

Ma première version était la suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE OR REPLACE PROCEDURE PS_GET_DETAILS_LOCALISATION
(NOM_TABLE IN VARCHAR2,
ID_TABLE IN VARCHAR2,
io_cursor IN OUT LP_LOCALISATION.C_ST_LOCALISATION )
AS
BEGIN
OPEN io_cursor FOR
SELECT ID, CODE, DESI FROM aii.VW_LOCALISATION_UP
WHERE ID = TO_NUMBER(ID_TABLE);
END;
/
Show errors

Avec le test suivant pas de problème :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SET SERVEROUTPUT ON;  
DECLARE  
        CUR LP_LOCALISATION.C_ST_LOCALISATION;  
        rec VW_LOCALISATION_UP%ROWTYPE ;  
BEGIN  
        PS_GET_DETAILS_LOCALISATION('UP','174',CUR);  
        LOOP  
                FETCH CUR INTO rec ;  
                EXIT 
        WHEN CUR%NOTFOUND ;  
                dbms_output.put_line( rec.ID || ' ' ||rec.CODE ) ;  
        END LOOP ;  
        CLOSE CUR ;  
END;

Maintenant si je créé mon curseur dynamiquement

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE OR REPLACE PROCEDURE PS_GET_DETAILS_LOCALISATION
(NOM_TABLE IN VARCHAR2,
ID_TABLE IN VARCHAR2,
io_cursor IN OUT LP_LOCALISATION.C_ST_LOCALISATION )
AS
 
vlStr	VARCHAR2(500) ;
BEGIN
 
vlStr := ' OPEN io_cursor FOR ' ;
vlStr := vlStr || ' SELECT ID, CODE, DESI ' ;
vlStr := vlStr || ' FROM aii.VW_LOCALISATION_' || NOM_TABLE ;
vlStr := vlStr || ' WHERE ID = TO_NUMBER(' || ID_TABLE || ')' ;
 
DBMS_UTILITY.EXEC_DDL_STATEMENT(vlStr);
END;
/
Show errors

La procédure compile mais mon test ne passe plus et ressort avec l'erreur

Code:

1
2
3
4
5
6
7
8
9
10
 
DECLARE
*
ERREUR à la ligne 1 :
ORA-00900: Instruction SQL non valide
ORA-06512: à "SYS.DBMS_SYS_SQL", ligne 826
ORA-06512: à "SYS.DBMS_SQL", ligne 32
ORA-06512: à "SYS.DBMS_UTILITY", ligne 550
ORA-06512: à "AII.PS_GET_DETAILS_LOCALISATION", ligne 15
ORA-06512: à ligne 5

Merci par avance de votre aide.

12/09/2006, 15h32
bouyao

Bonjour,

Ca peut être un problème de privilège :

Code:

grant select on aii.VW_LOCALISATION_UP to utilisateur

utilisateur : c'est le nom de ton utilisateur qui execute la procedure
12/09/2006, 15h35
Dadou74

Bonjour,
ben j'ai bien peur que non puisque je suis connectée en tant qu'admistrateur de la base, en tant que AII justement.
12/09/2006, 15h42
SheikYerbouti

Etes-vous sûr que DBMS_UTILITY.EXEC_DDL_STATEMENT soit la bonne fonction pour ouvrir un curseur :koi:
pour moi, DDL a toujours voulu dire Data Description Language...
12/09/2006, 15h46
Dadou74
Ben justement non je ne suis pas sûre que ce soit la bonne méthode.
Si j'essaie avec execute immediate il me retourne l'erreur suivante :
Code:

1 2 3 4 5 6 7 DECLARE * ERREUR à la ligne 1 : ORA-00900: Instruction SQL non valide ORA-06512: à "AII.PS_GET_DETAILS_LOCALISATION", ligne 15 ORA-06512: à ligne 5

Essayez plutôt cette syntaxe:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  cur SYS_REFCURSOR ;
  req VARCHAR2(2000) ;
  var EMP%ROWTYPE ;
BEGIN  
  req := 'select * from emp' ;
  OPEN cur FOR req ;
  LOOP
    FETCH cur INTO var ;
	EXIT WHEN cur%NOTFOUND ;
	dbms_output.put_line( 'ename = ' || var.ENAME ) ;
  END LOOP ;
  CLOSE cur ;
END;

J'avais déjà essayé de la façon suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SET SERVEROUTPUT ON;   
DECLARE   
        io_cursor LP_LOCALISATION.C_ST_LOCALISATION;   
        rec VW_LOCALISATION_UP%ROWTYPE ;   
begin   
        --PS_GET_DETAILS_LOCALISATION('UP','174',io_cursor);   
        OPEN io_cursor FOR SELECT ID, CODE, DESI FROM VW_LOCALISATION_UP WHERE ID = TO_NUMBER(174);   
        LOOP   
                FETCH io_cursor INTO rec ;   
                EXIT   
        WHEN io_cursor%NOTFOUND ;   
                dbms_output.put_line( rec.ID || ' ' ||rec.CODE ) ;   
        END LOOP ;   
        CLOSE io_cursor ;   
END;

Où OPEN io_cursor FOR SELECT ID, CODE, DESI FROM VW_LOCALISATION_UP WHERE ID = TO_NUMBER(174); correspond à la valeur de vlStr dans ma PS.

Cela fonctionne très bien mais je ne teste pas ma PS en fait.

En fait je suis en train de réaliser des pages d'un site Web avec VS 2005, qui doit fonctionner aussi bien avec Oracle que SqlServer 2005 et je souhaite remplir mes grilles de données avec des procédures stockées, des vues, ...
Je ne rencontre aucun problème avec Sql Server pour qui j'ai créé la PS suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE PROCEDURE [PS_GET_DETAILS_LOCALISATION] @nomTable VARCHAR(50), @id VARCHAR(50) AS DECLARE @REQUETE VARCHAR(4000) SET @REQUETE = 'SELECT ID, CODE, DESI ' + ' FROM aii.VW_LOCALISATION_' + @nomTable + ' WHERE ID = ' + @id EXEC(@REQUETE) GO

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE OR REPLACE PROCEDURE PS_GET_DETAILS_LOCALISATION
(NOM_TABLE IN VARCHAR2,
ID_TABLE IN VARCHAR2,
IO_CURSOR IN OUT SYS_REFCURSOR)
AS
 
vlStr	VARCHAR2(500) ;
BEGIN
vlStr := 'SELECT ID, CODE, DESI ' ;
vlStr := vlStr || ' FROM VW_LOCALISATION_' || NOM_TABLE ;
vlStr := vlStr || ' WHERE ID = TO_NUMBER(' || ID_TABLE || ')' ;
 
OPEN IO_CURSOR FOR vlStr;
 
END;