Ora | dba_source - Extended

dba_source - Extended

This utility extends the default DBA_SOURCE view and gives the below additional details as separate columns.

1. TEXT column for uncommented code
2. name of the procedure, function and cursor name defined within a package/procedure/function.
3. sub procedure / function name i.e procedures and functions defined within a another procedure/function.

Download the Source Code

1. TYPE  - typ_uc_dba_source_rec.sql
2. TYPE - typ_uc_dba_source_tab.sql
3. FUNCTION - uc_dba_source_fun.sql
4. VIEW - uc_dba_source_v.sql

Installation Steps

1. Download the sqls from the above links.
2. Run them in the same order.

Usage Notes

  select * 
    from uc_dba_source_v
   where owner = '[schema name]'
     and object_name = '[procedure/function/package name]'

Example

Here is the source code of the package body, taken for our example

CREATE OR REPLACE PACKAGE BODY VSUBR.my_test IS
/* this package is the test package to show how the DBA_source -extention works. 
   This is just a dummy code to show how the utility works. 
   please dont look intothe logic inside the code
*/

FUNCTION fun_test( p_param1 varchar2,
                    p_param2 varchar2,
                    p_param3 varchar2)
RETURN NUMBER IS

  -- var number;        
  var_new number;

  CURSOR c1 IS
      select 'x' col1, /*'y' col2,*/ 'z' col3
        from dual;
  /*
  procedure sub_prc_test1( sp_param1 varchar2) IS
  BEGIN
    -- not planning to do anything here..just a show
    null;
  END;
  */
  procedure sub_prc_test1( sp_param1 varchar2) IS
  BEGIN
    -- not planning to do anything here..just a show
    null;
  END sub_prc_test1;
BEGIN
  sub_prc_test1( 'a');
  var_new :=2;
  --var_new :=3;
  var_new :=4;
  return var_new;    
END;

procedure prc_test( p_param1 varchar2,
                    p_param2 varchar2,
                    p_param3 varchar2) IS
BEGIN
  null;
END;

END;
/

Now lets use our DBA_SOURCE_Extension utility

select *
  from vsubr.uc_dba_source_v
 where owner       = '[owner name]'
   and object_name = 'MY_TEST'
   and object_type = 'PACKAGE BODY'

and here is the result.

Limitations

The known limitation of this function is that - This query works effectively when it is filtered for a specific owner and object_name. It will work even without the filter condition, but it will be very slow.

Solution-1: One option is, try to run this script for all your source code in your DB during the night and populate that information to a table. so during the day you can use this table to all your querying purposes. But the limitation of this solution is that, if a source is changed and released during the day, then the query result will not be in sync with the actual source

Solution-2: You can make a hybrid view i.e check if the source was changed during the day( using the DDL_DATE_TIME) and if yes, then call the UC function, if not return the data from the pre-populated table ( table populated in solution-1)

If you find any issues with this util, please let me know by leaving a comment and I will try to fix that for you.