Solved

Find the code for a Procedure currently in the oracle database

Posted on 2011-02-17
5
368 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 73

Expert Comment

by:sdstuber
Comment Utility
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)
Comment Utility
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
Comment Utility
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
Comment Utility
>>'PACKAGE'  

How do you know it's a package?  

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

Assisted Solution

by:sdstuber
sdstuber earned 41 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now