title: Oracle function compilation error with no errors
body: <p>I am creating a function, logged in as sys.</p>
  
  <pre><code>CREATE FUNCTION Core.Get_Contact_Code
  (
         First_Name IN NVARCHAR2,
         Middle_Name IN  NVARCHAR2,
         Last_Name IN NVARCHAR2
  ) 
  RETURN NVARCHAR2 
  IS 
      Ret_Val NVARCHAR2(10);
      C_Code NVARCHAR2(10);
  
      CURSOR contact_cursor IS 
      SELECT Contact_Code FROM (
          SELECT Contact_Code
          FROM Core.Contacts
          WHERE
          Contact_Code LIKE UPPER(left(First_Name,2) ||
          CASE    
              WHEN Middle_Name IS NULL THEN left(Last_Name,3) 
              ELSE left(Middle_Name,1) || left(Last_Name,2) 
          END || '%')
          ORDER BY Contact_Code desc)
     WHERE ROWNUM=1;
  BEGIN
      open contact_cursor;
      fetch contact_cursor into C_Code;
      close contact_cursor;
  
      IF C_Code IS NULL THEN
      Ret_Val := 
      UPPER(left(First_Name,2)||
          CASE    
              WHEN Middle_Name IS NULL THEN left(Last_Name,3) 
              ELSE left(Middle_Name,1) || left(Last_Name,2) 
          END
      )
         || '-' || '0001'; 
      ELSE
          Ret_Val := 
          UPPER(left(First_Name,2)||
              CASE    
                  WHEN Middle_Name IS NULL THEN left(Last_Name,3) 
                  ELSE left(Middle_Name,1) || left(Last_Name,2) 
              END
          )
             || '-' ||
                trim(to_char(CAST(right(C_Code,4) AS integer)+1,'0000')); 
      END IF;
  
      RETURN Ret_Val;
  END;
  /
  
  show errors;
  </code></pre>
  
  <p>When I execute the query, it says:</p>
  
  <pre><code>Warning: compiled but with compilation errors
  No errors.
  </code></pre>
  
  <p>But when I omit the schema Core in the definition</p>
  
  <pre><code>CREATE FUNCTION Get_Contact_Code
  </code></pre>
  
  <p>it compiles.</p>
  
  <p>Any ideas why Oracle behaves this way?</p>