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
Have a fun 🙂
Tomasz
very useful information, thank you
Regards,
Ramkishan N
Pingback: how to find a login name from source code - datahowinfo
Hi Thanks putting this together! I wonder what AI will do in this area over the next five years.