Process date value : Utility Package « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Package

SQL>
SQL> CREATE OR REPLACE PACKAGE sessval IS
  2     PROCEDURE set_mydate (date_in IN DATE);
  3     FUNCTION mydate RETURN DATE;
  4  END sessval;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY sessval IS
  2     g_mydate DATE;
  3     PROCEDURE set_mydate (date_in IN DATE) IS
  4     BEGIN
  5        IF date_in IS NULL OR date_in > SYSDATE
  6        THEN DBMS_OUTPUT.PUT_LINE ('Sessval.mydate cannot be set into the future.');
  7        ELSE DBMS_OUTPUT.PUT_LINE ('Current value of sessval.mydate: ' || g_mydate);
  8             DBMS_OUTPUT.PUT_LINE ('New value of sessval.mydate: ' || date_in);
  9             DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
 10           g_mydate := date_in;
 11        END IF;
 12     END;
 13
 14     FUNCTION mydate RETURN DATE IS
 15     BEGIN RETURN g_mydate;
 16     END;
 17  END sessval;
 18  /

Package body created.

SQL> CREATE OR REPLACE PROCEDURE test_trace
  2  IS
  3  BEGIN
  4     sessval.set_mydate ( NVL (sessval.mydate, SYSDATE) -100);
  5  END;
  6  /

Procedure created.