How to View Oracle Stored Procedure using SQLPlus?

Question

How can I view the code of a stored procedure using sqlplus for Oracle 10g?

When I type in:

desc daily_update; 

it shows me the parameter, but when I try to do the following:

select * from all_source where name = 'daily_update';

I get

no rows selected

What am I doing wrong?

Solution

check your casing, the name is typically stored in upper case

SELECT * FROM all_source WHERE name = 'DAILY_UPDATE' ORDER BY TYPE, LINE;

Comments

[vikingsteve]

Just a heads up, you might want to order by line. By default, the lines are correctly ordered for me without the order by in some quick testing, so I'm not sure if it is necessary or not

[Ludovic Kuty]

IMHO order by type is useless since we already provided the name of the object

[Harrison]

I actually put the ORDER BY TYPE (etc) there in case someone took the example and modified the WHERE clause (perhaps changing the = to a like etc); but you are correct, as an example I wanted it to work forward. I tend to like to always specify an ORDER BY in case things in the optimizer change in the future my queries will continue to operate as normal

[volkerk]

The ORDER BY TYPE Is relevant when displaying package code, as you want the package declaration to be listed before the package body.