Oracle DB Object - Gists

Select all invalid objects:

	select object_name from user_objects
	where status = 'INVALID'
	/

view raw invalid.sql hosted with ❤ by GitHub

Recompile all invalid objects:

	set head off
	set feedback off
	spool compile.lis
	select 'alter package '|| owner ||'.'||object_name||' compile;'
	from all_objects
	where object_type like 'PACKAGE'
	and status = 'INVALID'
	/
	select 'alter package '|| owner ||'.'||object_name||' compile body;'
	from all_objects
	where object_type like 'PACKAGE BODY'
	and status = 'INVALID'
	/
	select 'alter trigger '|| owner ||'.'||object_name||' compile;'
	from all_objects
	where object_type like 'TRIGGER'
	and status = 'INVALID'
	/
	select 'alter view '|| owner ||'.'||object_name||' compile;'
	from all_objects
	where object_type like 'VIEW'
	and status = 'INVALID'
	/
	select 'alter materialized view '|| owner ||'.'||object_name||' compile;'
	from all_objects
	where object_type like 'MATERIALIZED VIEW'
	and status = 'INVALID'
	/
	select 'alter type '|| owner || '.'||object_name||' compile;'
	from all_objects
	where object_type like 'TYPE'
	and status = 'INVALID'
	/
	select 'alter procedure '|| owner || '.'||object_name||' compile;'
	from all_objects
	where object_type like 'PROCEDURE'
	and status = 'INVALID'
	/
	select 'alter function '|| owner || '.'||object_name||' compile;'
	from all_objects
	where object_type like 'FUNCTION'
	and status = 'INVALID'
	/
	spool off
	set head on
	set feedback on
	@compile.lis
	set lines 2000
	select substr(object_name,1,30) object_name, owner, object_type, status, timestamp from all_objects where status !='VALID';

view raw recompile.sql hosted with ❤ by GitHub

One of the really annoying things about Oracle PL/SQL is the way it tells you something is wrong, gives you a line number and then leaves it up to you to find the statement that caused the error. Well, this little piece of SQL gives you the exact line in error plus the lines immediately before and after it. (Acknowledgements to Ken Atkins of ARIS Consulting).

	set verify off
	define obj_name = '&1';
	column outline format a105 heading 'Error Listing';
	break on err_text skip 2;
	set linesize 105;
	set pagesize 0;
	set pause off;
	spool listerr
	SELECT
	decode(to_char(us.line), to_char(ue.line-7),ue.text,
	to_char(ue.line-6),'',
	to_char(ue.line+6),'',
	to_char(ue.line) ,
	' --'||to_char(us.line,'99990')||' '||us.text,
	''||to_char(us.line,'99990')||''||us.text) outline
	from user_source us, user_errors ue
	where us.name = '&obj_name'
	and us.line between (ue.line-7) and (ue.line+6)
	and us.name = ue.name
	and us.type = ue.type
	-- This predicate is put here to elminate this useless fallout error
	and ue.text != 'PL/SQL: Statement ignored'
	/
	spool off
	set pagesize 22;

view raw plsqlerrors.sql hosted with ❤ by GitHub