Passage d'une requête en param et utilisation d'un REF CURSOR

Version imprimable

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:

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:

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.

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

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

Bonjour,

Citation:

Envoyé par go_to_debut

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:

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:

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>

Citation:

Envoyé par go_to_debut

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?

20/08/2013, 16h02
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 :oops:

... 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 :cry:

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 8-)
20/08/2013, 17h05
dariyoosh

Citation:

Envoyé par go_to_debut

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

Citation:

Envoyé par go_to_debut

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:

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

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

Citation:

Envoyé par go_to_debut

... 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

Citation:

Envoyé par go_to_debut

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

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

Citation:

Envoyé par go_to_debut

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.

Citation:

Envoyé par go_to_debut

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:

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:

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.

Citation:

Envoyé par go_to_debut

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

C'est dans le tutoriel sur le sql dynamique ;)

20/08/2013, 17h16
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 :zoubi:
20/08/2013, 17h31
go_to_debut

Citation:

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 :mrgreen:
20/08/2013, 17h52
skuatamad

Citation:

Envoyé par go_to_debut

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
20/08/2013, 18h49
go_to_debut

Oui l'anglais technique n'est pas très difficile lorsqu'il s'agit, par exemple, rechercher la signification d'un code erreur, ou lorsqu'on maîtrise déjà un minimum mais quand c'est pour un apprentissage complet et aborder les subtilités d'un langage c'est plus difficile.

Quand aux forums en anglais je suis allée faire un tour (pour une autre question), j'ai trouvé des posts qui parlaient de mon problème (avec solution) mais je n'ai pas pu comprendre tout ce que le gars disait.

Bref je ferai avec ce que j'ai

Merci pour la référence du livre ;)
21/08/2013, 16h51
go_to_debut

Et bien voilà, ça marche parfaitement avec l' OPEN FOR :)

[MODE_DISCRET] je vais peut-être faire une pause en PL/SQL car j'attaque lundi pour un mois en cobol ;) [/MODE DISCRET]

Merci encore
21/08/2013, 17h15
pacmann

Oh, change de boîte !
(Ou demande de faire à la place du rpg :))
21/08/2013, 19h00
dariyoosh

En tout cas, au moins côté des banques, il me semble qu'on continue à embaucher des développeur Cobol 8-)
21/08/2013, 21h39
go_to_debut

je me vois mal refuser, je n'ai pas les moyens de faire la fine bouche, je suis au chômage ;) et encore j'ai eu du bol, ils sont acculés, y'a plus que comme ca que j'arrive à travailler :aie:

Ha non pas le rpg, je me perdais dans son cycle et ses clignotants ... et ily a encore moins de boulot qu'en cobol.

Bonne soirée à vous.