cancel
Showing results for 
Search instead for 
Did you mean: 

PL SQL query

Dave Walley
Frequent Advisor

PL SQL query

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;
/
why do i do this to myself
6 REPLIES
Brian Crabtree
Honored Contributor

Re: PL SQL query

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
Honored Contributor

Re: PL SQL query

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
A life ? Cool ! Where can I download one of those from ?
Yogeeraj_1
Honored Contributor

Re: PL SQL query

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Dave Walley
Frequent Advisor

Re: PL SQL query

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.

why do i do this to myself
Brian Crabtree
Honored Contributor

Re: PL SQL query

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
Honored Contributor

Re: PL SQL query

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