scott@ORA92> alter session set nls_date_format = 'dd-Mon-yyyy'
  2  /

Session altered.

scott@ORA92> alter session set nls_date_language = 'FRENCH'
  2  /

Session altered.

scott@ORA92> select sysdate - 51 from dual
  2  /

SYSDATE-51
-----------
01-Aou-2004

scott@ORA92> create or replace function Func1
  2    (p_date VARCHAR2)
  3    return date
  4  as
  5    <b>PRAGMA AUTONOMOUS_TRANSACTION;</b>
  6  begin
  7    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE="AMERICAN"';
  8    return TO_DATE (p_date, 'dd-Mon-yy');
  9  end Func1;
 10  /

Function created.

scott@ORA92> show errors
No errors.
scott@ORA92> set serveroutput on
scott@ORA92> declare
  2    retval DATE;
  3  begin
  4    select FUNC1 ('01-Aug-04' ) into retval from dual;
  5    dbms_output.put_line (retval);
  6  end;
  7  /
01-Aug-2004

PL/SQL procedure successfully completed.