CREATE USER TESTER
  IDENTIFIED BY "tester"
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for TESTER 
  GRANT DBA TO TESTER WITH ADMIN OPTION;
  ALTER USER TESTER DEFAULT ROLE ALL;

CREATE TABLE TESTER.TESTING_TABLE
(
  ROWNO       NUMBER,
  MODROWNO    NUMBER,
  TRUNCROWNO  NUMBER
);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (1, 1, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (2, 2, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (3, 3, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (4, 4, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (5, 5, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (6, 6, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (7, 7, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (8, 8, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (9, 9, 0);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (10, 10, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (11, 11, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (12, 12, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (13, 13, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (14, 14, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (15, 15, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (16, 16, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (17, 17, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (18, 18, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (19, 19, 1);
Insert into TESTER.TESTING_TABLE
   (ROWNO, MODROWNO, TRUNCROWNO)
 Values
   (20, 20, 2);
COMMIT;

CREATE OR REPLACE PACKAGE TESTER.pkg_test
IS
   TYPE tr_rec01 IS RECORD (rowno TESTER.TESTING_TABLE.TRUNCROWNO%TYPE);

   TYPE tc_rec01 IS REF CURSOR
      RETURN tr_rec01;

   TYPE tr_rec02 IS RECORD
   (
      rowno      TESTER.TESTING_TABLE.ROWNO%TYPE,
      modrowno   TESTER.TESTING_TABLE.MODROWNO%TYPE
   );

   TYPE tc_rec02 IS REF CURSOR
      RETURN tr_rec02;

   /*******************************************************************/
   /*  PROCEDURE proc_test01                                          */
   /*******************************************************************/
   PROCEDURE proc_test01 (pi_modrowno IN NUMBER, po_result OUT NUMBER);

   /*******************************************************************/
   /*  PROCEDURE proc_test02                                          */
   /*******************************************************************/
   PROCEDURE proc_test02 (pi_truncrowno IN NUMBER, po_result OUT tc_rec01);

   /*******************************************************************/
   /*  PROCEDURE proc_test03                                          */
   /*******************************************************************/
   PROCEDURE proc_test03 (pi_truncrowno IN NUMBER, po_result OUT tc_rec02);
END pkg_test;

CREATE OR REPLACE PACKAGE BODY TESTER.pkg_test
IS
   /*******************************************************************/
   /*  PROCEDURE proc_test01                                          */
   /*******************************************************************/
   PROCEDURE proc_test01 (pi_modrowno IN NUMBER, po_result OUT NUMBER)
   IS
   BEGIN
      SELECT rowno
        INTO po_result
        FROM tester.testing_table
       WHERE modrowno = pi_modrowno;

      RETURN;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN;
   END proc_test01;


   /*******************************************************************/
   /*  PROCEDURE proc_test02                                          */
   /*******************************************************************/

   PROCEDURE proc_test02 (pi_truncrowno IN NUMBER, po_result OUT tc_rec01)
   IS
   BEGIN
      OPEN po_result FOR
         SELECT rowno
           FROM tester.testing_table
          WHERE truncrowno = pi_truncrowno;

      RETURN;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN;
   END proc_test02;

   /*******************************************************************/
   /*  PROCEDURE proc_test03                                          */
   /*******************************************************************/

   PROCEDURE proc_test03 (pi_truncrowno IN NUMBER, po_result OUT tc_rec02)
   IS
   BEGIN
      OPEN po_result FOR
         SELECT rowno, modrowno
           FROM tester.testing_table
          WHERE truncrowno = pi_truncrowno;

      RETURN;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN;
   END proc_test03;
END pkg_test;

