CREATE OR REPLACE FUNCTION is_function (owner_in      IN VARCHAR2,    
                                        package_in    IN VARCHAR2,    
                                        subprog_in    IN VARCHAR2)    
   RETURN BOOLEAN    
IS    
   l_dummy   CHAR(1);    
BEGIN    
   /* keep it as simple as possible: you know it's a function if the the position = 0 */    
   SELECT null    
     INTO l_dummy    
     FROM user_arguments    
    WHERE     (package_name = package_in OR package_in IS NULL)    
          AND object_name = subprog_in    
          AND position = 0  
          AND rownum = 1;    
    
   RETURN true;  
EXCEPTION WHEN NO_DATA_FOUND then  
   RETURN false;    
END is_function; 

CREATE OR REPLACE PACKAGE my_pkg  
IS  
   PROCEDURE a_proc;  
  
   FUNCTION a_func  
      RETURN NUMBER;  
  
   FUNCTION a_func_ol  
      RETURN NUMBER;  
  
   FUNCTION a_func_ol (d IN DATE)  
      RETURN NUMBER;  
END; 

CREATE OR REPLACE PROCEDURE my_proc  
IS  
BEGIN  
   NULL;  
END; 

CREATE OR REPLACE FUNCTION my_func (v in varchar2) RETURN NUMBER  
IS  
BEGIN  
   RETURN NULL;  
END;  

CREATE OR REPLACE PROCEDURE bpl (val IN BOOLEAN)  
IS  
BEGIN  
   DBMS_OUTPUT.put_line (  
      CASE val WHEN TRUE THEN 'TRUE' WHEN FALSE THEN 'FALSE' ELSE 'NULL' END);  
END bpl; 

BEGIN 
   bpl (is_function (USER, 
                     'MY_PKG', 
                     'A_PROC')); 
   bpl (is_function (USER, 
                     'MY_PKG', 
                     'A_FUNC')); 
   bpl (is_function (USER, 
                     'MY_PKG', 
                     'A_FUNC_OL')); 
   bpl (is_function (USER, 
                     NULL, 
                     'MY_PROC')); 
   bpl (is_function (USER, 
                     NULL, 
                     'MY_FUNC' 
)); 
END;

CREATE OR REPLACE PACKAGE my_pkg    
IS    
   PROCEDURE a_proc;    
    
   FUNCTION a_proc  
      RETURN NUMBER;  
END my_pkg; 

BEGIN   
   bpl (is_function (USER,   
                     'MY_PKG',   
                     'A_PROC'));  
END;