[817] procédure avec REF CURSOR en paramètre

Version imprimable

04/01/2006, 22h49
Bourbigot

[817] procédure avec REF CURSOR en paramètre

Bonjour,

J'utilise Oracle v8.1.7 et j'avoue être un peu novice en REF CURSOR.

Si je n'ai aucun mal à tester, sous SQL+, des procédures stockées ayant un REF CURSOR en sortie :

SET AUTOPRINT ON;
VARIABLE RC_OUT REFCURSOR;
EXECUTE MON_PACKAGE.MA_PROC1(:RC_OUT);

je n'arrive pas à trouver la syntaxe (toujours sous SQL+) pour exécuter des procédures ayant un REF CURSOR en entrée.
J'ai essayé un tas de trucs du genre (comme dérivés) :

EXECUTE MON_PACKAGE.MA_PROC2(CURSOR(SELECT ... FROM ...));

Mais je n'aboutis à rien... Je me fais jeter en permanence... Sans résultat.

Auriez-vous la gentillesse de m'aider ?

Merci.

Vous ne pouvez pas utiliser de procédure dans du code SQL.
Transformez votre procédure en fonction:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION Get_Rows2 ( cur_lig IN SYS_REFCURSOR )
return number
IS
   Remp  EMP%ROWTYPE ;
BEGIN
   LOOP
     FETCH cur_lig INTO Remp ;
     EXIT WHEN cur_lig%NOTFOUND ;
     dbms_output.put_line( Remp.ename ) ;
   END LOOP ;
   return 1 ;
End ;
/

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
SQL> set serveroutput on
SQL> select Get_Rows2 ( CURSOR(SELECT * FROM EMP) ) from dual
  2  /
 
GET_ROWS2(CURSOR(SELECT*FROMEMP))
---------------------------------
                                1
 
FZAPPA
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
SQL>

Avec une variante:

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
 
DROP TABLE dept
/
CREATE TABLE dept
(deptno  INTEGER)
/
INSERT INTO dept VALUES (1)
/
INSERT INTO dept VALUES (2)
/
INSERT INTO dept VALUES (3)
/
 
COMMIT
/
 
PROMPT /*======================================================================+
PROMPT |                                                                       |
PROMPT |                                                                       |
PROMPT +======================================================================*/
CREATE OR REPLACE PACKAGE mypackage AS
 
TYPE t_dept IS REF CURSOR RETURN dept%ROWTYPE;
 
PROCEDURE myproc 
 (o_cursor IN OUT t_dept);
 
END;
/
PROMPT /*======================================================================+
PROMPT |                                                                       |
PROMPT |                                                                       |
PROMPT +======================================================================*/
CREATE OR REPLACE PACKAGE BODY mypackage AS
 
PROCEDURE myproc 
 (o_cursor IN OUT t_dept)
 
IS
 
lc_dept           t_dept;
 
BEGIN
IF NOT lc_dept%ISOPEN THEN 
    OPEN lc_dept FOR SELECT * FROM dept;
END IF;
o_cursor := lc_dept;
END;
 
END mypackage;
/
 
 
SET AUTOPRINT ON
VARIABLE myvar REFCURSOR
BEGIN
mypackage.myproc(:myvar);
END;
/

Output:

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
 
Table dropped.
 
 
Table created.
 
 
1 row created.
 
 
1 row created.
 
 
1 row created.
 
 
Commit complete.
 
/*======================================================================+
|                                                                       |
|                                                                       |
+======================================================================*/
 
Package created.
 
/*======================================================================+
|                                                                       |
|                                                                       |
+======================================================================*/
 
Package body created.
 
 
PL/SQL procedure successfully completed.
 
 
    DEPTNO
----------
         1
         2
         3
 
SQL>

05/01/2006, 11h12
Bourbigot

L'exemple proposé par Sheik Yerbouti est intéressant mais ne fonctionne pas en 8.1.7 (l'identifiant SYS_REFCURSOR n'existe pas). Je suis obligé d'utiliser un package pour déclarer un type REF CURSOR et de définir ma procédure au sein de ce package (j'ai procédé exactement comme dans l'exemple de Plabrevo).
J'ai tout de même essayé de transformer ma procédure en fonction mais quand je l'exécute :
SELECT MON_PACKAGE.MA_FONC(CURSOR(SELECT ... FROM ...)) FROM DUAL;
J'ai pour résultat : ORA-22902 : l'expression CURSOR n'est pas autorisée
Auriez vous une autre idée ?
Merci par avance.
05/01/2006, 17h25
plabrevo

Quel est le requirement exact? Mon exemple fonctionne. Comment veux-tu effectuer l'appel de Sqlplus exactement?
05/01/2006, 17h39
Bourbigot

Je ne conteste pas ton exemple, il fonctionne très bien, j'utilise ce principe lors que je souhaite afficher le contenu d'un REF CURSOR en sortie.
Le cas qui me préoccupe est différent, car ma procédure a à la fois un REF CURSOR en entrée et un autre en sortie. A partir du jeu d'enregistrement passé en paramétre, j'effectue des mises à jour en base et je restitue le compte-rendu de ces mises à jour en sortie.
A terme cette proc sera appelée par une appli Java.

Le problème, c'est que je n'arrive pas à tester ma procédure car je n'arrive pas, sous SQL+, à trouver la synthaxe pour exécuter cette proc en lui passant un jeu d'enregistrement en paramètre.
As tu une idée ?
05/01/2006, 18h52
plabrevo

Autant pour moi, j'etais a cote de la question. Desole.

J'y reflechirai cet apres-midi si personne n'a pu trouver de solution entre temps.

Et en passant par du dynamique SQL:

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
 
DROP TABLE dept
/
CREATE TABLE dept
(deptno  INTEGER
,dummy   INTEGER)
/
INSERT INTO dept VALUES (1,1)
/
INSERT INTO dept VALUES (2,1)
/
INSERT INTO dept VALUES (3,1)
/
 
COMMIT
/
 
PROMPT /*======================================================================+
PROMPT |                                                                       |
PROMPT |                                                                       |
PROMPT +======================================================================*/
CREATE OR REPLACE PACKAGE mypackage AS
 
TYPE t_curs IS REF CURSOR;
 
PROCEDURE myproc 
 (p_cursor  IN VARCHAR2
 ,o_cursor OUT t_curs);
 
END;
/
PROMPT /*======================================================================+
PROMPT |                                                                       |
PROMPT |                                                                       |
PROMPT +======================================================================*/
CREATE OR REPLACE PACKAGE BODY mypackage AS
 
PROCEDURE myproc 
 (p_cursor  IN VARCHAR2
 ,o_cursor OUT t_curs)
 
IS
 
l_string          VARCHAR2(4000);
lc_curs           t_curs;
 
BEGIN
l_string :='BEGIN OPEN :curs FOR '||p_cursor||'; END;';
 
EXECUTE IMMEDIATE l_string USING IN OUT lc_curs;
 
o_cursor := lc_curs;
END;
 
END mypackage;
/
 
SET AUTOPRINT ON
SET SERVEROUTPUT ON SIZE 1000000
VARIABLE myvar_out REFCURSOR
 
BEGIN
mypackage.myproc('SELECT * FROM dept',:myvar_out);
END;

19/01/2006, 10h42
Bourbigot

Ta solution est intéressante cependant j'ai pour contrainte que le paramètre en entrée soit obligatoirement de type ref cursor (en non varchar). en outre la proc ne passe pas à la compil en 8.1.7 car lc_curs ne peut visiblement pas être de type ref cursor "PLS-00457: les expressions doivent être de type SQL".
J'ai résolu le problème différemment : je fais appeler ma procédure par une autre procédure qui génère un ref cursor et qui le lui passe en paramètre en entrée.
En tout cas, merci de votre aide et de votre réactivité.