A l’opposé des fonctions pipelined, aka fonctions de table, qui retournent l’équivalent du résultat d’une requête à partir d’une fonction et utilisables dans une requête avec la fonction table(), les « Ref Cursor » permettent de manipuler les lignes résultant d’ordres SELECT comme un paramètre que l’on se passe de SQL en PL/SQL à l’aide de la fonction cursor().

Si vous manipulez des données en masse, vous avez probablement joué avec ces 2 outils qui sont d’une élégance rare. Vous savez également que les curseurs ne peuvent pas être manipulés dans des type enregistrements ou collections en PL/SQL. Si vous ne le saviez pas, le code ci-dessous illustre ce point :

create or replace package x is
   type oops is record (r sys_refcursor);
end ;
/

show err

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4      PL/SQL: Declaration ignored
2/25     PLS-00989: Cursor Variable in record, object, or collection is
         not supported by this release

L’exemple qui suit contourne ce point dans votre code PL/SQL à l’aide d’un EXECUTE IMMEDIATE ... INTO.

SELECT et CURSOR()

Pour commencer, voici la requête que nous allons manipuler en PL/SQL ; cette requête contient un curseur qui affiche l’ensemble des colonnes d’une table retournée dans la clause principale du SELECT. Ce type de construction permet d’afficher simplement en une seule requête des informations de sources multiples sans faire de jointures. Vous allez comprendre rapidement :

col owner format a5
col table_name format a10
col tablespace_name format a15
col num_rows format 9999999
col columns format a25
col column_name format a25
col data_type format a15
set lines 120
set pages 1000
select owner, table_name, tablespace_name, num_rows,
       cursor (select column_name, data_type
                 from dba_tab_columns
                where owner=t.owner
                  and table_name=t.table_name) columns
   from dba_tables t
 where (table_name='OBJ$' or table_name='COL$')
   and owner='SYS';

OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS COLUMNS
----- ---------- --------------- -------- -------------------------
SYS   OBJ$       SYSTEM             13077 CURSOR STATEMENT : 5

CURSOR STATEMENT : 5

COLUMN_NAME               DATA_TYPE
------------------------- ---------------
OBJ#                      NUMBER
DATAOBJ#                  NUMBER
OWNER#                    NUMBER
NAME                      VARCHAR2
NAMESPACE                 NUMBER
SUBNAME                   VARCHAR2
TYPE#                     NUMBER
CTIME                     DATE
MTIME                     DATE
STIME                     DATE
STATUS                    NUMBER
REMOTEOWNER               VARCHAR2
LINKNAME                  VARCHAR2
FLAGS                     NUMBER
OID$                      RAW
SPARE1                    NUMBER
SPARE2                    NUMBER
SPARE3                    NUMBER
SPARE4                    VARCHAR2
SPARE5                    VARCHAR2
SPARE6                    DATE

21 rows selected.

SYS   COL$       SYSTEM             58512 CURSOR STATEMENT : 5

CURSOR STATEMENT : 5

COLUMN_NAME               DATA_TYPE
------------------------- ---------------
OBJ#                      NUMBER
COL#                      NUMBER
SEGCOL#                   NUMBER
SEGCOLLENGTH              NUMBER
OFFSET                    NUMBER
NAME                      VARCHAR2
TYPE#                     NUMBER
LENGTH                    NUMBER
FIXEDSTORAGE              NUMBER
PRECISION#                NUMBER
SCALE                     NUMBER
NULL$                     NUMBER
DEFLENGTH                 NUMBER
DEFAULT$                  LONG
INTCOL#                   NUMBER
PROPERTY                  NUMBER
CHARSETID                 NUMBER
CHARSETFORM               NUMBER
SPARE1                    NUMBER
SPARE2                    NUMBER
SPARE3                    NUMBER
SPARE4                    VARCHAR2
SPARE5                    VARCHAR2
SPARE6                    DATE

24 rows selected.

Manipulation direct de la requête PL/SQL

Lorsque vous utilisez la syntaxe FOR I IN SELECT ..., le moteur PL/SQL crée implicitement un type pour décrire I. Evidemment comme présenté en introduction, cette méthode ne peut pas fonctionner puisque vous ne pouvez pas avoir de REF CURSOR dans un type enregistrement en PL/SQL :

set serveroutput on
begin
  for i in (select owner, table_name, tablespace_name, num_rows,
                  cursor (select column_name, data_type
                            from dba_tab_columns
                           where owner=t.owner
                             and table_name=t.table_name) columns
             from dba_tables t
            where (table_name='OBJ$' or table_name='COL$')
              and owner='SYS')
   loop
      dbms_output.print_line(i.table_name);
   end loop;
end;
/
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00989: Cursor Variable in record, object, or collection is not supported by this release
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Contourner le problème avec execute immediate

Pour contourner le problème, il suffit de passer la requête directement au parser SQL et de récupérer le REF CURSOR à l’aide de la clause INTO ; vous pourrez ensuite ouvrir le REF CURSOR et manipuler son contenu à l’aide d’un OPEN :

set serveroutput on
declare
  v_sql             varchar2(4000);
  v_owner           varchar2(30);
  v_table_name      varchar2(30);
  v_tablespace_name varchar2(30);
  v_num_rows        varchar2(30);
  v_columns         sys_refcursor;
  x                 sys_refcursor;
  v_column_name     varchar2(30);
  v_data_type       varchar2(30);
begin
  v_sql:='select owner, table_name, tablespace_name, num_rows,
                  cursor (select column_name, data_type
                            from dba_tab_columns
                           where owner=t.owner
                             and table_name=t.table_name) columns
             from dba_tables t
            where (table_name=''OBJ$'' or table_name=''COL$'')
              and owner=''SYS''';
  open x for v_sql;
  loop
    fetch x into v_owner,v_table_name,v_tablespace_name,v_num_rows,v_columns;
       exit when x%notfound;
    dbms_output.put_line(v_owner||'.'||v_table_name);
    loop
       fetch v_columns into v_column_name, v_data_type;
          exit when v_columns%notfound;
       dbms_output.put_line('>       '||rpad(v_column_name,30)||

' '||v_data_type);
    end loop;
  end loop;
end;
/
SYS.OBJ$
>       OBJ#                           NUMBER
>       DATAOBJ#                       NUMBER
>       OWNER#                         NUMBER
>       NAME                           VARCHAR2
>       NAMESPACE                      NUMBER
>       SUBNAME                        VARCHAR2
>       TYPE#                          NUMBER
>       CTIME                          DATE
>       MTIME                          DATE
>       STIME                          DATE
>       STATUS                         NUMBER
>       REMOTEOWNER
                VARCHAR2
>       LINKNAME                       VARCHAR2
>       FLAGS                          NUMBER
>       OID$                           RAW
>       SPARE1                         NUMBER
>       SPARE2                         NUMBER
>       SPARE3                         NUMBER
>       SPARE4                         VARCHAR2
>       SPARE5                         VARCHAR2
>       SPARE6                         DATE
SYS.COL$
>       OBJ#                           NUMBER
>       COL#                           NUMBER
>       SEGCOL#                        NUMBER
>       SEGCOLLENGTH                   NUMBER
>       OFFSET                         NUMBER
>       NAME                           VARCHAR2
>       TYPE#                          NUMBER
>       LENGTH                         NUMBER
>       FIXEDSTORAGE                   NUMBER
>       PRECISION#                     NUMBER
>       SCALE                          NUMBER
>       NULL$                          NUMBER
>       DEFLENGTH                      NUMBER
>       DEFAULT$                       LONG
>       INTCOL#                        NUMBER
>       PROPERTY                       NUMBER
>       CHARSETID                      NUMBER
>       CHARSETFORM                    NUMBER
>       SPARE1                         NUMBER
>       SPARE2                         NUMBER
>       SPARE3                         NUMBER
>       SPARE4                         VARCHAR2
>       SPARE5                         VARCHAR2
>       SPARE6                         DATE

En Java aussi…

Pour compléter cet exemple, lisez le thread qui suit sur AskTom ! En particulier, vous trouverez l’équivalent de cet exemple PL/SQL écrit en Java à l’aide de OracleTypes.CURSOR.