PL SQL query

[Dave Walley]

Hi

I have written a piece of code to compile invalid objects within a database. It works fine until I get compilation errors. Can anybody tell me how to handle the exception, and continue with the compile operation for the remainderof the objects. As you will see I have a seperate section for compiling Package bodies. The message returned is
Warning: PL/SQL compilation errors.

This is my code.

declare
cursor dw is
select a.object_name,
a.object_type,
a.owner
from dba_objects a
where a.status != 'VALID';
v_cursor number;
v_numrows integer;
begin
for col_rec in dw
loop
v_cursor:= dbms_sql.open_cursor;
if col_rec.object_type!= 'PACKAGE BODY' then
dbms_sql.parse(v_cursor, ' alter '||col_rec.object_type||' '||col_rec.owner||'.'
||col_rec.object_name||' compile',
DBMS_SQL.V7);
else
dbms_sql.parse(v_cursor, ' alter package '||col_rec.owner||'.'||col_rec.object_
name||' compile body',
DBMS_SQL.V7);
end if;
v_numrows:= dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end loop;
end;
/

[Brian Crabtree]

Inside of your "end loop;" do the following:
--------------
EXCEPTION
when -0000 then
dbms_output.put_line('Object '||col_rec.owner||' of type '||col_rec.object_type||' did not compile');
when others then
dbms_output.put_line('Object '||col_rec.owner||' of type '||col_rec.object_type||' failed for '||SQLCODE);
--------------

You will need to put the error number in for the -0000 (the - is needed).

Brian

[Tom Geudens]

Hi,
This has been a while for me, so if the following is very stupid ... excuse me :-)
Can't you put the dbms_sql.parse statement in an "BEGIN EXCEPTION END"-block of itself ? That way you can "catch" the execption of 1 specific statement ...

Regards,
Tom

[Yogeeraj_1]

hi,

you may consider adding the following pieces of codes in you procedure.

at the start:
set serveroutput on

at the end:
show errors

"show errors" will display the errors as follows:
============================
example:

SQL> show errors
Errors for PROCEDURE INS_EMP_DEPT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/11 PL/SQL: SQL Statement ignored
3/25 PLS-00201: identifier 'SCOTT.DEPT' must be declared
4/1 PL/SQL: SQL Statement ignored
4/25 PLS-00364: loop index variable 'X' use is invalid
5/1 PL/SQL: SQL Statement ignored
5/31 PLS-00201: identifier 'SCOTT.EMP' must be declared

============================

hope this helps!
Best Regards
Yogeeraj

[Dave Walley]

Thank you all for your advice, but the problem I have is that no Oracle error message is returned, just the message

Warning: PL/SQL compilation errors.

[Brian Crabtree]

Dave,

The errors are still there, just not being output to you. I would suggest working using the "EXCEPTION when OTHERS then" portion, as it should allow you to bypass the procedure exiting each time it fails.

Brian

[Christian Gebhardt]

Hi

There's a script from oracle to recompile all invalid PL/SQL-routines:

$ORACLE_HOME/rdbms/admin/utlrp.sql

This script also looks at dependencies and compile invalid object in correct order.

Is it this what you want?

Christian