SQL command to show code of stored procedure in Oracle

If you ain’t have a good Oracle tool, such as Toad, and you wish to view or print a stored procedure in the Oracle database, you can check the all_source table.

Such as while you’re left with the Oracle default sets of tool such as SQL Plus or SQL Worksheet, what you can do is to login to any of these tools, and then issue the following SQL command to show the complete code of the stored procedure that you want.

SELECT line FROM all_source WHERE name=’MY_STORED_PROC’ ORDER BY line

On your SQL Plus prompt, it’ll then display the many lines of code that make up the store procedure “MY_STORE_PROC”. Be sure, the name of the stored procedure must be all in big cap, that is how the Oracle works for all system objects, tables and views.

You can make use of the system all_source table to find out more info about other system objects within your Oracle DB. Do desc all_source to find out more what you can get from this system table.

Enter your email address to subscribe our newsletter or feed for FREE:

Delivered by FeedBurner

Bookmark with:

[Delicious]    [Digg]    [Reddit]    [Facebook]    [StumbleUpon]

0 Responses to “SQL command to show code of stored procedure in Oracle”

  1. No Comments

Leave a Reply

You must login to post a comment.