show the sql for a procedure 3

[maswien]

Can anybody tell me how to sghow the code in a stored procedure?


Thanks

 

[meridian99]

for viewing a stored procedure or function, I use Oracle DBStudio software utility that should reside on the oracle server.

 

[nagornyi]

Using simple means, you may just query the DB:

select text from all_source
where name = [b]your_procedure_name[/b]

 

[maswien]

Thanks for the help, I don't know how to use DBStudio software utility, I think nagornyi's solution is good enough for me.

Thanks

 

[KenCunningham]

nagornyi - you must have been reading my mind, as I had exactly the same question yesterday, but didn't bother to post it!! Thanks.

 

[SantaMufasa]

Just in case anyone is having trouble getting results from Nagornyi's correct concept, here is a clarification of his code:

select text from all_source
where owner = upper('<owner_name_here>')
  and name = upper('<your_procedure_name_here>')
order by line;

Since you are using "all_source", there is a distinct possiblity that more than one owner could have the same procedure name (unless you instead refer to "USER_SOURCE", which will automatically limit objects to "yours" only).

Also, since "*_source" uses multiple lines to disclose the content of a procedure, you should "order by line" so it doesn't result in scrambled lines of code.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:26 (10Aug04) UTC (aka "GMT" and "Zulu"), 10:26 (10Aug04) Mountain Time)

 

[maswien]

but why you use function upper()? why Oracle change the procedure name to upper case when it insert all_source table?

Thanks

 

[SantaMufasa]

MJia,

Oracle's default behaviour is to store in the data dictionary all user-defined names in upper case. So, if you say:

CREATE procedure mjia ...

...Oracle stores "MJIA" as the name of the procedure.

Oracle allows you to override the default behavior and store any case you wish in the data dictionary by your using double quotes around the names you choose. But Oracle highly discourages this behaviour:

CREATE procedure "MjIa" ...

...but every reference you make to this procedure must be in the form:

begin
    "MjIa" (<your arguments here)
end;

So this is why Oracle discourages overriding the default and it also explains why any comparisons you make to data dictionary objects must be in upper case (if you use Oracle default behaviour) or in "exact-case" if you override the default behaviour.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:18 (10Aug04) UTC (aka "GMT" and "Zulu"), 12:18 (10Aug04) Mountain Time)

 

[maswien]

Thanks, that makes a lot sense.

 

[nagornyi]

Thanks from me also. Always good to learn something new.