CREATE OR REPLACE FUNCTION MY_FUNC RETURN INTEGER AS
BEGIN
    MY_PACKAGE.MY_INTEGER := NVL(MY_PACK.MY_INTEGER,0) + 1;
    RETURN(MY_PACK.MY_INTEGER);
END;

SELECT a.*, my_func()
FROM   my_table;

RNDS  --  read no database state
RNPS  --  read no package state
WNDS  --  write no database state
WNPS  --  write no package state

PROCEDURE do_something (operation IN VARCHAR2, table_name IN VARCHAR2, conditions IN VARCHAR2)


sql_query varchar2(4000) := operation || table_name || conditions;

BEGIN

EXECUTE IMMEDIATE sql_query;

END;

SQL> CREATE TABLE bob (a NUMBER);

Table created.

SQL> CREATE PACKAGE bob_pkg AS
  2  
  3  PROCEDURE bob_proc_dynamic;
  4  pragma restrict_References (bob_proc_dynamic, WNDS);
  5  
  6  END bob_pkg;
  7  /

Package created.

SQL> CREATE PACKAGE BODY bob_pkg AS
  2  
  3  PROCEDURE bob_proc_dynamic AS
  4  BEGIN
  5  
  6    EXECUTE IMMEDIATE 'INSERT INTO bob (a) values (1)';
  7  
  8  END bob_proc_dynamic;
  9  END bob_pkg;
 10  /

Package body created.

SQL> CREATE PACKAGE bob_pkg_2 AS
  2  
  3  PROCEDURE bob_proc;
  4  pragma restrict_References (bob_proc, WNDS);
  5  END bob_pkg_2;
  6  /

Package created.

SQL> CREATE PACKAGE BODY bob_pkg_2 AS
  2  
  3  PROCEDURE bob_proc AS
  4  BEGIN
  5  
  6    INSERT INTO bob (a) values (1);
  7    COMMIT;
  8  
  9  END bob_proc;
 10  
 11  END bob_pkg_2;
 12  /

Warning: Package Body created with compilation errors.

SQL> sho errors
Errors for PACKAGE BODY BOB_PKG_2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00452: Subprogram 'BOB_PROC' violates its associated pragma
SQL> 
SQL> 
SQL> select * from bob;

no rows selected

SQL> exec bob_pkg.bob_proc_dynamic;
BEGIN bob_pkg.bob_proc_dynamic; END;

*
ERROR at line 1:
ORA-06538: statement violates WNDS RESTRICT_REFERENCES pragma
ORA-06512: at "LIVE.BOB_PKG", line 6
ORA-06512: at line 1


SQL> 

create table QUERY_LOG
(
  USER_NAME    VARCHAR2(30),
  REFRESH_DATE DATE default SYSDATE,
  QUERY_TYPE   VARCHAR2(30)
)
tablespace USERS;

------------------------------------

CREATE OR REPLACE FUNCTION max_log_date(p_user VARCHAR2) RETURN DATE IS

  max_date DATE;

BEGIN

  SELECT MAX(refresh_date)
    INTO max_date
    FROM query_log
   WHERE user_name = p_user;

  INSERT INTO query_log VALUES (p_user, sysdate, 'SELECT');

  COMMIT;

  RETURN max_Date;

END max_log_date;

------------------------------------------

SELECT *
  FROM query_log
 WHERE refresh_date = max_log_date(user)