CREATE OR REPLACE FUNCTION get_dir_path (p_file IN VARCHAR2) RETURN varchar2
IS
  v_dir_path VARCHAR2(1000);
BEGIN
/*
|| This Function returns a varchar2 string of the directory path of the file name.  -- not including trailing slash. 
||  Written by: Daniel Coe
||  Date: 10/09/2002
*/	
	BEGIN
	
	  IF instr(p_file,'/') > 0 THEN -- Unix
	  
   	  	 v_dir_path := substr(p_file,1,(instr(p_file,'/',-1,1)-1));
		 
	  ELSIF instr(p_file,'\') > 0 THEN -- Windows
	  
	  	 v_dir_path := substr(p_file,1,(instr(p_file,'\',-1,1)-1));
		 
	  END IF;
	  	  	 
	 EXCEPTION
	  WHEN value_error THEN
	  	 v_dir_path := sqlerrm;
		 
	END;
		 
   RETURN v_dir_path;
END;
/