來源出處:
http://dba-oracle.com/t_display_oracle_stored_procedures.htm

Display Oracle stored procedures Oracle Tips by Burleson Consulting September 6, 2008

Name                                       Null?    Type
------------------------------------------------------------------
OWNER                                          VARCHAR2(30)
NAME                                               VARCHAR2(30)
TYPE                                                VARCHAR2(12)
LINE                                                 NUMBER
TEXT                                                VARCHAR2(4000)

 

Notice that the TEXT column is a VARCHAR2(4000) data type. This 4000 length indicates that to properly view any text lines you may need to set the LONG variable in SQL*Plus to 4000. A simple select will return all of the lines for a given stored object in order as shown in Figure 6.

 

select 'create or replace '||text,line
from
 dba_source
where
 owner = upper('&&owner') and
 type = upper('&&obj_type') and
 name = upper('&&obj_name') and
 line = 1
UNION
select text,line
from
 dba_source s2
where
 s2.name = upper('&&3') and
 s2.owner = upper('&&1') and
 s2.type = upper('&&2') and
 s2.line > 1
order by 2;

Question:
I want the SQL to display all stored procedures in my database. I also want to know how to show the source for Oracle stored procedures. Where do I find stored procedure information in Oracle dictionary?

Answer:
 The dba_source view contains the details on Oracle stored procedures and it’s easy to query dba_source to see stored procedure details.

For a roadmap to the dictionary views, get the free Oracle poster, indispensible for these types of SQL queries.

The DBA_SOURCE view is based on the SOURCE$ table that holds all of the source code for the stored objects (other than triggers and views) in the database.