If you are finding short of a Oracle schema browser such as TOAD , SQL work bench etc , below SQL statements may be handy to get the source code of a object which could be a procedure , trigger , function etc.
- Connect as oracle sysdba or dba user
- The type of oracle objects which source code may be extracted
SQL> select distinct(type) from all_source;
PROCEDURE
PACKAGE
PACKAGE BODY
LIBRARY
TYPE BODY
TRIGGER
FUNCTION
JAVA SOURCE
TYPE
- Sample Query to extract source code of a oracle procedure
SQL> set linesize 120
SQL> set pagesize 0
SQL> select text from all_source where owner='SYSTEM' and type='PROCEDURE' and NAME='ORA$_SYS_REP_AUTH' order by LINE;
procedure ora$_sys_rep_auth as
begin
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repschema TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repprop TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_aqcall TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_calldest TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_error TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_destination TO SYS ' ||
'WITH GRANT OPTION';
end;
- Example to extract a oracle trigger source code
SQL> select text from all_source where owner='SYSTEM' and type='TRIGGER' and NAME='REPCATLOGTRIG' order by LINE;
TRIGGER system.repcatlogtrig
AFTER UPDATE OR DELETE ON system.repcat$_repcatlog
BEGIN
sys.dbms_alert.signal('repcatlog_alert', '');
END;
Hope this is useful to some SQL beginners