Passer un CURSOR en paramètre à une procédure [Résolu]

[JamesP]

Bonjour,

Le titre est explicite, je souhaite passer un CURSOR en paramètre d'une procédure.

Je suis sous Oracle 10g.

J'ai écrit ce qui suit, mais j'ai un soucis de TYPE.
Quand je met "CURSOR" à la place de "SYS_REFCURSOR", ça ne marche pas non plus.

Le code en question :

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
SET serveroutput ON
 
DECLARE
 
  CURSOR INTERNAL_PATH_CURSOR IS
    SELECT 1 AS id FROM dual
    union
    SELECT 10 FROM dual
  ; 
 
  myId INTEGER;
 
  PROCEDURE printRow( cur_lig IN SYS_REFCURSOR) IS
  BEGIN
     LOOP
       FETCH cur_lig INTO myId ;
       EXIT WHEN cur_lig%NOTFOUND ;
       dbms_output.put_line( 'Found : ' || myId ) ;
     END LOOP ;
  End;
 
BEGIN
 
  dbms_output.put_line( 'Trying procedure with cursor ...' ) ;
  printRow(INTERNAL_PATH_CURSOR);
 
END;
/

Merci à vous pour votre aide.

[mnitu]

Un curseur <> une variable curseur.

[JamesP]

Ok.

Merci pour l'info, mais je suis pas un expert PL/SQL, et malheureusement ça ne me fait pas avancer ...

Je cherche et teste, mais je sèche ...

[skuatamad]

Essaie comme ça :

Code :

printRow(cursor(SELECT 1 AS id FROM dual));

[JamesP]

Envoyé par skuatamad

Essaie comme ça :

Code :

printRow(cursor(SELECT 1 AS id FROM dual));

Le résultat :

Rapport d'erreur :
ORA-06550: Ligne 23, colonne 12 :
PLS-00405: sous-interrogation non autorisée dans ce contexte
ORA-06550: Ligne 23, colonne 3 :
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

[skuatamad]

Regarde sys_refcursor et essaie :

Code :

1
2
3
4
5
v_rc sys_refcursor;
...
 
open v_rc FOR INTERNAL_PATH_CURSOR;
printRow(v_rc);

[JamesP]

Envoyé par skuatamad

Regarde sys_refcursor et essaie :

Code :

Erreur à nouveau :

Rapport d'erreur :
ORA-06550: Ligne 24, colonne 17 :
PLS-00320: déclaration de type de cette expression est incomplète ou mal structurée
ORA-06550: Ligne 24, colonne 3 :
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

Je pense que c'est le "open v_rc FOR INTERNAL_PATH_CURSOR" qui déconne ... arf ...

Mon but est de factoriser un traitement très simple qui boucle sur des curseurs dont chacun n'a qu'une colonne.
Pour chaque entrée du curseur, on affiche un message et incrémente un compteur ...

Rien de plus ... Peut être y a t'il un autre moyen de faire ...

[skuatamad]

Effectivement il faut mettre la requête directement :

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
SQL> DECLARE
  2  
  3    CURSOR INTERNAL_PATH_CURSOR IS
  4      SELECT 1 AS id FROM dual
  5      union
  6      SELECT 10 FROM dual;
  7    v_rc sys_refcursor;
  8  
  9    myId INTEGER;
 10  
 11    PROCEDURE printRow( cur_lig IN SYS_REFCURSOR) IS
 12    BEGIN
 13       LOOP
 14         FETCH cur_lig INTO myId ;
 15         EXIT WHEN cur_lig%NOTFOUND ;
 16         dbms_output.put_line( 'Found : ' || myId ) ;
 17       END LOOP ;
 18    End;
 19  
 20  BEGIN
 21  
 22    dbms_output.put_line( 'Trying procedure with cursor ...' ) ;
 23    open v_rc FOR SELECT 1 AS id FROM dual
 24                   union
 25                  SELECT 10 FROM dual;
 26    printRow(v_rc);
 27  
 28  END;
 29  /
Trying procedure WITH cursor ...
Found : 1
Found : 10
 
PL/SQL procedure successfully completed.
 
SQL>

Par rapport au besoin regarde peut être la procedure print_table de tom kyte et inspire toi de son utilisation du package DBMS_SQL.

Sinon pourquoi ne pas compter les lignes avec un count(*) plutôt que de fetch toutes les lignes pour incrémenter une variable ?

[JamesP]

Merci de la réponse ...

Alors pourquoi ne pas compter ? Simplement car en fait mes requêtes retournent les identifiants des données des données erronées dans mes tables ... Les incohérences quoi ... Donc cette liste est importante.

Sinon mettre directement la requête ne m'arrange pas vraiment, le but étant d'avoir mes curseurs à un endroit, et ensuite une façon de les parcourir et d'afficher le contenu ...

Arf ...

Je vais jeter un oeil, merci pour le lien ...

Tout autre aide est bienvenu ^^

[skuatamad]

Alors pourquoi ne pas compter ? Simplement car en fait mes requêtes retournent les identifiants des données des données erronées dans mes tables ... Les incohérences quoi ... Donc cette liste est importante.

Justement un count(*) sera plus performant si le besoin n'est qu'incrémenter une variable.

Mais s'il faut aussi afficher les ID en erreur alors une adaptation très basic de print_table pourrait donner :

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
SQL> DECLARE
  2    TYPE my_type IS TABLE of varchar2(32000) INDEX BY pls_integer;
  3    l_query my_type;
  4    myId INTEGER;
  5  procedure printRow( p_query IN varchar2 )
  6  IS
  7      l_theCursor     integer DEFAULT dbms_sql.open_cursor;
  8      l_columnValue   varchar2(4000);
  9      l_status        integer;
 10      l_descTbl       dbms_sql.desc_tab;
 11      l_colCnt        number;
 12  begin
 13      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 14      dbms_sql.describe_columns
 15      ( l_theCursor, l_colCnt, l_descTbl );
 16  
 17      FOR i IN 1 .. l_colCnt loop
 18          dbms_sql.define_column
 19          (l_theCursor, i, l_columnValue, 4000);
 20      end loop;
 21  
 22      l_status := dbms_sql.execute(l_theCursor);
 23  
 24      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 25          FOR i IN 1 .. l_colCnt loop
 26              dbms_sql.column_value
 27              ( l_theCursor, i, l_columnValue );
 28              dbms_output.put_line
 29              ( rpad( l_descTbl(i).col_name, 30 )
 30                || ': ' ||
 31                l_columnValue );
 32          end loop;
 33          dbms_output.put_line( '-----------------' );
 34      end loop;
 35  end;
 36  BEGIN
 37    l_query(1) := 'select 1 as id from dual union select 2 from dual';
 38    l_query(2) := 'select 3 as id from dual union select 4 from dual';
 39    FOR i IN l_query.first..l_query.last loop
 40      dbms_output.put_line( 'Trying procedure with varchar2 ...' ) ;
 41      printRow(l_query(i));
 42    end loop;
 43  END;
 44  /
Trying procedure WITH varchar2 ...
ID                            : 1
-----------------
ID                            : 2
-----------------
Trying procedure WITH varchar2 ...
ID                            : 3
-----------------
ID                            : 4
-----------------
 
PL/SQL procedure successfully completed.
 
SQL>

Certes on passe des chaines de caratères et pas des curseurs...
Mais ça reste simple de stocker les l_query ailleurs.

PS : En fait je me suis contenté de supprimer les conversions de dates, je te conseille donc d'utiliser print_table directement si l'exemple te convient.

[JamesP]

Merci pour l'astuce, ça marche super bien !!!

Mais (je chipotte peut être mais bon) j'aimerais vraiment utiliser mes cursors tels-quels ...

Je continue à chercher, bien que je garde cette solution sous le coude si jamais je n'y arrive pas avec mes curseurs ...

[Rams7s]

Tu peux tout a fait

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
 
CREATE TABLE tmp AS SELECT level AS n1, level*11 AS n2 FROM dual connect BY level<10;
 
CREATE OR REPLACE PACKAGE tmp_pkg AS
 
   TYPE gencurtyp IS REF CURSOR;
 
   PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT);
   procedure get_param(p_cursor IN gencurtyp, p_var out number);
END tmp_pkg;
/
 
SHOW errors
 
CREATE OR REPLACE PACKAGE BODY tmp_pkg AS
   PROCEDURE open_cv (generic_cv IN OUT gencurtyp, choice INT) IS
   BEGIN
 
      IF choice = 1 THEN
         OPEN generic_cv FOR SELECT n1 FROM tmp;
      ELSIF choice = 2 THEN
         OPEN generic_cv FOR SELECT n2 FROM tmp;
      END IF;
  END;
 
 
 
   procedure get_param(p_cursor IN gencurtyp, p_var out number) IS
   begin
      fetch p_cursor INTO p_var;
      close p_cursor;
   end;
end tmp_pkg;
/
 
declare 
a number;
cur_toto tmp_pkg.gencurtyp;
begin
tmp_pkg.open_cv(cur_toto,1);
tmp_pkg.get_param(cur_toto,a);
dbms_output.put_line(a);
 
tmp_pkg.open_cv(cur_toto,2);
tmp_pkg.get_param(cur_toto,a);
dbms_output.put_line(a);
end;
/

[JamesP]

Alors la je capte moins, je suis pas un pro, loin de la même en PL/SQL ...

On se rapproche à nouveau de mon premier post, j'ai supprimé et adapté ce que Rams7s à écrit.

Pour info, je ne peux pas déclarer de packages ou de fonctions/procedures STOCKEES, je dois rester dans ma transaction PL/SQL ...

Voila ou je suis :

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
SET SERVEROUTPUT ON
 
DECLARE
 
  TYPE gencurtyp IS REF CURSOR;
 
    -- Retrieve list of internel paths
  gencurtyp INTERNAL_PATH_CURSOR IS
    SELECT 1 AS id FROM dual union SELECT 2 FROM dual;
 
  myid INTEGER;
 
  procedure printRow(p_cursor IN gencurtyp) IS
    OPEN p_cursor;
    LOOP
      FETCH p_cursor INTO myid;
      dbms_output.put('myid = ' || myid);
    END LOOP;
    CLOSE p_cursor;
  end;
 
BEGIN
  printRow(INTERNAL_PATH_CURSOR);
END;
/

Mon problème est que je voudrais déclarer mon CURSOR (ou ici "gencurtype") dans une variable préalable.
Je ne voudrait pas faire :

Code :

OPEN generic_cv FOR SELECT n1 FROM tmp;

Mais plus un

Code :

OPEN generic_cv FOR INTERNAL_PATH_CURSOR;

Ca doit être incohérent, ça ne marche pas ...

[Rams7s]

Faut pas tout essayer dans tous les sens. C'est tres clairement contre-productif.

Premiere chose:
http://download.oracle.com/docs/cd/E...s.htm#CIHDFDJG
Lire la doc sur le PL/SQL ca ne peut que vous faire gagner du temps a moyen terme, limite court terme la.

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
DECLARE
 
  TYPE gencurtyp IS REF CURSOR;
 
    -- Retrieve list of internel paths
  INTERNAL_PATH_CURSOR gencurtyp;
 
  myid INTEGER;
 
  cur_text varchar2(200):='SELECT 3 FROM DUAL UNION SELECT 4 FROM DUAL';
  procedure printRow(p_cursor IN gencurtyp) IS
  begin
    LOOP
      FETCH p_cursor INTO myid;
      exit when p_cursor%notfound;
      dbms_output.put_line('myid = ' || myid);
    END LOOP;
    CLOSE p_cursor;
  end;
 
BEGIN
   open internal_path_cursor FOR     SELECT 1 AS id FROM dual union SELECT 2 FROM dual;
  printRow(INTERNAL_PATH_CURSOR);
  open internal_path_cursor FOR cur_text;
  printRow(INTERNAL_PATH_CURSOR);
END;
/

Votre boucle, elle est infinie.

Et enfin, DBMS_OUTPUT.PUT, il n'imprime rien a l'ecran. Il faut un PUT_LINE. Ca, faut l'avoir vu pour le savoir.

[boussafi]

je propose deux solutions:
la premiere :

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
declare
  TYPE cursor_type IS REF CURSOR;
  cur cursor_type;
 
  myId INTEGER;
 
 
 la_requete varchar2(100):=' SELECT 1 AS id FROM dual
                    union
                   SELECT 10 FROM dual';
procedure printRow  (
                    cur_lig IN cursor_type) IS 
begin
 LOOP
          FETCH cur_lig INTO myId ;
          EXIT WHEN cur_lig%NOTFOUND ;
         dbms_output.put_line( 'Found : ' || myId ) ;
        END LOOP ;
 
end;
 
BEGIN
 
dbms_output.put_line( 'Trying procedure with cursor ...' ) ;
open cur FOR  la_requete;
 
     printRow(cur);
 
END;
/

la deuxieme:

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
declare
 
  TYPE cursor_type IS REF CURSOR;
  cur cursor_type;
 
  myId INTEGER;
 
 
 la_requete varchar2(100):=' SELECT 1 AS id FROM dual
                    union
                   SELECT 10 FROM dual';
procedure printRow  (
                    req_cur_lig IN varchar) IS
begin
open cur FOR  req_cur_lig;
 LOOP
          FETCH cur INTO myId ;
          EXIT WHEN cur%NOTFOUND ;
         dbms_output.put_line( 'Found : ' || myId ) ;
        END LOOP ;
 
end;
 
BEGIN
 
dbms_output.put_line( 'Trying procedure with cursor ...' ) ;
 
 
     printRow(la_requete);
 
END ;

Feedback stp

[mnitu]

Envoyé par JamesP

...
Ca doit être incohérent, ça ne marche pas ...

Un curseur <> une variable curseur!

Un curseur est un nom pour une zone privée SQL spécifique où est stockée l'information concernant le traitement de la requête SQL.

Une variable curseur est un pointer a une de ces zones privées.

Les deux ne sont pas interchangeables!

[JamesP]

Merci beaucoup de vos réponses ...

Je comprends mieux, et je vais rester sur une solution qui fonctionne bien, à savoir stocker mes requêtes en type STRING pour ensuite avoir un procédure qui exécute et parcours.
=> Cf premiers posts + message de "boussafi"

Je clos le sujet, un gros merci à vous tous !!!!

Sinon au passage, pour la boucle infinie et le "put_line", je savais c'est un résidu de copier/coller, et j'ai pas fait attention comme ça ne fonctionnait pas ...

Mais merci quand même !