CODEWITHSUNDEEP
oracle-databaseplsql
Vallabh Patade
Vallabh Patade

Reputation: 5110

How to see PL/SQL Stored Function body in Oracle

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

Upvotes: 36

Views: 211387

Answers (3)

DazzaL
DazzaL

Reputation: 21973

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;

Upvotes: 55

user330315
user330315

Reputation: 

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

alternatively:

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

Upvotes: 14

Frank Schmitt
Frank Schmitt

Reputation: 30775

You can also use DBMS_METADATA:

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

Upvotes: 2

Related Questions