DBA_SOURCE

Tested on an Oracle 9 database. Imagine you have a function call which returns a value as follows:

SQL> select sys.login_user from dual;

 

LOGIN_USER

--------------------------------------------------

ORACLE

 

SQL>
  

You can see its source code stored in DBA_SOURCE:

SQL> desc dba_source

Name                    Null?    Type

----------------------- -------- ----------------

OWNER                            VARCHAR2(30)

NAME                             VARCHAR2(30)

TYPE                             VARCHAR2(12)

LINE                             NUMBER

TEXT                             VARCHAR2(4000)

 

SQL> l

  1  select text from dba_source

  2  where owner = 'SYS'

  3  and name = 'LOGIN_USER'

  4* order by line

SQL> /
  

TEXT

--------------------------------------------------

function login_user return varchar2 is

begin

return dbms_standard.login_user;

end;

 

SQL>

The TYPE column shows what type of object the source came from. There are several possibilities:
  

SQL> select distinct type from dba_source;

 

TYPE

------------

FUNCTION

JAVA SOURCE

PACKAGE

PACKAGE BODY

PROCEDURE

TRIGGER

TYPE

TYPE BODY

 

8 rows selected.

 

SQL>
  

In the example above, the source came from a function, which you might have guessed as it returns a value to the user:
  

SQL> l

  1  select distinct type from dba_source

  2  where owner = 'SYS'

  3* and name = 'LOGIN_USER'

SQL> /

 

TYPE

------------

FUNCTION

 

SQL>