Discussion :

[lcaya]

Bonjour à tous,

Je cherche à créer une procédure qui me renvoit le contenu d'un select. Le contenu peut être nul, une ligne ou plusieurs lignes. Cette procédure reçoit un id et puis retourne le contenu du select en plus des variables de message d'échec ou de réussite. J'utilise Oracle 10g R2.

Voici 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
PROCEDURE comm_op_range(
                                        vi_op_id         IN NUMBER,
                                        vo_cur           OUT sys_refcursor,
                                        vo_ok            OUT NUMBER,
                                        vo_error_num  OUT NUMBER,
                                        vo_error_msg  OUT VARCHAR2)
IS
 
sel_comm_error  EXCEPTION;
 
BEGIN
OPEN vo_cur FOR
SELECT * FROM TABLE
WHERE TABLE.ID = vi_op_id

Donc le retour du select est renvoyé au programme appelant. Mais ici, si j'ai bien compris on ne peut pas retourner un curseur null. Alors l'utilisateur reçoit le message ORA-01001 Invalid cursor. J'aimerais pouvoir gérer tout les cas et ainsi si c'est nul retourner à la variable vo_error_msg "Invalid ID" par exemple.

Merci.

[laurentschneider]

ton exemple n'est pas clair...

1) soit tu retournes un curseur sur 0 ligne

Code :

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

1
2
3
4
5
6
create table lsc_t(x number);
create PROCEDURE lsc_p(vi_op_id IN NUMBER, vo_cur OUT sys_refcursor)
IS BEGIN
OPEN vo_cur FOR SELECT * FROM lsc_tWHERE x = vi_op_id;
END;
/

Code :

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

1
2
3
4
5
6
7
8
SQL> var c refcursor;
SQL> exec lsc_p(1,:c);
 
PL/SQL procedure successfully completed.
 
SQL> print
 
no rows selected

2) tu retournes null

Code :

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

1
2
3
4
5
create or replace PROCEDURE lsc_p(vi_op_id IN NUMBER, vo_cur OUT sys_refcursor)
IS BEGIN
  vo_cur := null;
END;
/

Code :

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

1
2
3
4
SQL> var c refcursor;
SQL> exec lsc_p(1,:c);
 
PL/SQL procedure successfully completed.

Alors où est le ORA-01001 Invalid cursor?

[lcaya]

Vous avez raison il n'y pas de problème particulier avec la procédure en elle-même mais plutôt avec les tests générés avec SQL Developper. Je tente toutes les valeurs possibles pour le paramètre d'entré vo_op_id. Mais lorsque j'utilise une valeur inexistante dans la table du curseur VO_CUR en utilisant le 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
36
37
38
39
40
DECLARE
  VI_OP_ID NUMBER;
  VO_CUR SYS_REFCURSOR;
  VO_OK NUMBER;
  VO_ERROR_NUM NUMBER;
  VO_ERROR_MSG VARCHAR2(200);
  VO_RANGE_BEG number;
  VO_RANGE_END NUMBER;
  VO_COMM      VARCHAR2(200);
  VO_OP_IST    VARCHAR2(3);
  VO_BLOCKED   VARCHAR2(1);
 
BEGIN
  VI_OP_ID := 130000;
 
  PKG_REF_SELECT.COMM_OP_RANGE(
    VI_OP_ID => VI_OP_ID,
    VO_CUR => VO_CUR,
    VO_OK => VO_OK,
    VO_ERROR_NUM => VO_ERROR_NUM,
    VO_ERROR_MSG => VO_ERROR_MSG
  );
 
  --OPEN VO_CUR; 
  LOOP
  FETCH VO_CUR INTO VO_RANGE_BEG, VO_RANGE_END, VO_BLOCKED, VO_COMM, VO_OP_IST;
  EXIT WHEN VO_CUR%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE('VO_beg = ' || VO_RANGE_BEG);
  DBMS_OUTPUT.PUT_LINE('VO_end = ' || VO_RANGE_END);
  DBMS_OUTPUT.PUT_LINE('VO_com = ' || VO_COMM);
  DBMS_OUTPUT.PUT_LINE('VO_op_ist = ' || VO_OP_IST);
  END LOOP;
  CLOSE VO_CUR;
 
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('VO_CUR = ' || VO_CUR);
  DBMS_OUTPUT.PUT_LINE('VO_OK = ' || VO_OK);
  DBMS_OUTPUT.PUT_LINE('VO_ERROR_NUM = ' || VO_ERROR_NUM);
  DBMS_OUTPUT.PUT_LINE('VO_ERROR_MSG = ' || VO_ERROR_MSG);
END;

A ce moment précis j'obtiens l'erreur Invalid cursor qui à mon avis doit être lié au bloc fetch puisqu'en enlevant le bloc et en testant avec la même valeur je n'ai plus l'erreur. Dans ce cas, l'utilisateur reçoit un message qui est juste, l'informant que le vo_op_id n'est pas valide. Alors question, est-ce que je peux conserver tout, en ajoutant un bloc conditionnel qui me permettrait de tester à la fois une valeur inexistante et une valeur existante ?

[laurentschneider]

exception

[lcaya]

Merci pour l'info.