Search the Site

Sponsors

bottom corner

Using DBA_SOURCE to query package source code

This article shows how we can use Oracle's DBA_SOURCE view to search through the source code of a package.

This page is filed under keyword(s): oracle.

Recently I had the need to find out exactly what existing PL/SQL logic was touching a certain field that seems to update by itself, wiping out important data. Below was the quick query I put together, using the DBA_SOURCE view, to hunt down the culprit.

select name as package_name, line, text
from dba_source
where owner='MY_SCHEMA'
and type='PACKAGE BODY'
and (
  upper(text) like '%MY_TABLE_NAME%FIELD_NAME%'
  or
  upper(text) like '%FIELD_NAME%MY_TABLE_NAME%'
)
order by name, line;

Note that I could have searched for "and upper(text) like '%UPDATE%MY_TABLE_NAME%FIELD_NAME%'" rather than having two conditions, but I wanted to err on the side of caution, thus my wish to pull more data out to be safe.

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 4 Dec 2014

bottom corner