Link to home
Start Free TrialLog in
Avatar of mphillip85
mphillip85Flag for United States of America

asked on

retrieve code from stored procedures in oracle since original compile scripts are lost.

I have loaded oracle 11g stored procedures scripts that were modified.  How do I retrieve the code from the compiled scripts so I can modify them again.  

I am not that familiar with oracle to know where to look.

Thank you.  I have an extreme deadline I need to meet, and this would help me out greatly.
Avatar of Milleniumaire
Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

If you haven't got the original scripts used to create the stored procedures then you can retrieve the stored procedure code from the dictionary views USER_SOURCE, ALL_SOURCE or DBA_SOURCE.

e.g.
select text
from all_source
where owner = 'JOEBLOGGS'
and     name = 'MYPROCEDURE'
order by line
Alternatively, if you have a tool like Toad or SQL Navigator, you will be able to view the procedures stored in the database and save the code to a file.
Avatar of mphillip85

ASKER

I have SQL Tools, does that help me?
I've never used SQL Tools, however, looking at the website (http://www.sqltools.net/) it does appear to be displaying a screen shot which shows the code for a stored package, so it looks like you should also be able to view stored procedures.

I would guess there is an icon or something which lets you navigate to a database scheme and view the objects e.g. tables, views, procedures, packages etc.  If so then you should be able to view the code behind these objects.
According to the tutorial I found on the SQL Tools website, "Press Alt+3 when the cursor is inside the editor" and the object browser is displayed.  Click on the procedures tab and you should then be able to view any of the listed procedures.
I see the procedures, but I do not see the code behind it.  I need to save the code to a pls file or open it or copy it.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Milleniumaire
Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial