Re: The equivalent of sp_helptext in Oracle??

gamaz wrote:
> Hi,
> I need to get the texts of all stored procedure, triggers and packages of
> one user. What would
> be the best way to handle this? I know in sql server, I used to use the
> sp_helptext command.
> Any help is appreciated in advance. Thanks.
>

In Oracle, use sqlplus. Of course, since all the SQL Server sp_whatever are just stored procedures, you could write your own SPs in Oracle.

To get the text of all your stored procedures:

set echo off feedback off pause off pagesize 0 linesize 90 column text format a90 wrapped fold_before select text
from user_source
where type = 'PROCEDURE'
order by name, line;
clear columns
set feedback on pagesize 24 linesize 80

To get the text of all your triggers:

set heading off echo off feedback off pause off pagesize 0 linesize 80 set long 100000 longchunksize 1000
column trigger_body format a80 wrapped fold_before select 'Trigger: ' || trigger_name,

        trigger_body
from user_triggers
order by trigger_name;
set feedback on pagesize 24
set long 80 longchunksize 80
clear columns

To get the text of all your packages:

set echo off feedback off pause off pagesize 0 linesize 90 column text format a90 wrapped fold_before select text
from user_source
where type in ('PACKAGE', 'PACKAGE BODY') order by name, type, line;
clear columns
set feedback on pagesize 24 linesize 80 Received on Tue Dec 03 2002 - 18:09:52 CST