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;
/