ORA-24344: success with compilation error

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This in a rather curious error message that i got when i attempted to perform the following operation against a 9iR2 instance (i didn't check if the same happens on newer versions, but it should be the same):

create or replace force view test_view
as select a,b from dual2;

declare
procedure run(p_sql varchar2) as
begin
execute immediate p_sql;
end;
begin  
run('rename "TEST_VIEW" to TEST_VIEW2');
run('alter view  TEST_VIEW2 compile');
end;

ORA-24344: success with compilation error
ORA-06512: at line 4
ORA-06512: at line 8
24344. 00000 -  "success with compilation error"
*Cause:    A sql/plsql compilation error occurred.
*Action:   Return OCI_SUCCESS_WITH_INFO along with the error code

The anonymous PL/SQL block has been generated by SQL Developer in response to my request of renaming TEST_VIEW.
TEST_VIEW was in an invalid state because DUAL2 doesn't exist, but this fact doesn't prevent Oracle from renaming it, indeed SQL Developer throws the error but it renames the view successfully from TEST_VIEW to TEST_VIEW2.

Whilst the official documentation says that errors should be returned whenever the view remains in an invalid state using ALTER VIEW ... COMPILE, SQL developer seems to behave in an odd way because it throws ORA-24344 if you execute the command using the procedure shown above, but it simply logs a sort of warning if you execute it directly as a script.

And it doesn't report anything if you execute it as a single SQL statement.

This is certainly one of the most bizarre error messages i've come across so far!

See message translations for ORA-24344 and search additional resources.