I would like to modify a stored procedure which was created by a developer who has left our project. My question is how to view or get the current code of a stored procedure?
All developers always store their source code in well-known and easily accessible places, and no developer would ever think of leaving a project without writing complete documentation that included this information.
But seriously, you can query the view all_source (or user_source, or, if you have the right privileges, dba_source).
DESCRIBE all_source Name Null? Type ---------------- -------- --------------- OWNER VARCHAR2(30) NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000)
Possible values for TYPE include:
- JAVA SOURCE
- PACKAGE BODY
- TYPE BODY
Package heads and bodies will have the same name, so remember to specify which one you want.
SET LINESIZE 200 SET PAGESIZE 9999 SET TRIMSPOOL ON SPOOL util_body.sql SELECT text FROM all_source WHERE owner = 'LONGGONE' AND name = 'UTIL' AND type = 'PACKAGE BODY' ORDER BY line; SPOOL OFF
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.