Technical Books I have read in 2016

I have always enjoyed reading books about Programming. From books that lead you to take your first tentative steps with a new language to ones that take you on a deep dive into the world of particular feature. I especially enjoy ones that discuss language agnostic programming concepts such as debugging, estimating etc. Books like Code Complete, The Pragmatic Programmers, The Mythical Man Month and Don’t Make Me Think.

To me technical books are such a bargain. For £20 – £30 you can gain knowledge and insight that can make you so much better at your job, such as taking different approaches to solving the daily problems that we as programmers face. Without a doubt there is a lot of published rubbish out there but fortunately in these days of reviews and questions on the numerous Stack Exchange sites it is a lot easier to avoid the charlatans and their ammo pouches stuffed with silver bullets. Although as you will see from my own list, one or two may still slip through the net!

Here are the programming related books I have read this year, listed in the order that they were read.

cplayersThe C# Player’s Guide (2nd Edition)

This is my favourite book that I have read whilst learning C#. Immediately accessible. The large format of the book along with the lucid and easy to grasp descriptions of Object Orientated topics make this my recommended book to anyone that is interested in learning C#.

Django By Example djangobe

Unfortunately this book is still on the “bought but not read” pile. It is no reflection on the book I have been focusing my attention on learning C# this year.

C# 6.0 and the .NET 4.6cnet46 Framework

At 1600+ pages this was certainly the biggest technical book I bought this year. For me it is too unwieldy to use on a day to day basis so, for the first time I have abandoned the printed version of a book and have spent the last 8 months using the e-book. Usually the ebook is open on one monitor whilst Visual Studio is open in the other. Not sure if it’s such a good book for beginners but as a reference I can see myself returning to it to look things up.

The Psychology of Computer Programming: Silver Anniversary Editionpcp

I have been wanting to read this book for several years and finally got round to it. It is by a very long way my favourite read this year and it is in the top 5 all time technical books I have ever read. Although 45 years old, the ideas discussed then are still very relevant today; How we don’t read existing code to see how others have solved problems, the critical importance of having code reviews, egoless programming, estimating and setting expectations around delivery times. I could go on and on. If you haven’t read it, order it today you will not regret it. It will make you a better programmer or manager!

learnciadLearn C# in One Day and learn it well

The worse book I read this year. I have already written what I think of it here.  Not much more to add so moving on to the final book…..

Working Effectively With Legacy Code wewlc

The final book for this year is another classic and I have high expectations for it. Currently I am a third of a way through but I will have finished it by the end of the year. At this point I think it should be called “Working Effectively with Legacy Object Oriented Code” because a lot of the ideas in the book code are centred around legacy Object Oriented code. I will update this once I get to the end of the book.

Summary

This year marks a slight change from previous year lists in that I haven’t read any Oracle database or Application Express books. There are two reasons for this. First I don’t think there have been any unmissable Oracle books published this year (I am interested in Real World SQL and PL/SQL that was published in September 2016 however I awaiting reviews or to actually have a look through it) –  and secondly most of my spare time has been spent learning C#.

I have taken something from each of these five books this year, yes even Learn C# in a day. I know that as a result of reading these books, I will start 2017 a better programmer.

An introduction to Web scraping using Python 3

In this article I will demonstrate how easy it is to perform basic text Web scraping using Python and just a few lines of code.

The example have been developed and tested using Python  3.5.2.

The first step is to see if you have the following third party libraries already installed; Requests and Beautiful Soup 4. So start idle and try typing the following command:


import requests

After you press return, if you see no error messages then requests is installed. If you see an error message that shows requests has not been found, you should install it using pip from the command line as shown below.


pip install requests

Repeat the process to see if you already have the Beautiful Soup library installed, fortunately you don’t have too much to type….


import bs4

Again if Python complains that it can’t find the library, use pip from the command line to install it.


pip install beautifulsoup4

With the libraries installed, here is a program that scrapes this site. It returns the titles from the blog posts that are shown on this page.

To demonstrate how this is achieved with just a few lines of code, here is the program without comments:


import requests, bs4

def getTitlesFromMySite(url):

 res = requests.get(url)
 res.raise_for_status()

 soup = bs4.BeautifulSoup(res.text, 'html.parser')
 elems = soup.select('.entry-title')
 
 return elems


titles = getTitlesFromMySite('http://www.oraclefrontovik.com')

for title in titles:
 print(title.text)

Now the same code but this time with each section commented…


# import requests (for downloading web pages) and beautiful soup (for parsing html) 
import requests, bs4

# create a function that allows a parameter containing a url to be passed into it
def getTitlesFromMySite(url):

# download the webpage and store it in res variable
res = requests.get(url)
# check for problems - if there are, raise_for_status() raises an exception
# and the program stops at this point
res.raise_for_status()

# running the downloaded webpage through Beautiful Soup returns a
# Beautiful Soup object which represents the HTML as a nested data structure.
soup = bs4.BeautifulSoup(res.text, 'html.parser')

# store in an array the items that match this css selector. 
# I will explain how I obtained this entry below
elems = soup.select('.entry-title')

return elems

# call the function and store the results in titles
titles = getTitlesFromMySite('http://www.oraclefrontovik.com')

# loop through the array printing out the title.
for title in titles:
print(title.text)

Running the example returns the following expected output….


Learn C# in One Day and Learn It Well – Review

Contributing to an Open Source Project

A step by step guide to building a Raspberry Pi Hedgehog camera

Is there more than one reason to use PL/SQL WHERE CURRENT OF ?

Structured Basis Testing

Raspberry Pi connected to WiFi but no internet access

The auditing capabilities of Flashback Data Archive in Oracle 12c.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and the perils of the RAISE statement

Using INSERT ALL with related tables

The best lesson I learnt from Steve McConnell

To summarise, the code imports two third party libraries, requests and Beautiful Soup 4, that perform the lions share of the work. In the example I use the requests library to download a web page as HTML and then pass it to Beautiful Soup along with a CSS selector to return the information I want from it.

Obtaining the CSS selector

The code example has the following line which extracts the part of the webpage, the blog post titles, that we are interested in:

elems = soup.select('.entry-title')

Using Firefox, I obtained the CSS Selector ‘.entry-title’ by:

  1. Navigate to the page of interest, in this case, oraclefrontovik.com
  2. Opened Firefox developer tools (Ctrl + Shift + I)
  3. Highlighted the first title (which at the time of writing was Learn C# in One Day and Learn it Well – Review) , right click and select Inspect Element
  4. In the console, I then right click and select Copy and then choose CSS Selector from the sub menu.

At the time of writing, I was unable to get the same CSS Selector using the native developer tools from Chrome. If you know of a way please let me know in the comments.

Summary

In this post I have walked through the steps to perform basic text Web scraping using Python 3.

Learn C# in One Day and Learn It Well – Review

I have been learning C# and the .NET framework for a while now and have been working my way through several books; The C# Programming Yellow Book, The C# Player’s Guide (2nd Edition) and C# 6.0 and the .NET 4.6 Framework All of these books have helped me to varying degrees to get comfortable in Object Oriented programming, the C# language and the .NET framework.

When learning a new programming language, I always look to improve my knowledge of the fundamentals, so seeing an introduction to C# book that was getting good reviews piqued my interest. That book was Learn C# in One Day and Learn It Well although I am very suspicious of Learn X in Y days\hours\minutes titles (see Peter Novig masterly description) I ordered a copy.

learncinaday

 

 

 

 

 

 

 

At 153 pages the book is slim and can be divided into two parts. Chapters 1 through 11 cover the various building blocks that make up a programming language such as variables, arrays, condition statements as well as briefly touching on Object Orientated concepts. The second half of the book, starting on page 128 brings together what you have learnt in a project by building a Payroll programme.

I think the book is self published, obviously not an issue in itself however I felt that it could have done with a review\editor to catch the typos and misaligned paragraphs but these are minor irritants. The real point of this post is, can you use this book to learn C# in a Day?

In my opinion no. The main problem with this book is how brief the topics are covered. Take for example Interfaces which are discussed on pages 107 – 109. The text compares Interfaces with Abstract classes, however no where in these two pages does it tell you what an interface actually is and why you would want to create one.

In summary I am not sure who the target audience for this book is. Perhaps someone that just needs to get some course work or module “working” For everyone else it is far to brief and does not go into enough detail especially explaining why you would want to use a feature of the language. If you are interested in learning C# my advice would be to put the £8 towards a better resource.

 

 

Contributing to an Open Source Project

I have been interested in Git, the distributed version control software since reading the first edition of Scott Chacon’s Git book way back in 2010. However outside of my own projects, my real world experience of using Git is relatively limited and it’s one of the skills I never seem to get around to improving on.

To change this, I have recently contributed to an open source project hosted on GitHub. The change I made can be found here and this post is my recollection of the process to help me and hopefully others just getting started with the GitHub workflow.

For a comprehensive guide to the GitHub workflow, I recommend reading Chapter 6 of the Git book.

Find a project that you want to contribute to.

Probably the most tricky step – there are so many projects how do you find one to contribute to? In my case I started with a project that I know and use. OraOpenSource/Logger which is a great tool for instrumenting Oracle PL/SQL code.

githubissues

 

From there it’s a quick scan of the open issues. I picked one related to the documentation because I wanted to focus on the GitHub workflow. The challenging technical issues and enhancements will still be there once I have got up to speed with the GitHub way of working.

Once you have found a project, it is unlikely that you will be able to push your changes to it, so the next step is to fork it. This gives you a copy of the project within your users namespace which you can then make changes to.

Make the change

With the project forked, you can go ahead, create a topic branch and make the necessary changes to the files and once you are happy with them, push them back to your copy of the project.

Pull request and …..Oops!

When you are ready to contribute your changes back to the original project you need to create a pull request. Creating a pull request opens up a discussion thread with a code review focusing on your proposed change.

Don’t worry if the change is discussed or rejected. Dust yourself down and go again. I had my own oops moment with my first pull request as I had changed a URL from relative to absolute. Not a problem so I closed the initial pull request and created another which has now been accepted and merged into the project.

Make the world a better place

Apologies the for the heading, I have been enjoying Silicon Valley around the time this post was taking shape.  If not the world, your change no matter how small will make the project you are contributing to better and it gives you a public artefact that you can point to.

Summary

In this article I had written about my Git experience along with my first contribution to an open source project.

 

A step by step guide to building a Raspberry Pi Hedgehog camera

 

What the camera was designed to do! Photograph of a Hedgehog in the garden

I had been looking for an Raspberry Pi project for a long time, the various media player and game emulators didn’t interest me enough besides I wanted something that the whole family could get involved in.  It was only through discussing this at work that someone mentioned they had built a Hedgehog camera with a Raspberry Pi. Awesome! That was exactly the project I was looking for.

So over the course of several nights I built a prototype and was genuinely surprised at a) how much fun I had putting it together, b) that it actually worked and finally c) how much the whole family has got involved.

One thing I found pretty much straight away was although there is plenty of information on the web to build one of these devices it is not all in one place and I found myself going from one site to another so this post is my attempt to bring all the information and what I learnt whilst building it into one place. I will of course acknowledge which sites helped.

Before going any further here is what my device looks like.

Ugly but effective! The completed Hedgehog camera

 

Granted, it won’t win any design awards. You can spend more time making it look far more aesthetically pleasing if you so wish.

What you will need and how much it will cost

A Raspberry PI (I am using the Raspberry Pi 3 and bought this starter kit)£41.50
A Raspberry PI Case. Unfortunately the official case that comes with the kit above requires you to remove the top part in order to fit the camera and as the PI will be spending a lot of it’s time outside I wanted to protect it as much as I could so I bought this case which has a slot for the camera ribbon£4.50
Waveshare Raspberry Pi Camera Night Vision Camera Module Kit£23.99
An air tight storage box.From 99p
A drill bit to make a hole in the storage box for the camera. After managing to destroy several boxes by using the wrong tools, I ordered this drill bit set which made the task a breeze.£7.95
A Battery Pack. I use this one but at the time of this post the cost has gone up considerably since I bought mine. You just need one that has a large capacity, the one I am using has an advertised capacity of 20100mAh and comfortably powers the camera all night.Around £25 - £30
Total:£109.44

Setting up the Raspberry Pi

If you are using an existing Raspberry PI and its software is up to date , then you can skip this step.  Out of the box my Raspberry PI required various updates. Using the advice from here  I ran

sudo apt-get update

to update the package list followed by

sudo apt-get dist-upgrade

to upgrade all packages to their latest version.

If you are going to use the default user, Pi remember to change the password!

Fitting the camera and installing the camera software

It is important to note that the Waveshare camera is an all in one device. By which I mean it comes with everything needed to take pictures at night time. You don’t need extra infra red led’s or motion sensors. I should also point out that the camera takes stills and not videos. What you can do using the software that I mention below is to “stitch” all the pictures taken in one night together to create a time lapse MP4 file that you can watch, upload to YouTube etc.

First you need to fit the camera to the Raspberry Pi. This video is an excellent tutorial, even though the camera module in the video is different, the installation is exactly the same.

With the camera now fitted you need to install software to control the camera and when pictures are taken. I chose pi-timolo.  This excellent piece of software can be configured to take photo’s whenever motion is detected. The installation is straight forward and as my Raspberry Pi is only being used for the Hedgehog camera I also followed the instructions to run on boot. Which, in case of need means that when the Raspberry Pi starts up, the camera starts capturing images once it detects motion.

The default location for the photos are:

/home/<<username>>/pi-timolo/motion

where <<username>> is the name of the user that installed pi-timolo.

At this point, we now have a Raspberry Pi camera that will take a photograph whenever it detects motion which means that the number of photographs taken every night will be measured in the hundreds. Sifting through these every day will very quickly lose it’s appeal (if it ever had one) so the final software package we are going to install is FFmpeg. This will “stitch” all the photographs for a day (or folder) into one MP4 file. Playing the MP4 file will give you a time lapse like experience. You can download FFmpeg from here. I will show you how to use FFmpeg in the Reviewing the Results section.

Fitting the PI and camera into the air tight container

Overview of the components within the storage box

Firstly make a guide in the middle of the airtight container. This should be at one end of container. Then using the drill bit I have suggested or another tool drill\cut a circle so that the camera can be fitted. You do not need to cut similar holes for the infra red sensors.  Then fix the camera to the inside of the container. I have used parcel tape(!) to secure mine. Not elegant but it works.

If you have not done so, now is the time to connect the camera to the Raspberry Pi.

The final step is to connect the battery to the Raspberry Pi, I normally do this just before I place the container in the garden.

The parcel tape around the outside of the box is to cut down the amount of light that is visible from within the box.

Where to place in the garden

The obvious answer is to place the camera in the area where you have seen Hedgehogs or their poo! However the key is to experiment so try various places around your garden.

Reviewing the Results

After collecting the container the following morning,  my workflow for reviewing the results is to first power down the Raspberry Pi (it is still using the battery at this point) and then connect it to the mains and restart it.

After navigating to

/home/&lt;&lt;username&gt;&gt;/pi-timolo/motion

I copy the photos to another folder within my home directory called

/home/&lt;&lt;username&gt;&gt;/garden/&lt;&lt;date_of_photos&gt;&gt;

and using the following FFmpeg command

ffmpeg -f image2 -pattern_type glob -i '*.jpg' 20160821.mp4

which creates an MP4 file from all the jpg files within the directory where this command was run.

After a few minutes the process will be complete and you will be able to play the MP4 file using your favourite player. When viewing on the Raspberry Pi I use Omxplayer  or other devices I use VLC.

Optional Extras

One optional extra that springs to mind is to add a WiFi extender dongle (if your garden is large enough) this will enable you to connect to the device and see what is happening in real time.

If you think of other optional extras please let me know via the comments.

Summary

In this article I have gone through the steps required to create a Hedgehog camera using a Raspberry Pi.

This write up has been made a couple of weeks after I had built my own version, so if I have missed something or I haven’t made a particular point clear enough please let me know in the comments.

Acknowledgements

My gratitude for a large part of this work, in particular the genius of using the air tight container goes to http://www.sconemad.com/blog/hedgeycam/

 

 

Is there more than one reason to use PL/SQL WHERE CURRENT OF ?

I can’t remember using the PL/SQL construct where current of  in code I have written so along with my usual article style of how use to use this feature, this is also an experiment to find out if there are any other reasons to use where current of.

The code examples in this post have been developed using the Oracle Database App Development VM for Oracle VirtualBox which at the time of this post was using Oracle database 12.1.0.2. (Although nothing in this post is 12c specific)

First I create a table that can be used for the experiment


create table t
as
select *
from all_objects
/

With the table created, I then use it in the following example which I will explain below.  At this point I would like to say that I appreciate the example could be rewritten as a single SQL statement however I wanted to concentrate on illustrating the use of where current of  without an overtly complicated example where the point of this post is lost in a forest of code.


declare

  cursor c 
  is 
  select t.object_name
    from t
   where t.object_type in ('PACKAGE', 'PACKAGE BODY')
     and t.owner = 'APEX_040200'
     for update of object_name; 

  ln_count pls_integer := 1;

begin

  for i in c
  loop
    
    update t
       set t.object_name = 'X - ' || ln_count
     where current of c;
 
    ln_count := ln_count + 1;
 
  end loop;

  commit;  

end;

At line 3 I declare a cursor that identifies the records of interest, which are the package specifications and bodies owned by the APEX user.  Note the use of the for update clause. This is mandatory when using where current of . If you remove it and try to run the example you will see the following error message

PLS-00404: cursor 'C' must be declared with FOR UPDATE to use with CURRENT OF

Moving on to the execution section,  for each iteration of the cursor for loop,  the object name is updated (to something not very useful!) and you can see at line 20 what I think is the primary benefit of using where current of is that instead of repeating the where clause used by the cursor I have used where current of c. So when the cursor has to change to return different records the only change that needs to be made is to the cursor.

The final part of the example is once the loop completes I release the lock on the table by committing the changes.

The primary benefit of where current of is the reduction in duplicated code which is a good thing but am I missing anything else? Please use the comments to let me know.

Summary

In this post I have demonstrated how to use the PL/SQL construct where current of along with what I believe is it’s primary benefit, the reduction of duplicated code.

Acknowledgements

The inspiration for this post came from Steven Feuerstein’s book, Oracle PL/SQL Programming  5th Edition.

 

Structured Basis Testing

Re-reading Code Complete 2 recently, I came across the concept of Structured Basis Testing which despite its foreboding name is in fact an easy to understand and straight forward method that you can use to calculate the minimum number of unit test required to exercise every path through a function or procedure.

Structured Basis testing differs from Code Coverage testing or Logic Coverage because Structured Basis testing gives you the minimum number of test cases you need to exercise every path whereas when using Code Coverage testing or Logic Coverage testing you could end up with many more test cases than you would need to cover the same logic with Structured Basis Testing.

You calculate the minimum number of tests required by following these rules:

  1. Start with 1 for the straight path through the function or procedure
  2. Add 1 for each keyword or decision construct such as if, while, for, do, and, or,
  3. Add 1 for each case in a case statement.

Some Examples

Example 1

example120160726

 

 

 

 

 

 

 

 

 

 

In this first example, there are 2 unit tests required to exercise every path through the function. Number 1 is the straight path through the function, i.e a record is found by the select statement. Number 2 is the path taken when a record is not found.

Example 2

example220160726

 

 

 

 

 

 

 

 

 

 

 

In the second example there are 5 unit tests required to test every path through this procedure. Number 1 is again the straight path through the procedure. Numbers 2 – 5 are each of the cases in the case statement.

Example 3

example320160726

 

 

 

 

 

 

 

 

 

 

 

 

In this  final example, there are four unit tests required to test every path through this procedure. The first is the straight path through the routine, the bulk collect will not raise an exception if no records are found so there are no divergent paths at this point, the next unit test, number 2 exercises the for loop and then numbers 3 and 4 exercise the IF statement paths.

Summary

In this article I have explained what Structured Basis Testing is, how it differs from other code coverage testing and how to calculate the minimal number of unit tests required to exercise every path in your function or procedure.

Acknowledgements

The idea for this post  comes from the discussion on Structured Basis Testing in Code Complete 2 Pages 505 – 506

Raspberry Pi connected to WiFi but no internet access

A quick tip in case you run into the following problem as I did with a Raspberry Pi 3 running the Jessie version of Raspbian.

Problem

Unable to access the Internet using the Raspberry Pi’s built in WiFi.

Symptoms

  1. You can successfully connect to the WiFi network
  2. You are unable to access any Internet sites.
  3. Running the command route -n in a terminal shows a default gateway of 0.0.0.0
  4. Within the file /etc/network/interfaces you see the following:

    iface wlan0 inet manual
    wpa-conf /etc/wpa_supplicant/wpa_supplican.conf

Resolution

Using your favourite text open the file /etc/network/interfaces and find the following line:

iface wlan0 inet manual
wpa-conf /etc/wpa_supplicant/wpa_supplican.conf

Amend or comment out the line iface wlan0 inet manual and change it to:

iface wlan0 inet dhcp
wpa-conf /etc/wpa_supplicant/wpa_supplican.conf

The only change is shown in bold i.e manual has changed to dhcp. The screen shot below shows how the interfaces file looks with the original manual entry commented out and the corrected line with dhcp added:

interfaces file

Reboot your Raspberry Pi, run the command route -n and you should see a new line with the
gateway details once confirmed open your Browser and you should now be able to connect to the Internet.

What is DHCP?

The following is taken from here.

Dynamic Host Configuration Protocol (DHCP) is a network protocol that enables a server to automatically assign an IP address to a computer….

The auditing capabilities of Flashback Data Archive in Oracle 12c.

The goal of this post is to build a prototype that demonstrates the auditing capabilities of Flashback Data Archive in Oracle 12c.

Although the documentation mentions that Flashback Data Archive provides the ability to track and store transactional changes to a table I was struggling to see how this functionality actually worked and what audit information was available hence my need to build something that I could test drive.

This post is not a deep dive into Flashback Data Archive for that I would refer you to the landing page of Flashback Data Archive within the documentation.

The prototype was developed using the pre-built developer app vm for virtual box which at the time of writing used Oracle database version 12.1.0.2.

The goal of the demo is show how a table is audited for changes, the audit will show who changed the record and when the change was made.

Creating the prototype is split into the following steps:

  1. Create a new user (optional).
  2. Creation of a table that you wish to audit changes to.
  3. Creation of the Flashback Data Archive objects.
  4. Creation of a new context and the dependent package.
  5. Executing DML statements against the table of interest.
  6. Viewing the audit information.

Create a new user (optional).

Logging on as a user with SYSDBA role, create a new user called: fda_user


create user fda_user identified by fda_user;
alter user fda_user quota unlimited on users;

fda_user is then given the following grants:


grant create table to fda_user;
grant alter user to fda_user;
grant alter session to fda_user;
grant create session to fda_user;
grant create any context to fda_user;
grant create procedure to fda_user;

Creation of a table that you wish to audit changes to.

For this prototype I am using a copy of the employees table owned by the HR user. The command below was run using whilst logged in with the SYSDBA role.


create table fda_user.employees as select * from hr.employees;

Creation of the Flashback Data Archive objects.

Still logged in as a user with SYSDBA role create a new tablespace and allow the fda_user to utilise it.


create tablespace fda2 datafile 'FDA2.dbf' size 1M autoextend on next 1M;

alter user fda_user quota unlimited on fda2; 

The next step is to create the Flashback Data Archive. In this example the audit information will be kept for 1 year and then automatically purged.


create flashback archive fda_one_year_data tablespace fda2 quota 1G retention 1 year;

Next the fda_user is granted permissions in order to utilise Flashback Data Archive.


grant flashback archive on fda_one_year_data to fda_user;

grant execute on dbms_flashback_archive to fda_user;

In the file step with the sysdba role, the table that is to be audited, employees is pointed to the Flashback Data Archive that was created earlier.


alter table fda_user.employees flashback archive fda_one_year_data;

Creation of a new context and the dependent package.

Log in to the database as the fda_user create the following context and PL/SQL package spec and body. This will used to assign who made the changes to the Employees table what those changes where.


CREATE OR REPLACE CONTEXT employee_context USING employee_ctx_api;


CREATE OR REPLACE PACKAGE employee_ctx_api
IS
  PROCEDURE set_value (p_name  IN VARCHAR2,
                       p_value IN VARCHAR2);

END employee_ctx_api;
/

CREATE OR REPLACE PACKAGE BODY employee_ctx_api
IS
  PROCEDURE set_value (p_name IN VARCHAR2,
                       p_value IN VARCHAR2)
  IS
  BEGIN
    dbms_session.set_context('employee_context', p_name, p_value);
  END set_value;

END employee_ctx_api;
/

For the sake of keeping the example on track the context and dependent package has been created in the same schema but in a production environment both the context and package would live in a different schema.

With this step, the required set up and configuration of Flashback Data Archive is now complete.

Executing DML statements against the table of interest.

In the following statements, a call is made to dbms_session and the package used by our context to record who is making the change and the action they are undertaking. This is then followed by the insert, update or delete statement.

begin

  dbms_session.set_identifier('Ian');

  employee_ctx_api.set_value('action','Insert');

  insert into employees
  (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
  )
  values
  (
    9999,
    'William',
    'Bodie',
    'bodie@ci5.com',
    '01-123-456',
    TO_DATE('01-JAN-1978', 'DD-MON-YYYY'),
    'SH_CLERK',
    '20000',
    NULL,
    149,
    80
  );

  commit;

end;


begin
 
 dbms_session.set_identifier('Ian');
 
 employee_ctx_api.set_value('action','Update');

 update employees e
    set e.salary = 100009 
  where e.employee_id = 9999;
 
 commit;

end;
begin
 
 dbms_session.set_identifier('Ian');
 
 employee_ctx_api.set_value('action','Delete');

 delete from employees
 where employee_id = 9999;
 
 commit;

end;

Viewing the audit information

With committed changes to the Employees table the audit information can be viewed using a Flashback Table Query such as the following:


SELECT employee_id, 
       salary,
       versions_starttime,        
       versions_endtime,       
       versions_operation vo,       
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'employee_context','action') AS action
FROM   employees
       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24) AND SYSTIMESTAMP
WHERE  employee_id = 9999
ORDER BY versions_startscn;

Reviewing the query results shows a record being inserted, updated and finally deleted along with who did it (session_user and client_identifier columns), when it was done (versions_starttime column) and what was done (action column). Although this last column is not really useful as the information is already provided by the versions_operation (vo column) in the screenshot but is included as an example of what can be recorded.

fda_results

Summary

In this post I have shown how you can build a prototype to see if the Audit capabilities of Flashback Data Archive would work with your existing application.

Acknowledgements

The inspiration for this post came from two sources. First was Connor McDonald’s superb presentation on Flashback Data Archive at the 2015 UKOUG conference and Tim Hall’s fantastic and generous site

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and the perils of the RAISE statement

Since dbms_utility.format_error_backtrace has been available from Oracle 10g this isn’t another what it is\how to use it post. Instead I will be taking this opportunity to discuss a nuance of dbms_utility.format_error_backtrace that you may not be aware of.

dbms_utility.format_error_backtrace reports the call stack back to the exception or the last raise statement which if you are not aware of could catch you out.

A quick word on the examples that follow, I am not proposing you should use this functionality in its raw format as I have done here. I believe that for production code calls to dbms_utility.format_error_backtrace should be encapsulated within your exception handling package.

The following code creates 3 procedures. procedure a calls b and b calls c. Within procedure c an exception is raised. There is no intermediate exception handling and the exception passes back to procedure a which makes a call to dbms_utility.format_errror_backtrace.

create or replace procedure a
is
begin
 dbms_output.put_line('In procedure a');
 b(); 
exception 
 when no_data_found then
 dbms_output.put_line(dbms_utility.format_error_backtrace);
end a;
/

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
end b;
/

create or replace procedure c
is
begin
 dbms_output.put_line('In procedure c');
 RAISE NO_DATA_FOUND;
end c;
/

When procedure a is run, the following results are observed:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.C", line 5
ORA-06512: at "HR.B", line 5
ORA-06512: at "HR.A", line 5

The error stack shows the exception started on line 5 (line 24) . Excellent, when things go wrong we can track the problem down to the line number! However procedure b needs to change and the developer, Gary Careless makes the required enhancements, one of the changes is the introduction of a new exception handling block that includes a RAISE statement.

The amended version of procedure b is now:

create or replace procedure b
is
begin
 dbms_output.put_line('In procedure b');
 c();
 -- lots of new functionality here
exception
 -- new exception handler for the new functionality
 when others then
 -- do something here and re-raise the exception
 RAISE; 
end b;
/

Now when procedure a is run again we see the following:

begin
   a();
end;

In procedure a
In procedure b
In procedure c
ORA-06512: at "HR.B", line 11
ORA-06512: at "HR.A", line 5

Oh no! we have lost the line number where the exception occurred. Instead of displaying that the exception was raised by line 5 of procedure c as it did in the first example, the callstack reports the exception location is now at the the location of the RAISE statement within procedure b!

To get the accurate output from dbms_utility.format_error_backtrace you need to call it

  • In the exception section that first raised the exception

or

  • Call it from the outermost PL/SQL block and ensure that the intermediate routines do not raise\re raise the exception.

The location of exception handling within an application is a project wide decision which should be agreed before development begins. However if your error handling package includes a call to dbms_utility.format_error_backtrace then you should keep this nuance in mind.