One project every month - making stuff better ...

Extracting information from Airbrake, directly from inside the database

In this article, I will describe how we can select project information and deployment information from Airbrake, from inside the database. We want to do this, because later on in these demos, we want to build an automated job, that track errors in relation to code releases inside the database.

First we need to setup the environment. Login to the airbrake account, that we created in the previous tutorial. We need to run a couple of additional scripts that you need to download from the github project page:

  • airbrake_projects.spec.sql
  • airbrake_projects.body.sql
  • airbrake_deploys.spec.sql
  • airbrake_deploys.body.sql
  • airbrake_tables.spec.sql
  • airbrake_tables.body.sql

Once logged in, setup the environment using the session_setup procedure.

This time we have added the user key as well as the project key (you can get the user key, from the user settings page on the airbrake homepage), because we need the user key to access information about all projects etc.

[oracle@localhost airbrake_utl]$ sqlplus airbrake/airbrake@orcl

SQL*Plus: Release Production on Tue Oct 13 05:20:07 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Oct 10 2015 19:40:35 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set serverout on

    transport_protocol => 'https'
    , airbrake_host => ''
    , airbrake_host_port => '443'
    , airbrake_api_name => 'api'
    , airbrake_api_version => 'v3'
    , wallet_location => 'file:/home/oracle/wallet'
    , wallet_password => 'WalletPasswd123'
    , airbrake_project_id => '[project_id]'
    , airbrake_project_key => '[project_key]'
    , airbrake_user_key => '[user_key]'
SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15  
PL/SQL procedure successfully completed.


So once we have the session configured we can start using some of the new pipelined functions that I have added to the utility. We can get information about our projects, by using the airbrake_tables package. Here we are select a list of all the projects we have registered in our account, and that our user has access to:

SQL> column name format a30
SQL> select id, name from table(airbrake_tables.list_projects);

---------- ------------------------------
    116548 plsqlninja

As you can see, we have one project listed here, but it could have been more than just the one. If we had multiple projects, and only wanted data about one specific project we can, do the following select:

SQL> set lines 250
SQL> select id, name, noticetotalcount, rejectioncount from table(airbrake_tables.show_project(116548));

---------- ------------------------------ ---------------- --------------
    116548 plsqlninja					10		3

If we want to track errors related to specific release, we need to add information about our releases to to Airbrake. We can do that with the new airbrake_deploys package and the create_deploy procedure. Let's take my github project as an example. Let us register the latest commit as a deployment:

SQL> begin
  airbrake_deploys.create_deploy('production', 'morten-egan', '', '5de946dd6e968913972a5b3d4a0c5fad7473390c', 'v1.0.0');
/  2    3    4  

PL/SQL procedure successfully completed.


Now that we have added a deployment we can use the airbrake_tables package once again to select information about our deployments:

SQL> column id format a25
SQL> column repository format a50
SQL> column environment format a15
SQL> column revision format a12
SQL> select id, repository, environment, substr(revision,1,10) as revision from table(airbrake_tables.list_deploys);

------------------------- -------------------------------------------------- --------------- ------------
1530407745104399631	     production      5de946dd6e
1530263307384277928	     production      5f805e924f


So now that we can see the projects, and we can register and get information about our code deployments we are ready for the next article, where I will show how we can use the projects and deployments information to filter on error messages.
So stay tuned

Tagged in : Pipelined function, UTL_HTTP