Tuesday, August 30, 2011

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>

No comments: