Querying DBA_SOURCE

Admin — Thu, 19 Feb 2009 16:30:28 GMT

I didn't have TOAD or any GUI utility in my laptop and I needed to view the source of a procedure. Now we all know how difficult it can be do view the source in DBA_SOURCE view. The output can be horrible and you'll never know what you're looking at if you don't format it properly.

Here is a quick outline to help you get a better output from DBA_SOURCE view.

SQL>
SQL>
SQL>
SQL> set feedback on
SQL> set heading off
SQL> set termout off
SQL> set linesize 1000
SQL> set trimspool on
SQL> set verify off
SQL>
SQL> select decode( type||'-'||to_char(line,'fm99999'),
2 'PACKAGE BODY-1', '/'||chr(10),
3 null) ||
4 decode(line,1,'create or replace ', '' ) ||
5 text text
6 from dba_source
7 where name = '';

You can adjust the output based on your linesize and pagesize.

Good luck.

Thanks,

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Database Administration / Querying DBA_SOURCE / Latest Posts

Querying DBA_SOURCE