dbms_profiler for code coverage

Prerequisite:
1. dbms_profiler is installed
2. current user has execute privilege on dbms_profiler
select, insert, update, delete privileges on
plsql_profiler_data
plsql_profiler_units
plsql_profiler_runs
3. synonyms (private or public (preferred) created

Steps:

1. exec dbms_profiler.start_profiler(run_comment IN VARCHAR2 := SYSDATE,
run_comment1 IN VARCHAR2 := '',
run_number OUT BINARY_INTEGER);

2. run the application

3. exec dbms_profiler.stop_profiler

Run this script to get code coverage:
Note: set value for :owner
:runid (this is the out variable in Step 1)

SELECT EXEC.unit_name, -- code coverage by package
ROUND (EXEC.cnt/total.cnt * 100, 1) "Code% coverage"
FROM (SELECT u.unit_name,
COUNT(1) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = :runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_owner = UPPER(:owner)
GROUP BY u.unit_name) total,
(SELECT u.unit_name,
COUNT(1) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = :runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_owner = UPPER(:owner)
AND d.total_occur > 0
GROUP BY u.unit_name) EXEC
WHERE EXEC.unit_name = total.unit_name

Links

• Subscribe to this feed

ADF UIX Component Guide

Get ADF UIX Component Guide if required.

Links

• Subscribe to this feed

text in all_source ends with chr(10)

Did you know that all_source.text ends with chr(10) ?

if you want to search for /*single line comment*/ in all_source,
this will not work:

select *
from all_source
where ltrim(text) like '/*%*/'

this will work

select *
from all_source
where ltrim(text) like '/*%*/' || chr(10)

Links

• Subscribe to this feed

Adopting an MVC approach to Oracle Development

MVC is very popular in Java for separating the model from the view from the controller.
Its advantage is that it segments an application to distinct components - Model, View, Component.

We can adopt this segmented approach for pl/sql development.

Let's take an example that we want to build an application that will compare 2 tables for differences and populate a table for the differences.

Let's say the tables have these common columns
k1, k2, a1, a2

Step 1 - The V

Build a view based on a select statement that will identify the difference.

In our example the sql might look like this:

select k1, k2, a1, a2
from table1
minus
select k1, k2, a1, a2
from table2

We build a view for the above sql statement.
Let's call the view difference_v.
The view is also encapsulating our business rule for what differences are.

Step 2 - the business work

A package that uses the view
with pseudo logic like this:

for each row in difference_v
insert row info into difference_table
next

What have we achieved so far?
We have :
1. segmented the difference business rule in the difference_v
if the business rule changes, we just change the view creation select statement

2. segmented the 'what to do with difference' in the package in Step 2

Using this segmented approach, we can start with step 1 with certain assumptions which can be revised later and move on to Step 2.

Links

• Subscribe to this feed

dbms_profiler

How to use dbms_profiler

use dbms_profiler to :
1. search out performance bottlenecks
2. analyse code coverage

Links

• Subscribe to this feed

7 Rules for Project Progress (moving forward)

1. Untested code isn't done.
2. Unreleased code isn't done.
3. Undocumented code isn't done.
4. Set many, small, specific goals.
5. Do something every day. Finish something every week.
6. One feature finished is better than ten features planned.
7. Simplicity gives you more options than complexity.

Links

• Subscribe to this feed