Friday, June 20, 2008

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.

3 comments:

MosheElisha said...

I had the same issue. It appears that this error also occurs when the line break char used in the trigger code is not suitable to the platform or if there are lines of code that are very very long.

MosheElisha said...

I also had this issue when I executed immediately code that did not compile well. In order to see the compilation errors I used the query:

select * from user_errors;

After fixing the errors shown it worked perfectly.

Frosty said...

Thanks MosheElisha.

select * from user_errors;

...is the way to go.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles