Monthly Archives: July 2016

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',
    '[email protected]',
    '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