Solved

Find the code for a Procedure currently in the oracle database

Posted on 2011-02-17
5
371 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 77

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 77

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 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