title: local function inside PL/SQL script
body: <p>I'm trying to execute this code in Oracle 10 SQL Developer:</p>
  
  <pre><code>FUNCTION is_valid_date (p_val in VARCHAR2, p_format IN VARCHAR2 ) 
  RETURN numeric IS
      l_date VARCHAR2(100);
  BEGIN
      l_date := TO_date( p_val, p_format );
      RETURN 1;
  EXCEPTION
      WHEN OTHERS THEN
          RETURN 0;
  END is_valid_date;
  
  
  BEGIN
  DBMS_OUTPUT.PUT_LINE(is_valid_date('20120101', 'YYYYMMDD' )); 
  END;
  </code></pre>
  
  <p>but I get a generic error without any specific Oracle code, as if it is a syntax problem.</p>
  
  <p>I need to check if a date is valid and, as there is no Oracle built in function for that, I have defined it inside my script (I don't want it to be global or stored somewhere).</p>
  
  <p>Edit:</p>
  
  <p>I have found a solution on an oracle forum using oracle regexp, instead of a function. My script is:</p>
  
  <pre><code>BEGIN
  
  select * from mytable where not REGEXP_LIKE(mydatefield, '(((0[1-9]|[12]\d|3[01])\.(0[13578]|1[02])\.((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\.(0[13456789]|1[012])\.((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\.02\.((19|[2-9]\d)\d{2}))|(29\.02\.((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))')
  
  END;
  </code></pre>
  
  <p>where mydatefield is in the format DD.MM.YYYY</p>