How to see stored procedure contents? 3

[volcano]

Hi, I created some stored procedures long time ago in DB. Now I want to read the contents of those stored procedures. Is there any view or table I can search from? Thanks

 

[SantaMufasa]

Volcano,

You can use this script. (Since there is an ACCEPT/PROMPT, you must first save the code to a script and run that script):

accept obj_name prompt "Enter the name of the procedure you wish to see: "
set linesize 200
set feedback on
col line heading "Line|Num" format 9999
col text heading "Source Code" format a100
select line,text from user_source
where name = upper('&obj_name')
  and type = 'PROCEDURE'
order by line
/

Here is a sample invocation of the script (which I named "ShowProcs"):

@ShowProcs

Enter the name of the procedure you wish to see: showtime

 Line
  Num Source Code
----- -------------------------------------------------------------------
    1 procedure showtime is
    2 begin
    3     dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
    4 end;

4 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[volcano]

oh, yea, that's exactly what I want. Thanks a lot!

 

[DeepDiverMom]

Volcano,

I enjoy learning from MVP's responses to threads. As I reviewed this thread, I noticed that Mufasa produced a very useful piece of code. I also noticed that it seemed to do exactly what you wanted, but that you didn't toss a Star to him for his efforts.

I gather from his signature, that Mufasa supports his family by solving other's IT problems. When he is helping here on Tek-Tips, his only "pay" (besides a "Thank-you") is the Stars. The benefit to the rest of us is that when we see a Star, it is a signal that we can look at that thread and find a useful solution that worked.

I noticed that you have been a member of Tek-Tips for nearly five years, you have sought help on 60 different issues, and amazingly, you have never voted one single Star to anyone, ever !!! If we use Mufasa as a example, depending upon his consulting rates, he delivered a solution to you that probably would have cost his "paying customers" $50-$75. Multiply that by the 60 other times you have received help from others, and that's about $3,000 - $4,500-worth of free consulting for which you have not acknowledged with our Tek-Tips currency, Purple Stars.

What are your thoughts? What should we do with you?

 

[BJCooperIT]

If Volcano's mouse refuses to click on Thank SantaMufasa
for this valuable post!, mine has no problem.

SantaMufasa, have one on me for your valiant effort!

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

BJ,

Thanks to you and your mouse...very thoughtful.

DeepDiver,

Thanks for recognising the effort, as well.

Volcano,

Best regards.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[volcano]

Hi, vote is made. Thanks for reminding me this feature. I just know this thanking action besides replying a thankyou here.