- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: PL SQL query
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2002 07:25 AM
тАО09-10-2002 07:25 AM
PL SQL query
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;
/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2002 05:22 PM
тАО09-10-2002 05:22 PM
Re: PL SQL query
--------------
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2002 08:08 PM
тАО09-10-2002 08:08 PM
Re: PL SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-10-2002 09:40 PM
тАО09-10-2002 09:40 PM
Re: PL SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-11-2002 01:19 AM
тАО09-11-2002 01:19 AM
Re: PL SQL query
Warning: PL/SQL compilation errors.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-11-2002 02:24 PM
тАО09-11-2002 02:24 PM
Re: PL SQL query
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-12-2002 12:48 AM
тАО09-12-2002 12:48 AM
Re: PL SQL query
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