sqlplus, view stored procedures

I would like to view stored procedures and packages in sqlplus.

All stored procedures, functions, and packages can be found in the user_source, dba_source, or all_source (depending on your access). Let's examine user_source.

foo_owner@FOO> desc user_source
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME						    VARCHAR2(30)
 TYPE						    VARCHAR2(12)
 LINE						    NUMBER
 TEXT						    VARCHAR2(4000)

foo_owner@FOO> 

Let's examine user_source.type to see what kind of objects are available to view,

foo_owner@FOO> SELECT DISTINCT type FROM user_source;

TYPE
------------
PROCEDURE
PACKAGE
PACKAGE BODY
FUNCTION
TYPE

5 rows selected.

foo_owner@FOO>

The user_source table (like dba_source and all_source) contains line-numbered text for functions, procedures, and packages.

If you want a list a packages, you could simply run the following:

foo_owner@FOO> SELECT DISTINCT name FROM user_source WHERE type = 'PACKAGE';

NAME
------------------------------
PROCS_GIFT_SUBS
PROCS_TAX_EXCEPTION_REPORT_V1
PROCS_GIFT_SUBS_TAX
PROCS_FLASH_REPORT
PROCS_TAX_V1
PROCS_FISCAL
PROCS_EXCEPTION_REPORT
PROCS_TAX
...

If you'd like to view the source for a package (the package and package body), you can query the user_source.text as follows,

foo_owner@FOO> SELECT text FROM user_source WHERE name = 'PROCS_DATE' ORDER BY type,line;

TEXT
----------------------------------------------------------------------------------------------------
PACKAGE 	    "PROCS_DATE" AS
  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE;
  FUNCTION DATE_FROM_ID(IN_ID NUMBER) RETURN DATE RESULT_CACHE;
END;

PACKAGE BODY		 "PROCS_DATE" AS

  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE
  IS
    ID NUMBER;
  BEGIN
    SELECT D.ID INTO ID FROM DATES_DIM D WHERE SQL_DATE = TRUNC(IN_DATE);
    RETURN ID;
  END;

  FUNCTION DATE_FROM_ID(IN_ID NUMBER) RETURN DATE RESULT_CACHE
  IS
    the_date DATE;
  BEGIN
    SELECT sql_date INTO the_date FROM dates_dim WHERE id = in_id;
    return the_date;
  END;

END;

foo_owner@FOO> 
This entry was posted in oracle. Bookmark the permalink.