?
Solved

Find the code for a Procedure currently in the oracle database

Posted on 2011-02-17
5
Medium Priority
?
373 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

764 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