Q
Manage Learn to apply best practices and optimize your operations.

How to find source code

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:

  • FUNCTION

  • JAVA SOURCE

  • PACKAGE

  • PACKAGE BODY

  • PROCEDURE

  • TRIGGER

  • TYPE

  • 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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close