Q:
Which table and timestamp column is used for 'show errors' definition of
'the previously defined object'?


Longer description:

Concider you have the schema definition for a larger schema with
tables and PL/SQL objects in a text file. Everything works for the
current version but you want some error control for new pices of code.

So, you want SQLPLUS to abort when the first defective object definition
is found and report an exist status unequal to zero in case of problems.

I tried to catch compilation errors with WHENEVER SQLERROR EXIT FAILURE
and noticed sqlplus exits for errors in SQL code, but errors in PL/SQL
code will pass unnoticed. Sqlplus will report the errors for the last
compiled object from USER_ERRORS with 'show errors', but it will not
abort the script. WHENEVER SQLERROR does not catch this type of errors.

Google found an article from T.Kyte with a PLS/SQL procedure which
addresses this problem. The procedure raises an exception if
it finds an error message in USER_ERRORS. But the original procedure
required me to enter and update the object name as a parameter of
the procedure for each use of the procedure.

Would't it be nice to let the method it self find the 'most recently
compiled object' and raise only if that object had an error?

I tried to modify the procedure, but failed to find the proper
timestamp column.

Do you know how which view and column finds the 'most recently compiled
object'?

Thanks,

Volker

############################################# CODE
create or replace procedure test_error
-- derived from a procedure of T.Kyte from the news groups
as
n number;
begin
for rec in (select OBJECT_NAME, OBJECT_TYPE
from (
select OBJECT_NAME, OBJECT_TYPE from USER_OBJECTS
order by TIMESTAMP DESC
-- what is the proper view and time column for 'show errors'
-- for selection of the most recently compiled object?
)
where ROWNUM = 1)
loop
-- this 'loops' exactly once
select count(*) into n
from USER_ERRORS
where name = rec.OBJECT_NAME and TYPE = rec.OBJECT_TYPE;
if ( n > 0 ) then
raise_application_error(
-20000, n || ' Errors on ' ||rec.OBJECT_TYPE
||' '|| rec.OBJECT_NAME );
end if;
end loop;
end;
/
show errors

############################################ USAGE example
WHENEVER SQLERROR EXIT FAILURE

BEGIN
-- valid code
null;
END;
/
show errors
exec test_error

BEGIN
N := 1; -- invalid code
END;
/
show errors
exec test_error
-- above raises and produces an
EXIT 0
############################################

btw:
Oracle 92, Linux



--
Volker Apelt

"Volker Apelt" <gq437x@yahoo.de> a écrit dans le message de
news:lg1xe9asta.fsf@chi.biosolveit.loc...
>
<snip>
> Do you know how which view and column finds the 'most recently compiled
> object'?
<snip>

user_objects.last_ddl_time

Regards
Michel Cadot

"Michel Cadot" <micadot{at}altern{dot}org> writes:

> "Volker Apelt" <gq437x@yahoo.de> a écrit dans le message de
> news:lg1xe9asta.fsf@chi.biosolveit.loc...
>>
> <snip>
>> Do you know how which view and column finds the 'most recently compiled
>> object'?
> <snip>
>
> user_objects.last_ddl_time


Replaceing TIMESTAMP with last_ddl_time

Merci Michel,

Volker

--
Volker Apelt