PACKAGE BODY PKG1

Function list:


1. FCN_SEQ1_GET_NEXT

Procedure list:


1. PRC_2
2. PRC_REF_CUR_TAB2

Cursors list:


1. CUR_GET_VIEW1
2. CUR_SEQ1_NEXT

CREATE OR REPLACE PACKAGE BODY pkg1 IS


  user_defined_exc EXCEPTION;

  CURSOR cur_get_view1 IS /*=>*/
    SELECT * FROM view1;

--- Returns the next value of SEQ1  

/* FCN_SEQ1_GET_NEXT: ref_list, ref_tree, fcall_tree */

  FUNCTION fcn_seq1_get_next RETURN NUMBER
  IS
    CURSOR cur_seq1_next IS /*=>*/
      SELECT seq1.NEXTVAL
        FROM dual;
    lv_next_val NUMBER;
  BEGIN
    OPEN cur_seq1_next;
    FETCH cur_seq1_next INTO lv_next_val;
    CLOSE cur_seq1_next;
    RETURN lv_next_val;
  END;


/* ***************************************************
  This procedure returns a reference cursor pointing
  to a SELECT statement.
*************************************************** */

/* PRC_REF_CUR_TAB2: ref_list, ref_tree, fcall_tree */

  PROCEDURE prc_ref_cur_tab2(iov_ref IN OUT type1)
  IS
    lv_max_val NUMBER;
  BEGIN
    lv_max_val := pkg2.fcn_big_max_val;
    OPEN iov_ref FOR
      SELECT *
      FROM tab2
      WHERE f1<=lv_max_val;
  END;

/* ***************************************************
  This procedure is a sample of how reference cursors
  can be used.
*************************************************** */

/* PRC_2: ref_list, ref_tree, fcall_tree */

  PROCEDURE prc_2
  IS
    lv_cur typ1;
    lv_rec tab2%ROWTYPE;
    lv_next_val NUMBER;
  BEGIN
    prc_ref_cur_tab2(lv_cur);
    LOOP
      FETCH lv_cur INTO lv_rec;
      EXIT WHEN lv_cur%NOTFOUND;
      IF lv_rec.f1=pkg2.fcn_max_val THEN
         BEGIN
           UPDATE tab2
             SET f4='This is maximum value'
             WHERE f1=lv_rec.f1;
           EXCEPTION
             WHEN OTHERS THEN
                /* ********************************************
                   this is an artificial example to demonstrate 
                   as many features of the PLSQL2HTML converter
                   as possible
                ********************************************* */
                RAISE user_defined_exc;
         END;
      END IF;
    END LOOP;
    CLOSE lv_cur;

    FOR lv_rec1 IN cur_get_view1 LOOP
      --- do some processing of the rows to be fetched
    END LOOP;

  END;

END;
/