How to see PL/SQL Stored Function body in Oracle

Question

I have a stored function in Oracle database pAdCampaign.fGetAlgoGroupKey. How to see the code of this function.?

possible duplicate of [.NET: How to retrieve the body of an Oracle 9i PL/SQL procedure or function](http://stackoverflow.com/questions/554115/net-how-to-retrieve-the-body-of-an-oracle-9i-pl-sql-procedure-or-function) — APC, Jan 08 '13 at 12:59

score 53 · Accepted Answer · edited Dec 19 '19 at 16:37

53

If is a package then you can get the source for that with:

    select text from all_source where name = 'PADCAMPAIGN' 
    and type = 'PACKAGE BODY'
    order by line;

Oracle doesn't store the source for a sub-program separately, so you need to look through the package source for it.

Note: I've assumed you didn't use double-quotes when creating that package, but if you did , then use

    select text from all_source where name = 'pAdCampaign' 
    and type = 'PACKAGE BODY'
    order by line;

edited Dec 19 '19 at 16:37

Shivam Puri

answered Jan 08 '13 at 10:14

DazzaL

1

If you're selecting from ALL_SOURCE you should include the OWNER in the WHERE clause. – APC Jan 08 '13 at 12:52
2

for showing a function, the `type` you should use is `FUNCTION` – StampyCode Sep 23 '16 at 08:14

score 13 · Answer 2 · 2013-01-08T10:09:47.080

13

SELECT text 
FROM all_source
where name = 'FGETALGOGROUPKEY'
order by line

alternatively:

select dbms_metadata.get_ddl('FUNCTION', 'FGETALGOGROUPKEY')
from dual;

edited Jan 08 '13 at 10:09

answered Jan 08 '13 at 09:58

For you first answer query it's giving an error ORA-00904: "PROCEDURENAME": invalid identifier. – Vallabh Patade Jan 08 '13 at 10:08
It's returning null. But I am sure this function is there/exist. I am calling this function from Java code or from SQL code it's returning the value as expected. – Vallabh Patade Jan 08 '13 at 10:13
@VallabhPatade Maybe you created it using double quotes, and the name is now case-sensitive? Are you connected with the user that owns the function? – Jan 08 '13 at 10:21
@VallabhPatade: just saw that you are actually talking about a package function, not a standalone function (which you should have mentioned right away). See DazzaL's answer then. – Jan 08 '13 at 10:22
1

dbms_metadata.get_ddl only works for your own objects or for SYS, so the first solution sometimes returns text where the second doesn't. – Michael Grazebrook May 29 '18 at 17:08

score 2 · Answer 3 · answered Jan 08 '13 at 10:02

2

You can also use DBMS_METADATA:

select dbms_metadata.get_ddl('FUNCTION', 'FGETALGOGROUPKEY', 'PADCAMPAIGN') 
from dual

answered Jan 08 '13 at 10:02

Frank Schmitt

3 Answers3