Discussion :

[go_to_debut]

Bonjour,

Après avoir utilisé les curseur paramétrés et les REF CURSOR qui permettent de coder la requête sous forme de chaîne de caractères dans la clause OPEN, j'ai tenté de passer une requête en paramètre sous forme de chaîne de caractère.

Il m'a semblé que c'est ce à quoi servent (entre autre) les REF CURSOR (mais me je trompe peut-être)

Je ne suis pas arrivée à marier les 2 syntaxes.

Voilà la requête que j'injecte :

'Select T_CLIENT.CLI_ID , T_TITRE.TIT_LIBELLE , T_TITRE.TIT_CODE from T_TITRE , T_CLIENT where T_TITRE.TIT_CODE = T_CLIENT.TIT_CODE and T_CLIENT.CLI_ID <= 20'

j'ai essayé ca (et toutes sortes de formules que je vous épargne).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
ItemTitre  sys_refcursor (requete) ;
ou 
ItemTitre  sys_refcursor  (requete in char(160)) ;
ou)
ItemTitre  sys_refcursor  (requete in char) ;
 
...
 
 OPEN ItemTitre for ('&requete')  ;
ou
 OPEN ItemTitre for (&requete)  ;

et je rencontre ce genre d'erreur

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
 
 
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 22 :
PLS-00103: Symbole "SELECT" rencontré à la place d'un des symboles suivants :
 
ou 
 
ORA-06550: Ligne 4, colonne 39 :
PLS-00103: Symbole "CHAR" rencontré à la place d'un des symboles suivants :
(
 
ou 
 
Rapport d'erreur :
ORA-06550: Ligne 4, colonne 12 :
PLS-00566: le nom type "SYS_REFCURSOR" ne peut être calligraphique

Est-ce que je fais fausse route en voulant injecter une requête en paramètre ?
N'est-ce pas pourtant le genre de problème que l'on peut être amené résoudre ?

Merci de vos avis éclairés.

[go_to_debut]

j'ai aussi essayé un mix avec le FOR ... IN

Code :

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

1
2
3
4
5
6
7
8
9
 
cursor itemtitre ;
 
BEGIN
for ItemTitre in (&requete)
 
Rapport d'erreur :
ORA-06550: Ligne 3, colonne 1 :
PLS-00360: déclaration de curseur sans corps requiert type retour

et d'autres format mais sans succès

[dariyoosh]

Bonjour,

Est-ce que je fais fausse route en voulant injecter une requête en paramètre ?

Je dirais que c'est une mauvaise idée car le code pourrait être vulénrable à l'injection SQL (après ça dépend de ce que vous faites et des contrôles faits sur le contenu des requêtes)

A titre informatif:

Avoiding SQL Injection in PL/SQL

D'habitude vous définissez vos requêtes dans des modules (procédure par exemple) sous forme de chaîne de caractère constante et s'il y a des paramètre vous utilisez des bind variables dans votre requête SQL.

Voici un exemple basé sur le schéma hr (la table employees): On crée une fonction qui prend en paramètre le n° de département et renvoie un REF CURSOR avec des informations liés aux employés de ce département.

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
SET SQLBLANKLINES ON;
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
SET SERVEROUTPUT ON;
 
 
CREATE OR REPLACE PROCEDURE mytestProc
AUTHID DEFINER
IS   
BEGIN
    <<bk1>>
    DECLARE
        TYPE empRec_ty IS RECORD
        (
            depId       hr.employees.department_id%TYPE ,
            empId       hr.employees.employee_id%TYPE   ,
            fname       hr.employees.first_name%TYPE    ,
            jobId       hr.employees.job_id%TYPE        ,
            salary      hr.employees.salary%TYPE
        );
        TYPE empCur_ty IS REF CURSOR;
        empRec  empRec_ty;
        empCur  empCur_ty;
 
 
        FUNCTION getEmpDepInfo(p_depId IN  PLS_INTEGER)
        RETURN empCur_ty
        IS
        BEGIN
            <<bk2>>
            DECLARE
                empCur  empCur_ty;
                STMT    CONSTANT VARCHAR2(500) :=
                    'SELECT t1.department_id    ,
                            t1.employee_id      , 
                            t1.first_name       ,
                            t1.job_id           ,
                            t1.salary 
                     FROM   hr.employees t1
                     WHERE  t1.department_id = :b_depId';
            BEGIN   
                OPEN bk2.empCur FOR bk2.STMT USING IN p_depId;
                RETURN bk2.empCur;
            END;
        END getEmpDepInfo;
    BEGIN
        bk1.empCur := bk1.getEmpDepInfo(p_depId=>90);
        IF bk1.empCur%ISOPEN THEN        
            LOOP
                FETCH bk1.empCur INTO bk1.empRec;
                EXIT WHEN bk1.empCur%NOTFOUND;
                sys.dbms_output.put_line
                (
                    'depId = '  || bk1.empRec.depId ||  CHR(9)  ||
                    'empId = '  || bk1.empRec.empId ||  CHR(9)  ||
                    'fname = '  || bk1.empRec.fname ||  CHR(9)  ||
                    'jobId = '  || bk1.empRec.jobId ||  CHR(9)  ||
                    'salary = ' || bk1.empRec.salary
                );
            END LOOP;
            CLOSE bk1.empCur;
        END IF;
    END;
END mytestProc;
/
SHOW ERRORS;
 
 
BEGIN
    mytestProc();
END;
/

Et le résultat:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Procedure created.
 
No errors.
depId = 90	empId = 100	fname = Steven	jobId = AD_PRES	salary = 24000
depId = 90	empId = 101	fname = Neena	jobId = AD_VP	salary = 17000
depId = 90	empId = 102	fname = Lex	jobId = AD_VP	salary = 17000
 
PL/SQL procedure successfully completed.
 
 
Procedure dropped.
 
SQL>

N'est-ce pas pourtant le genre de problème que l'on peut être amené résoudre ?

De quel genre de problème est-ce que vous parlez exactement?

[go_to_debut]

Merci pour ta réponse.

Pour l'injection de code j'y ai pensé au moment où je le faisait.

En fait je n'avais pas d'idée préconçue quand j'ai voulu faire cet exercice, c'était simplement pour "fouiller" le langage. Comme j'espère pouvoir mettre cette nouvelle compétence sur mon CV j'essaye d'imaginer tout ce que je pourrais trouver comme code dans la réalité d'un boulot (peur de devoir intervenir dans du code que je ne comprendrais pas).

Par contre tu me plonge dans l'obscurité avec ton exemple, je suis un peu dépourvue car je n'ai pas encore vu cet exemple d'une fonction imbriquée dans une procédure ...

Maintenant je comprends pourquoi ça permet d'invoquer le paramètre p_depId avant qu'il ne soir implémenté.

Il m'a fallut faire des paquets pour m'y retrouver

... alors ne parlons pas de ces label qui viennent préfixer des objets ... je ne connaissais pas ... pas vu dans les tutoriels dont je me suis servi.
Est-ce qu'on peut faire la même chose avec les label qui servent avec les go to ?

je me rends compte que j'ai encore de quoi faire avant d'être à l'aise

Par contre j'apprends la syntaxe Open FOR USING (pareil pas vu dans les tutoriels). Tiens je vais l'essayer avec mon injection ...

bonne journée

PS : j'ajoute que je n'ai nullement l'intention de faire de l'injection de code à des fins malicieuses, c'est parceque j'ai utilisé le prompt/accept que je me suis trouvée dans ce cas

[dariyoosh]

Comme j'espère pouvoir mettre cette nouvelle compétence sur mon CV j'essaye d'imaginer tout ce que je pourrais trouver comme code dans la réalité d'un boulot

Dans ce cas une aide importante que je recommande vraiment (parmi d'autre livres intéressants, bien sûr):

http://shop.oreilly.com/product/9780596514464.do

Par contre tu me plonge dans l'obscurité avec ton exemple, je suis un peu dépourvue car je n'ai pas encore vu cet exemple d'une fonction imbriquée dans une procédure ...

Définir un sous-programme à l'intéreieur d'un autre ce n'est pas du tout une spécificité de PL/SQL. J'aurais pu mettre le code dans un bloc anonyme DECLARE ... BEGIN ... END; mais je préfère d'encapsuler mes codes toujours à l'intérieur d'une procédure et autant que possible j'évite les blocs anonyme car, au début de mes scripts, je mets la ligne suivantes pour voir tous les messages Warning

Code :

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

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Le problème c'est que ceci ne s'applique pas aux blocs anonymes.

... alors ne parlons pas de ces label qui viennent préfixer des objets ... je ne connaissais pas ... pas vu dans les tutoriels dont je me suis servi.

Ce n'est pas du tout une obligation, pour moi c'est une bonne façon d'éviter des conflits de noms entre SQL et PL/SQL. Pour plus d'information et connaître l'intérêt: tu peux lire les pages 12 et 13 du livre (en ligne) Doing SQL from PL/SQL: Best and Worst Practices
Comme j'ai dit il y en a beaucoup qui se fichent complètement de ça et préfèrent de choisir et respecter une convention pour nommer leur variables (par exemple préfixer le nom de leur variables locales par l_ etc.) C'est une façon de voir les choses, mais moi, pour ma part j'ai choisi la méthode de qualification des noms et je ne regrette rien.

Et bien entendu la référence principale de PL/SQL est:

PL/SQL Language Reference

je me rends compte que j'ai encore de quoi faire avant d'être à l'aise

Peu importe quel est le niveau d'expérience et de connaissance, il y a toujours des choses à apprendre, personne ne connaît tout, donc ça ne sert à rien de se laisser décourager

Par contre j'apprends la syntaxe Open FOR USING (pareil pas vu dans les tutoriels). Tiens je vais l'essayer avec mon injection ...

Je pense que pour quelqu'un qui commence, passer par un livre, dont je viens de donner un exemple ci-dessus, est une meilleure approche (sur différents aspects: clarté, organisation, niveau, etc.)

Après ça dépend de ce que l'on cherche et de ce qu'on souhaite faire.

[go_to_debut]

merci pour toutes tes indications. Oui un bon bouquin aurait été la base mais j'ai du mal avec l'anglais

Je peux comprendre des phrases simples mais passé un seuil je n'y arrive plus, c'est un handicap. Et je n'en ai pas trouvé d'assez costaud en français.

N'empêche je vais mettre tes liens en favoris et m'y référer le plus possible.

Merci

[skuatamad]

Oui un bon bouquin aurait été la base mais j'ai du mal avec l'anglais

Oui c'est un vrai handicap :
1/ La doc oracle est en anglais exclusivement
2/ De nombreux très bon sites/blogs sont en anglais.
3/ Les livres les plus poussés techniquement sont en anglais uniquement.

Mais franchement l'anglais technique n'est pas très très complexe, c'est la partie technique qui l'est.

En français, vous avez developpez.net, qui est plutôt un bon support d'apprentissage, moi j'ai débuté par DVP (tuto + lecture du forum). Il est cependant nécessaire d'aller lire les liens régulièrement proposés vers des ressources en anglais.

Côté livre, regardez peut être celui de Christian Soutou :
http://www.amazon.fr/SQL-pour-Oracle...7012503&sr=8-1

[skuatamad]

j'essaye d'imaginer tout ce que je pourrais trouver comme code dans la réalité d'un boulot.

Voici une utilisation classique des REF CURSOR :

Code :

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

1
2
3
4
5
6
7
8
9
SQL> create or replace procedure p (p_cur out sys_refcursor) as
  2  begin
  3    open p_cur for select ename, sal from emp;
  4  end;
  5  /
 
Procedure created.
 
SQL>

Et pour tester la procédure dans sqlplus :

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
SQL> var rc refcursor
SQL> exec p(:rc);
 
PL/SQL procedure successfully completed.
 
SQL> print :rc;
 
ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
 
14 rows selected.
 
SQL>

L'application cliente pourra appeler la procédure (EXEC dans sqlplus) et récupérer les données (PRINT dans sqlplus).

Pour ce qui est du sql dynamique, une utilisation classique est lorsqu'il est nécessaire de construire la requête, comme pour :
Making a generic search sql Query
Un peu complexe lorsqu'on débute, n'hésitez donc pas à poser des questions si vous ne comprenez pas bien le code proposé.

D'une manière générale, ne faites pas de sql dynamique lorsque vous pouvez faire du sql statique.
Si vous êtes obligé de faire du sql dynamique, prenez garde aux injections sql en utilisant les variables de liaison (USING) et si nécessaire le package DBMS_ASSERT.

Par contre j'apprends la syntaxe Open FOR USING (pareil pas vu dans les tutoriels).

C'est dans le tutoriel sur le sql dynamique

[go_to_debut]

C'est dans le tutoriel sur le sql dynamique

Tout s'explique je n'ai pas encore vu cette partie

Bon je vais gratter encore un peu avant de la mettre sur mon CV