CREATE OR REPLACE
TYPE zy_w AS OBJECT(
  CODE_INFORMATION	VARCHAR2(4),
  MATRICULE_RH	VARCHAR2(8),
  INFORMATION_DATA	VARCHAR2(4000),
  DATE_TRAITEMENT	TIMESTAMP(0),
  LINE_NUMBER	NUMBER
);
/

CREATE OR REPLACE
TYPE t_toto AS TABLE OF ZY_W;
/

DECLARE
  p_toto t_toto;
  cpt NUMBER := 0;
BEGIN
  SELECT 
    CODE_INFORMATION,
    MATRICULE_RH,
    INFORMATION_DATA,
    DATE_TRAITEMENT,
    LINE_NUMBER   
  INTO p_toto
  FROM RRGQTZY_W0
  WHERE MATRICULE_RH = '01192704';
  
  SELECT line_number
  INTO cpt 
  from table(p_toto);
  
  DBMS_OUTPUT.PUT_LINE(cpt);
END;
/


****************
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 3 :
PL/SQL: ORA-00947: nombre de valeurs insuffisant
ORA-06550: Ligne 5, colonne 3 :
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

SCOTT@orcl_11gR2> CREATE OR REPLACE
  2  TYPE zy_w AS OBJECT(
  3    CODE_INFORMATION      VARCHAR2(4),
  4    MATRICULE_RH	     VARCHAR2(8),
  5    INFORMATION_DATA      VARCHAR2(4000),
  6    DATE_TRAITEMENT	     TIMESTAMP(0),
  7    LINE_NUMBER	     NUMBER
  8  );
  9  /

Type created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> CREATE OR REPLACE
  2  TYPE t_toto AS TABLE OF ZY_W;
  3  /

Type created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> create table RRGQTZY_W0 (
  2    CODE_INFORMATION      VARCHAR2(4),
  3    MATRICULE_RH	     VARCHAR2(8),
  4    INFORMATION_DATA      VARCHAR2(4000),
  5    DATE_TRAITEMENT	     TIMESTAMP(0),
  6    LINE_NUMBER	     NUMBER
  7  )
  8  /

Table created.

SCOTT@orcl_11gR2> insert into rrgqtzy_w0 values
  2    ('a', '01192704', 'b', systimestamp, 1)
  3  /

1 row created.

SCOTT@orcl_11gR2> insert into rrgqtzy_w0 values
  2    ('c', '01192704', 'd', systimestamp, 2)
  3  /

1 row created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> DECLARE
  2    p_toto t_toto;
  3  BEGIN
  4    SELECT CAST (MULTISET
  5  		(SELECT CODE_INFORMATION,
  6  			MATRICULE_RH,
  7  			INFORMATION_DATA,
  8  			DATE_TRAITEMENT,
  9  			LINE_NUMBER
 10  		 FROM	RRGQTZY_W0
 11  		 WHERE	MATRICULE_RH = '01192704')
 12  	       AS t_toto)
 13    INTO   p_toto
 14    FROM   DUAL;
 15  
 16    FOR r IN
 17  	 (SELECT line_number
 18  	  from	 table(p_toto))
 19    LOOP
 20  	 DBMS_OUTPUT.PUT_LINE(r.line_number);
 21    END LOOP;
 22  END;
 23  /
1
2

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> 

SQL> set serveroutput on
SQL> DECLARE
  2    p_toto t_toto;
  3  BEGIN
  4    SELECT CAST (MULTISET
  5    (SELECT CODE_INFORMATION,
  6     MATRICULE_RH,
  7     INFORMATION_DATA,
  8     DATE_TRAITEMENT,
  9     LINE_NUMBER
 10     FROM RRGQTZY_W0
 11     WHERE MATRICULE_RH = '01192704')
 12          AS t_toto)
 13    INTO   p_toto
 14    FROM   DUAL;
 15   FOR i IN 1..p_toto.count LOOP
 16    DBMS_OUTPUT.PUT_LINE(p_toto(i).line_number);
 17    END LOOP;
 18  END;
 19  /
1
2

PL/SQL procedure successfully completed.

SQL> 

DECLARE
  p_toto t_toto;
BEGIN
    SELECT  CAST(
                 COLLECT(
                         zy_w(
                              CODE_INFORMATION,
                              MATRICULE_RH,
                              INFORMATION_DATA,
                              DATE_TRAITEMENT,
                              LINE_NUMBER
                             )
                        )
                 AS t_toto
                )
      INTO  p_toto
      FROM  RRGQTZY_W0
      WHERE MATRICULE_RH = '01192704';
 FOR i IN 1..p_toto.count LOOP
	 DBMS_OUTPUT.PUT_LINE(p_toto(i).line_number);
  END LOOP;
END;
/
1
2

PL/SQL procedure successfully completed.

SQL> 

DECLARE
  p_toto t_toto;
BEGIN
    SELECT  zy_w(
                 CODE_INFORMATION,
                 MATRICULE_RH,
                 INFORMATION_DATA,
                 DATE_TRAITEMENT,
                 LINE_NUMBER
                )
      BULK  COLLECT
      INTO  p_toto
      FROM  RRGQTZY_W0
      WHERE MATRICULE_RH = '01192704';
 FOR i IN 1..p_toto.count LOOP
	 DBMS_OUTPUT.PUT_LINE(p_toto(i).line_number);
  END LOOP;
END;
/
1
2

PL/SQL procedure successfully completed.

SQL>