Finding source code in Oracle

Finding source code written by a developer is typical task executed by a DBA or a developer.

Generally there are two basic methods to get source code:

• select code from dictionary views – quick, simple, can return part of code, complicated to generate properly DDL
• generate code using DBMS_METADATA package – very powerful, returns full code, easy to generate DDL code

Prepare test objects

Two show both methods first I need to create some objects

table TEST_TBL

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 NUMBER,
  id3 NUMBER,
  id4 NUMBER
);

index TEST_TBL_IDX

CREATE INDEX test_tbl_idx ON test_tbl(id1);

view TEST_TBL_VW

CREATE OR REPLACE VIEW test_tbl_vw
AS
SELECT * FROM test_tbl;

materialized view TEST_TBL_MV

CREATE MATERIALIZED VIEW test_tbl_mv
AS
SELECT * FROM test_tbl;

trigger TEST_TBL_BD_TRG

CREATE OR REPLACE TRIGGER test_tbl_bd_trg
BEFORE DELETE ON test_tbl
BEGIN
  NULL;
END;
/

procedure TEST_TBL_PRC

CREATE OR REPLACE PROCEDURE test_tbl_prc
AS
  l_cnt NUMBER;
BEGIN
  SELECT count(*)
    INTO l_cnt
    FROM test_tbl;
END;
/

Manual from dictionary

This method is very popular to get quick look on source code of interesting us object. The most benefit is you can get only part of code a few interesting lines to analyze. It’s especially useful when Oracle raises errors and return owner, name and line where error occurred in your PL/SQL code.

Finding code for

• FUNCTION
• JAVA SOURCE
• LIBRARY
• PACKAGE
• PACKAGE BODY
• PROCEDURE
• TRIGGER
• TYPE
• TYPE BODY

can be done by following select. Interesting option here is possibility to limit code by LINE

SELECT type, line, text
  FROM dba_source
 WHERE owner=USER 
   AND name='TEST_TBL_PRC'
ORDER BY line;

TYPE       LINE TEXT
---------- ---- --------------------------
PROCEDURE    1    PROCEDURE test_tbl_prc
PROCEDURE    2    AS
PROCEDURE    3      l_cnt NUMBER;
PROCEDURE    4    BEGIN
PROCEDURE    5      SELECT count(*)
PROCEDURE    6        INTO l_cnt
PROCEDURE    7        FROM test_tbl;
PROCEDURE    8    END;

Finding view code

SELECT view_name, text 
  FROM dba_views
WHERE owner=USER
  AND view_name='TEST_TBL_VW';

VIEW_NAME    TEXT
------------ -------------------------------
TEST_TBL_VW  SELECT "ID1","ID2","ID3","ID4" 
               FROM test_tbl

finding materialized view code

SELECT mview_name, query
  FROM dba_mviews
  WHERE owner=USER
    AND mview_name='TEST_TBL_MV';

MVIEW_NAME   QUERY
------------ -----------------------
TEST_TBL_MV  SELECT * FROM test_tbl

another quite popular method to find trigger body code

SELECT trigger_name, trigger_body 
  FROM dba_triggers
WHERE owner=USER
  AND trigger_name='TEST_TBL_BD_TRG';

TRIGGER_NAME     TRIGGER_BODY 
---------------- -------------
TEST_TBL_BD_TRG  BEGIN
                   NULL;
                 END;

It’s not good method to find full DDL definitions for objects like TABLES, INDEXES etc.

DBMS_METADATA

It’s very advanced package used to generate full code. Always should be used to generate full code for DDLs.

Some examples how to generate detailed DDLs

Table

SELECT dbms_metadata.get_ddl('TABLE', 'TEST_TBL')
  FROM dual;

CREATE TABLE "TOMASZ"."TEST_TBL" 
(    
  "ID1" NUMBER, 
    "ID2" NUMBER, 
    "ID3" NUMBER, 
    "ID4" NUMBER
) 
SEGMENT CREATION DEFERRED 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
TABLESPACE "USERS";

Index

SELECT dbms_metadata.get_ddl('INDEX', 'TEST_TBL_IDX')
  FROM dual;
  
CREATE INDEX "TOMASZ"."TEST_TBL_IDX" ON "TOMASZ"."TEST_TBL" ("ID1") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
TABLESPACE "USERS";

View

SELECT dbms_metadata.get_ddl('VIEW', 'TEST_TBL_VW')
  FROM dual;

CREATE OR REPLACE FORCE EDITIONABLE VIEW 
"TOMASZ"."TEST_TBL_VW" ("ID1", "ID2", "ID3", "ID4") AS 
SELECT "ID1","ID2","ID3","ID4" FROM test_tbl

Materialized view

SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'TEST_TBL_MV')
  FROM dual; 

CREATE MATERIALIZED VIEW "TOMASZ"."TEST_TBL_MV" 
("ID1", "ID2", "ID3", "ID4")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
TABLESPACE "USERS" 
BUILD IMMEDIATE
USING INDEX 
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM test_tbl

Trigger

SELECT dbms_metadata.get_ddl('TRIGGER', 'TEST_TBL_BD_TRG')
  FROM dual;

CREATE OR REPLACE EDITIONABLE TRIGGER "TOMASZ"."TEST_TBL_BD_TRG" 
BEFORE DELETE ON TEST_TBL 
BEGIN
  NULL;
END;

ALTER TRIGGER "TOMASZ"."TEST_TBL_BD_TRG" ENABLE;

Procedure

SELECT dbms_metadata.get_ddl('PROCEDURE', 'TEST_TBL_PRC')
  FROM dual;

CREATE OR REPLACE EDITIONABLE PROCEDURE "TOMASZ"."TEST_TBL_PRC" 
AS
  l_cnt NUMBER;
BEGIN
  SELECT count(*)
    INTO l_cnt
    FROM test_tbl;
END;

huge advantage over manual method are extra options included in the package like:

• transformations of attributes like owner, tablespace name, storage etc
• possibility to generate code as XML
• it’s main method used by export and import tools expdp, impdp
• support for all objects in the database