Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Find the code for a Procedure currently in the oracle database

Posted on 2011-02-17
5
Medium Priority
?
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 168 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 168 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 164 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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 …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

609 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