ORA-24344: success with compilation errors occurs when using
execute immediate to execute statements such as
create or replace view,
create procedure,
alter procedure etc. and the
object that is being modified or created (i.e. the
view, procedure etc.) have an error, for example because they reference a table that does not exist etc.
CREATE OR REPLACE VIEW
This error is for example thrown when creating a view with the
force option on a table that is inexistant, as is demonstrated in the following anonymous
PL/SQL block:
begin
execute immediate '
create or replace force view TQ84_ORA_24344 as
select
xyz
from
inexisting_table';
dbms_output.put_line('View created without exception');
exception when others then
dbms_output.put_line(sqlerrm);
end;
/
CREATE PROCEDURE
The procedure which the execute immediate statement tries to create is syntactially incorrect: it lacks a semicolon after a statement:
begin
execute immediate q'[
create procedure tq84_ora_24344_prc as
dbms_output.put_line('hello world') -- Note the missing semicolon!
end tq84_ora_24344_prc;
]';
end;
/
ERROR:
ORA-24344: success with compilation error
ORA-06512: at line 2
Interesting behavior in combination with EXECUTE IMMEDATE in 19c
Tomáš Š. has notified me of an interesting behavior if an ORA-24344 error is thrown in a
create or replace view force statement being executed in
execute immediate in an Oracle 19c (tested with oracle 19.21) database.
The following
PL/SQL block creates the view
tq84_view_1 and then stops executing. The interesting thing is that Oracle reports
PL/SQL procedure successfully completed:
begin
dbms_output.put_line('-- start --');
execute immediate 'create or replace force view tq84_view_1 as select * from inexisting_table';
dbms_output.put_line('-- not reached --');
execute immediate 'create or replace force view tq84_view_2 as select * from inexisting_table';
end;
/
-- start --
The following query confirms that only the first view is created (with status being invalid):
select
object_name,
status,
created,
last_ddl_time,
timestamp
from
user_objects
where
object_name like 'TQ84_VIEW_%';
If the
execute immediate statements are placed within
exception handlers, it shows that there is indeed the ORA-24344 exception thrown and handled such that both views are created:
drop view tq84_view_1;
begin
dbms_output.put_line('-- start --');
begin
execute immediate 'create or replace force view tq84_view_1 as select * from inexisting_table';
exception when others then dbms_output.put_line(sqlerrm); end;
dbms_output.put_line('-- reached --');
begin
execute immediate 'create or replace force view tq84_view_2 as select * from inexisting_table';
exception when others then dbms_output.put_line(sqlerrm); end;
dbms_output.put_line('-- reached as well --');
end;
/