Procédure : Nom de schéma Oracle paramétré [Résolu]

[pressdell]

Bonjour,

Je rencontre un petit soucis sans doute simple à éviter.
J'ai une procédure stockée dans laquelle je déclare un curseur.

Code :

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE SCHEMA1.MA_PROC(SCHEMA2 IN string)
IS
 
    CURSOR Mon_Curseur IS
    SELECT * FROM SCHEMA2.MA_TABLE;
 
BEGIN
    [...]
END;

Le soucis est que lors de la création de mon curseur, le bloc PL n'arrive pas à interpréter SCHEMA2 comme ma variable d'entrée, ce que je peux comprendre.

Après BEGIN, j'arrive sans soucis à utiliser ma variable SCHEMA2, en la concaténant à du texte par exemple.

Comment pourrais-je créer mon curseur à partir de la requête prenant en compte le bon nom de schéma Oracle?

D'avance merci pour vos indications.

Bien cordialement,
pressdell.

[lola06]

Voici la méthode pour déclarer et ouvrir ton curseur dans ton BEGIN.

[mnitu]

Utilisez Sql Dynamique ou supprimer schema2 et utilisez authid avec invoker rights dans la procédure.

[pressdell]

Merci infinimment Lola, j'avoues ne pas avoir assez cherché.
Merci également mnitu!

Bonne journée à vous.

Cordialement.

[pressdell]

Je ré-ouvre ce post car je rencontre un autre problème.

Soit le code suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE PROCEDURE SCHEMA1.MA_PROC(SCHEMA2 IN string)
IS
 
    TYPE ref_cursor_type IS REF CURSOR;
    Mon_curseur ref_cursor_type;
    Ma_ligne Mon_curseur%ROWTYPE;
 
BEGIN
    OPEN Mon_curseur FOR 'SELECT * FROM ' || SCHEMA2 || '.MA_TABLE';
    LOOP
        BEGIN
              FETCH Mon_curseur INTO Ma_ligne;
              EXIT WHEN Mon_curseur%NOTFOUND;
        END;
    END LOOP;
    CLOSE Mon_curseur;
END;

Afin d'effectuer un FETCH, j'ai besoin d'une variable dont le type m'est inconnu (Car, comme vu tout à l'heure, je ne peux pas connaître à l'avance le schéma Oracle sur lequel est la table).

J'essaye donc d'attribuer à la variable Ma_ligne, la ligne du curseur Mon_curseur courante.

J'ai déclarer Ma_ligne comme suit :

Code :

Ma_ligne Mon_curseur%ROWTYPE;

Mais cela ne compile pas...

PLS-00320: déclaration de type de cette expression est incomplète ou mal structurée

[lola06]

Si j'ai bien compris tu vas chercher des informations dans une table suivant le schéma entré par l'utilisateur.

Est-ce que la structure de tes tables sont les mêmes (quelque soit le schéma) ?
--> si oui tu peux déclarer ta ligne comme ça :

Code :

 Ma_ligne MA_TABLE%ROWTYPE;

[pressdell]

Merci pour ton aide Lola.

Je vais effectivement chercher des données dans une table dont je ne connais pas le schéma à l'avance.

Quelque soit le schéma, ma table a la même structure.

J'ai déclaré Ma_ligne comme tu le mentionne, mais j'obtiens l'erreur suivante :

PLS-00201: l'identificateur 'MA_TABLE' doit être déclaré

Ma procédure est sur un schéma Oracle différent de celui de la table MA_TABLE.
Afin de déclarer une variable de type ligne relative à MA_TABLE, je dois préciser le schéma Oracle. Le code suivant fonctionne par exemple :

Code :

Ma_ligne SCHEMA2.MA_TABLE%ROWTYPE;

Je sais qu'il est possible d'utiliser le %ROWTYPE d'un curseur, mais ceci ne semble pas fonctionner avec les curseurs implicites...

[lola06]

C'est normal que tu ne puisse pas utiliser le %rowtype sur la curseur puisqu'il n'est pas encore déclaré.

Puisque tes tables sont les mêmes dans tous les schémas alors laisse le code suivant :

Code :

Ma_ligne SCHEMA2.MA_TABLE%ROWTYPE;

Normalement ça marchera quelque soit le schéma entré par l'utilisateur.

[pressdell]

C'est normal que tu ne puisse pas utiliser le %rowtype sur le curseur puisqu'il n'est pas encore déclaré.

En fait, dans mon précédent message, je déclarais bien mon curseur avant :

Code :

1
2
3
4
5
6
CREATE OR REPLACE PROCEDURE SCHEMA1.MA_PROC(SCHEMA2 IN string)
IS
 
    TYPE ref_cursor_type IS REF CURSOR;
    Mon_curseur ref_cursor_type;
    Ma_ligne Mon_curseur%ROWTYPE;

Puisque tes tables sont les mêmes dans tous les schémas alors laisse le code suivant

Le soucis est que les noms des schémas changent d'un environnement à l'autre (développement/recette)...sinon ce serait trop facile

Si je ne trouve pas de solution, je pense éviter de faire un 'SELECT *', mais de ne sélectionner que quelques données, et de déclarer quelques variables afin de les affecter via le FETCH...

[lola06]

Effectivement je me suis mal exprimée.
Tu as bien déclaré ton curseur mais il n'est pas affecté.
Donc il ne peut pas savoir exactement comment il sera constitué.

La meilleure solution est effectivement de faire une selection des champs 1 à 1, sachant qu'une requête est bien mieux sans * !!

[mnitu]

Envoyé par mnitu

Utilisez Sql Dynamique ou supprimer schema2 et utilisez authid avec invoker rights dans la procédure.

[pressdell]

mnitu,

Je pensais que mon code était déjà quelque peu dynamique du fait du nom de schéma paramétré.

Je viens de tomber sur ces explications : Lien
Pensais-tu à cela lorsque tu évoquais le SQL dynamique?

Concernant :

utilisez authid avec invoker rights dans la procédure

J'avoue ne jamais en avoir entendu parler...

[mnitu]

Oui votre code est dynamique mais j'ai parlé de
Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)

[pressdell]

Merci pour ce lien mnitu.

Malheureusement je n'ai pas la main sur les "users" autres que dans mon environnement de développement.
i.e. Les DBAs avec lesquels je travaille me décapiteront si j'aborde cette solution avec eux pour les environnements suivants...

J'ai finalement fait au plus simple, c'est à dire en ne sélectionnant dans ma "requête implicite" que les données dont j'ai besoin (fin du SELECT *).

Encore merci à vous deux.

Je passe le sujet à Résolu.

[mnitu]

Gare à vous, vous risquez bien de vous faire étêter avec la solution que vous avez adoptée, trop permissive à l’injection SQL.
Petit remarque : cursor%rowtype n’est pas autre chose qu’un type enregistrement (record) que vous pouvez définir par vous même dans votre procédure.

[Rams7s]

Envoyé par mnitu

Gare à vous, vous risquez bien de vous faire étêter avec la solution que vous avez adoptée, trop permissive à l’injection SQL.

Comme pour la majorite des choses dont on a souvent besoin, il y a des fonctions prefabrique pour ca:
http://docs.oracle.com/cd/B28359_01/...t.htm#BABJIAEG

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
TMP@MINILHC >exec dbms_output.put_line(dbms_assert.schema_name('tmp'))
BEGIN dbms_output.put_line(dbms_assert.schema_name('tmp')); END;
 
*
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 243
ORA-06512: at line 1
 
 
Elapsed: 00:00:00.13
TMP@MINILHC >exec dbms_output.put_line(dbms_assert.schema_name('TMP'))
TMP

Sinon pour votre curseur, vous auriez pus essayer avec une clause return.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
TMP@MINILHC >CREATE OR REPLACE procedure tmp_proc IS
  2     type l_type IS record(champ1 tmp%rowtype);
  3     type t_cur IS ref cursor RETURN tmp%rowtype;
  4     my_cur t_cur;
  5     my_var my_cur%rowtype;
  6  begin
  7     NULL;
  8  end;
  9  /
 
Procedure created.
 
Elapsed: 00:00:00.05
TMP@MINILHC >SHOW errors
No errors.

(TMP etant une table dans le second exemple)

[mnitu]

Envoyé par Rams7s

Comme pour la majorite des choses dont on a souvent besoin, il y a des fonctions prefabrique pour ca

Personnellement je n’ai pas vu du dbms_assert.schema_name dans le code proposé, mais peut être que vous pouvez me le montrer.

Envoyé par Rams7s

Sinon pour votre curseur, vous auriez pus essayer avec une clause return.
...

Très bien et maintenait enrichissez votre procédure avec l’ouverture du my_cursor en mode dynamique et dite-nous si vous arrivez à la compiler.

[pressdell]

Bonjour à vous deux, et merci pour vos conseils encore une fois.

Je tiens à préciser que j'ai également tenté hier de définir un curseur à partir des solutions que vous exposez, mais en vain.

Je m'y suis certainement mal pris et j'ai joué la facilité...j'aime les choses simples, et je suis "fainéant".

Merci à vous tout de même pour vos explications et votre attention.

[Rams7s]

Peut-etre que DBMS_ASSERT n'y est pas, parce qu'il est inconnu pour le moment?
Mais que en l'ajoutant, ca peut resoudre le probleme de l'injection SQL que vous faites bien de mentionner.

J'ai bien fait de noter, essayer, vu que ca ne compile pas. C'est ben les forums, on en apprends a chaque fois.

[mnitu]

Envoyé par Rams7s

...
...
2 type l_type is record(champ1 tmp%rowtype);
....[/code]