The equivalent of sp_helptext in Oracle??

[gamaz]

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.

[Sybrand Bakker]

Refer to user_source.
I guess you consistently refuse to read manuals, do you?

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

[Karsten Farrell]

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

[gamaz]

Hey Sybrand,
Thanks for the help. I appreciate it.
"Sybrand Bakker" <gooid...@sybrandb.demon.nl> wrote in message
news:tsequusmk2nins8gq...@4ax.com...