Solved

Find the code for a Procedure currently in the oracle database

Posted on 2011-02-17
5
370 Views
Last Modified: 2012-05-11

There is procedre in my database, PROCEDURE SYS.HANDLE_TBS_DDL. I want to see the script or the "code" that is currently residing in the database. Which table DBA_% or view V$_% can show me the code of this procedure. This procedure is there and is working as expected. I just want to see the code and see what calls it is making and where.
0
Comment
Question by:KamalAgnihotri
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34918650
DBA_SOURCE

if the procedure is part of a package, you'll need to look for the package name, not the procedure
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34918684
To be a little more specific:

select text from dba_source where owner='SYS' and name='HANDLE_TBS_DDL' order by line;


Also, you shouldn't create objects in the SYS schema.

0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 42 total points
ID: 34918725
to be even a little more spesific

select text from dba_source where owner ='SYS' and name = 'HANDLE_TBS_DDL'
and type = 'PACKAGE'  order by line
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 42 total points
ID: 34918776
>>'PACKAGE'  

How do you know it's a package?  

Anyway, to see the actual source you need: type='PACKAGE BODY'
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 41 total points
ID: 34918803
Based on the name, it is a stand-alone procedure  
I only mentioned the package part to be thorough

so

type = 'PROCEDURE'  


would be correct, but redundant because there couldn't be another object with that name.
the type would only apply if it was, in fact, a package.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 106
Component is listed with a Protocol more than once 3 42
run sql script from putty 4 66
Creation date for a PDB 5 39
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question