SQL Server 2014 Install Experience

I recently starting working on a new project using Microsoft SQL Server.  I'm not new to relational databases, but I am new to SQL Server.   So, I'm going to do what any geek would do:  install it myself, poke with it a stick, and see what it does.  Let's get started.

First, google "microsoft download sql server".  I suppose I should use bing, but "bing" is not a verb, at least not yet.  From the google search results, select the SQL Server Express Edition - Microsoft link.

The Microsoft Express Edition page includes a download link for 2012 Express and a link to try SQL Server 2014.  There's a lot of experience on the Internet installing SQL Server 2012 or 2008, so let's try the 2104 version.  Click on Try SQL Server 2014 Express .  (If you're still running Windows XP, you will need to upgrade your Windows operating system or install SQL Server 2008.)

The next page describes five downloads in detail:

• LocalDB (SqlLocalDB)
• Express (SQLEXPR)
• Express with Tools (SQLEXPRWT)
• SQL Server Management Studio Express (SQLManagementStudio)
• Express with Advanced Services (SQLEXPRADV)

We will use the last one -- it includes the database, the tools, and SQL Server Management Studio.

Click the Get Started Now button.

Sign in with you live.com or outlook.com id.  Complete the registration page, select Express with Advanced Services radio button, select 32/64 bit,  and select your language.

The download starts automagically.  If you don't have the Akamai Netsession installer, you will be prompted to install it. Click download to install it; after the installer is done, the download continues.

After the download is complete, find the file and execute it.  In my case, I'm running SQLEXPRADV_x64_ENU.exe.  The file is over 1GB in size, so it will take a moment to load.  Accept the default directory to load files, and get a coffee.  After a few minutes, the SS Installation Center opens.  Select the first option (assuming you don't have an older version), then accept the license agreement.




The setup continues, finally stopping at the Feature Selection screen.  Accept the defaults, which includes everything but the LocalDB.   Click Next. 




At the Instance Configuration, again take the defaults, noting the names: SQLExpress is the Named instance and SQLEXPRESS is the Instance ID.  Notice that the SQL Server directory and Reporting Services directory are both versioned 12, not 14.  These cannot be changed, but what's in a name?  Just click Next to continue.




At the Server Configuration Screen, set up the accounts.   Again, note and take the defaults.  Click next:




The Database Engine has four tabs: Server Configuration, Data Directories, User Instances, and FILESTREAM.   My server is just for learning purposes, so I'll use the default Windows authentication mode. 

The Reporting Services Configuration window has radio buttons Install and configure, or Install only.  Again, take the default Install and configure, and click next.

Now the Installation Progress window displays while the install completes.  This will take a while, grab a coffee and a donut. 


Finally, installation is complete.  On my older computer, installation took about half an hour.  But, the installer does all the work and the installation is easy. The Complete install screen lists some additional resources.  Note that unlike earlier SQL Server 2005 and 2008, the Microsoft SQL Server books are now online:

• Use Microsoft Books Online for SQL Server
• SQL Server Sample Databases
• Release Notes
• Microsoft Update

Now I'm ready to take a dive into SQL Server with even more resources at the SQL Server 2014 forums on MSDN, the training courses at www.lynda.com, or perhaps even a paper book.

Documenting APEX Development

Most of us started programming using the Edit -> Compile -> Link -> Test cycle.  Whether we edited our source code in a simple editor like vi or in a development environment like Eclipse, we always had a source code file that we could print, or share with co-workers, or save in a repository.  The source code was a record of exactly how we solved a particular problem.

In declarative programming environments like APEX, we don't have any source code.  We develop pages by working through the APEX wizards in a browser window. Yes, we could export a page; but the resulting exported code isn't intended for mere human readers, and it's certainly not a road map to developing a similar page. Someone reading the export could not easily see what choices the developer made, or when the developer took the defaults.

So, how can we document what the APEX developer sees as they work through the widgets?  We could take a lot of screen shots and paste them into an MS Office or OpenOffice document.  Many of the APEX wizards display a page that would take four or five screenshots to document; pasting all the snapshots into another document is a pretty clumsy process.  We need a way to capture the whole browser page in fell swoop.

Printing the browser page does a good job of document everything on the page, but rather than printing to paper, we can print to the Microsoft XPS printer.   XPS is a device-independent document format describing the layout and properties of a document using XML.  The Microsoft XPS printer prompts the user to save the XML document in a file, and these files can be viewed using a XPS viewer.

The XPS files aren't source code, but in a declarative programming environment, they're the next best thing. Now you never need to wonder again how you defined a control, configured security, or defined a region. Just print the definition as an XPS file. Be sure any text areas are large enough to display all code that you entered, and then print the page, choosing XPS as your printer.  Save the XPS file with a descriptive name, and you will have saved a record of how you defined the object.  Now we have file that we can view later, share with co-workers, or save in a repository.

Saving Windows XP

No, this isn't a plea to save Windows XP from retirement.  Windows XP had a long and useful life, and it finally found a worthy successor in Windows 7.  When Microsoft officially ends Window XP support in April, most of us won't have computers old enough to still be running XP.

Except for me.  I do have a few older computers. (My Heathkit H-8 is over 30 years old.)  I get a few extra years out of these machines by running Linux on them.  But the screen on my Ubuntu/Windows laptop started failing a few years ago, and it's getting progressively worse.  So, when someone offered me a newer laptop, I jumped at the chance to get a faster machine with a working display.

The new laptop spent its early years as a gaming box, and there was a lot of software installed on the machine that I didn't want.   I was a little suspicious of some of the software, too.  And with good reason, as it turned out.  As I ran one software uninstaller, I got a popup window asking me what other games I wanted to install.  Wasn't I trying to uninstall software? I closed that popup window, and the computer was never right again.  Argh!!   Most of the start menu was gone.  Argh!!  The Run... command was gone. Argh!!  None of the icons in the Control Panel worked, and attempting to run programs from the command prompt failed, too.  Argh!!!!

Not a big deal, I thought,  I intended to re-partition the disk and install Ubuntu on half the disk.  Two weeks later, after fixing the fouled-up disk partition table and replacing a tempermental DVD drive with one from eBay,  I had a fresh Ubuntu install.   Oh, and there was the Broadcom wireless driver to deal with: google  "install broadcom wireless linux" to see what fun that was!

So, for the past few months I've been happily working in the Linux world.  I usually don't need to boot into Windows, but I have a few apps that only work under Windows. We were heading out on a road trip, and I needed to update the maps on my Magellan GPS.  I figured it was time to get the Windows side working.

When Windows is badly hosed, the best thing is to format the partition and start with a clean, fresh install.  And then run Windows Update to get the latest enhancements and security patches.  But I didn't have any recovery CDs for the laptop, and if there was a recovery partition, the partition was long deleted.  Googling my options, I found a useful article on the Microsoft support website:  https://support.microsoft.com/kb/307545

According to the support article, I needed to follow the procedures after booting from a recovery CD.  But I didn't have a recovery CD.  My Windows system was clearly compromised by junkware, so booting  Windows into full or maintenance mode didn't seem like good options, either.

But, I did have a good Ubuntu partition. Why not mount the Windows partition to Linux and follow the Microsoft directions from a xterm or terminal window?   With a non-functional Windows install, I had nothing to lose.  If you're in same situation, follow these tips for using Microsoft's procedure from a Linux command prompt:

• You need root authority to mount the Windows partition: su
• Create a new mount point in under your /mnt directory:  cd /mnt; mkdir c
• Mount the Windows partition to the new mount point:  mount /dev/sda1 /mnt/c
• Follow the Microsoft notes, substituting /mnt/c/ for C:\WINDOWS\
• Substitute the Unix cp command for the Windows copy command.
• Substitute the forward slash  ( / ) for the backward slash ( \ ) when typing path names.
• Remember that Unix is case sensitive.  Windows won't care, but Linux will.  The Windows filenames will be a mix of upper and lower case characters, and in the Linux shell window you must type the names exactly as you see them.

After following the directions from my xterm window, I rebooted the laptop and selected Windows  from the GRUB menu.  And....  Success!!  I could use Windows again.  My first task was running the Malwarebytes scanner; my second task was running the  Microsoft Malicious Software Removal Tool.  Finally, after running Windows update to grab the latest fixes, the Windows side of my laptop is back in action.

In a couple of months, Microsoft will end Windows XP support.   If you have an older computer that lacks the power to run one of the new versions of Windows, and if you want to use a operating system that's supported, take a look at the open source world.  I've used many versions of Linux over the past twenty years: Yggdrasil, Slackware, Mandake, Red Hat, Suse, and now Ubuntu.  They're all great;  there are lots of tools for sysadmins and lots of apps for desktop users.  Linux has gained wide commercial acceptance for its low cost and robust server performance.  Windows is still a more popular desktop then Linux, but there is a large selection of desktop applications available to the Linux user.  And if you have an older computer that's just taking up closet space, why not try Linux?

Oracle Pipelined Functions

I've had a few opportunities to use and write PL/SQL pipelined functions.   From a developer's point of view, a pipelined function is just a PL/SQL function with a new way of returning data.  From the function user's point of view, a pipelined function looks like a view on steroids. 

Let's start with the user's point of view first.   Why use a pipelined function?

• The function can implement complicated business logic that might not be feasible in a view.
• The  function can extend security. Create the function with definer's rights and grant execute to specific invokers to give the invokers access to data they might not have.
• The function can implement additional security, restricting the data returned to the user.
• The function can accept arguments, looking like a parameterized view.
• The function can create rows of data and log errors.

Applications commonly create data with date information.  Suppose we have the following INCIDENT_LOG  table. Every incident is logged in this table with the date and description:

create table incident_log (
    incident_date date not null,
    incident_description varchar(4000)
    );

From this table, we can easily ask questions like "How many incidents did we have on January 2?" Or "How many incidents have we had?"  But, we can not answer the question "what days did we not have an incident?" We can not answer the question "Including days with no incidents, how many incidents do we average per day?" There is no data in our table if we did not have an incident, so we can not answer the last two questions.

From a developer's point view, to answer the last two questions we need to join our incident table to a table with all possible dates of interest. We could create a new table and populate it with all the dates to cover the range of dates in the INCIDENT_LOG table. For organizations with years or decades of data, that's a big table requiring ongoing maintenance.

Why not just create the dates we need when we need them?  We can do exactly that using a pipelined function. Our function will accept two arguments, a start date and an end date. Our function will return a table of rows with two columns, the date of interest and the name of the day, starting with the start date and finishing with the end date. 

Our pipelined function returns rows from a table, so we need to  create a TYPE of the table to return.  First, we create a row in the table as an OBJECT, then we create the TABLE of row objects.  Here's the code:

create or replace
    type row_of_date as object (
        v_date date,
        v_day  varchar2(12)
    );
/
create or replace
    type table_of_date as table of row_of_date;
/

Next, we create a function named WORK_DATES that returns the working dates and days between two dates.   Let's start with the declarations. Our function accepts two input dates and returns a TABLE_OF_DATE. We created the TABLE_OF_DATE in the code above. Notice, too, that our return is PIPELINED. The function creates one local variable, V_ROW_OF_DATE. This variable is an object, and so we must instantiate it using the default constructor. We do not need to create the constructor, Oracle defines the default constructor for us.

create or replace function work_dates (p_start_dt in VARCHAR2,
                                       p_end_dt in VARCHAR2)
return table_of_date pipelined as
        
    v_row_of_date row_of_date := row_of_date(null, null);

begin
    
    v_row_of_date.v_date := to_date(p_start_dt,'yyyy-mm-dd');
    loop
        
        v_row_of_date.v_day := to_char(v_row_of_date.v_date,'FmDay');
        if v_row_of_date.v_day not in ('Sunday','Saturday') then
            pipe row(v_row_of_date);  -- Pipelined functions return data here
        end if;

        v_row_of_date.v_date := v_row_of_date.v_date + interval '1' day;

        if v_row_of_date.v_date > to_date(p_end_dt,'yyyy-mm-dd') then
            exit;
        end if;

    end loop;

end work_dates;
/

Now, let's examine the executable code.  Our function starts by converting the start date to a date and stores it in v_row_of_date.v_date.  Next, the function determines the day of the week.  Our WORK_DATES function returns working days and skips Sundays and Saturdays.   If our organization's calendar is online, we could add logic here to query the calendar via a TCP/IP connection and skip holidays, too.

The important step is next:  piping the data back to the caller.  We don't use a RETURN statement, instead we use a PIPE ROW statement.

Finally, our function adds 1 day to the date and exits when the range of dates has been piped to the caller.

Using our new function is easy. We just CAST it as a table and query it as a table:

select *
from table(work_dates('2001-01-01','2001-01-09))
 
V_DATE          V_DAY
----------      ----------
01/01/2001      Monday
01/02/2001      Tuesday
01/03/2001      Wednesday
01/04/2001      Thursday
01/05/2001      Friday
01/08/2001      Monday
01/09/2001      Tuesday

Now, back to our queries. What working days in 2012 do not have incidents associated with them?

select v_date, v_day
from table(work_days('2012-01-01','2012-12-31')) a
where not exists ( select 1
                   from incident_log b
                   where a.v_date = b.incident_date
                 );

That was easy.  Next, what is the average number of incidents per working day in 2012? How many incidents were there in 2012?

with by_day as ( 
   select v_date, sum(case
                        when b.incident_date is not null then 1
                        else 0
                      end) daily_count
   from table(work_days('2012-01-01','2012-12-31')) a
   left outer join incident_log b
     on a.v_date = b.incident_date
   group by v_date
)
select sum(daily_count) total_incidents,
       count(*) n_work_days,
       avg(daily_count) incidents_per_day
from by_day;

The common table expression by_day sums up the daily count of incidents; the outer query sums the annual count and average daily count.

Pipelined functions make easy work of writing queries to answer questions where we're missing data.  With the ability to add complicated business logic, extend security, and write logs, pipeline functions are another useful tool in our toolbox.

Up, Periscope!

Last year I "inherited" the maintenance of some our shop's old PL/SQL code.  It was a key piece of infrastructure code; it was also over 15 years old, and many hands had worked on the code before me.  There was some interesting functionality with which I was not familiar, so before I made changes to the code, I needed to take a deeper dive into the code. 

After 15 years and many hands working on the code, I found the scope of the variables to be very confusing.  Variables were global; variables were public; variables were local to a function or a procedure or an anonymous block.  Some variables were scalars, others were part of a structure.  Some variables belonged to the same domain; they were really duplicates of each other.  There were local and global variables with the same name, and some of the code just assigned values back and forth.

So, for year, I maintained the code as it was, doing what I needed to do to add functionality as required, trying to make as few changes as possible to not break something. 

Now I have a new mandate:  retire the old code and write a new, simpler version.  There's a lot of good work in the old code, so I'm not going to throw the baby out with the bath water.  But I am taking a deeper dive into the scope of the variables, and that leads to this post's topic, taking a deep dive into variable scope and variable lifetime.  We'll take a look at private versus public variables, local versus global variables, and why we might choose one over the other.  Our examples are in Oracle's PL/SQL programming language, but it's important to understand variable scope and variable lifetime in any programming language.

Let's start with a package specification:

create or replace package demo_scope as

global_public_string varchar2(32);

procedure test (
   p_arg1 in varchar2 default null,
   p_arg2 in out number   default null);

function test_func (
    p_arg3 in number )
  return varchar2;

end demo_scope;

Our package has one procedure, one function, and one varchar2 string visible to any user with execute permission on the package. Based on the package specification, a valid anonymous block could look like the following:

declare
   my_func_results varchar2;
   my_number number := 1776 ;
begin
   demo_scope.global_public_string := 'my value';

   demo_scope.test(p_arg1 => 'argument to test', 
                   p_arg2 => my_number);

   my_func_results := demo_scope.test_func(2001);
end; 

Our package body implements the public functions and procedures. In addition, the package body defines more variables, functions, and procedures that are not visible to package users. Here's our package body:

create or replace package body  demo_scope as

global_private_string varchar2(32);

procedure test (
   p_arg1 in varchar2 default null,
   p_arg2 in out number   default null) is
    
   local_private_string varchar2(32);

begin
     
    -- An anonymous block within a named procedure
    declare
    
       local_private_in_block_string varchar2(32);

    begin
    
       global_public_string := p_arg1;
       global_private_string := 'some other value';

    end;
    
    p_arg2 := 1976;

end test;
 
function test_func ( p_arg3 in number )
    return varchar2 is

begin

  return null;

end test_func;

begin  --Package initialzation

  global_public_string := null;
  global_private_string := null;  

end demo_scope;

Let's take a look at the variables and see what we can do with them. We'll start from the package specification and work down through the package body.

1. Variable global_public_string -- Any user of the package and any block within the package can directly reference this variable.  Declaring  variables (functions and procedures, too) in the package spec instead of the package body makes them public.
2. Procedure test -- Procedure test is public, and any user of the package can call this procedure.
3. Parameters p_arg1 and p_arg2 -- Any user calling procedure test can supply arguments for these parameters..  Parameter p_arg1 is passed by value to the procedure. Because p_arg2 is specified as an "out" parameter, procedure test can change p_arg2 and the new value is returned to the caller.  If we try to assign a value to parameter p_arg1 within procedure test, we will get an error message when we try to compile the procedure.
4. Function test_func -- Any user of the package can call this function.
5. Parameter p_arg3 -- Any user calling function test_func can supply an argument for this parameter.
6. Variable global_private_string -- Only functions, procedures, and blocks defined within the package body can use this variable.  Declaring variables outside of functions and procedures in a package body makes the variable scope global.  Declaring variables in the package body rather than the package spec makes them private; only functions and procedures defined in the package can reference these variables.
7. Variable local_private_string -- Only the procedure test and any blocks within procedure test can use this variable. When procedure test ends, the value of this variable is lost.
8. Variable local_private_in_block -- Only the anonymous block within procedure test can use this variable. When this block ends, the value is lost. This variable can not be referenced outside of the block.

To review: locally-defined variables do not retain their value when the block (procedure, function, or anonymous block) ends.   Locally-defined variables can only be referenced within the block that declares them.

Parameters passed to functions and procedures can not be changed by the function or procedure unless the parameter is an "out" parameter.  The PL/SQL compiler flags as an error any attempt to assign a value to an "in" parameter.

Global variables declared in the package body can be referenced by any block defined in the package, but they can not be referenced outside the package body.  Global variables declared in the package spec are public and can be referenced anywhere in the package body, and they can be referenced by any user of the package.

Global variables retain their values when a block ends.   In fact, a package's  global variables retain their values as long as the session is active.  Control can pass from the package back to its caller;  global variables still retain their value.

This property makes them useful for caching results that may be expensive to compute.  Repeatedly selecting data from a lookup table can often be done more inexpensively by defining an associative array as a global variable,  loading data into the array during package initialization, and then searching the array by a key.  This can be a big performance gain when lookups are performed in a tight loop.  And as long as the database session is active, the array retains its values, and there is no need to reload it.

Good programming practice dictates that we define variables in the scope that we need them.  It may be tempting to define variables globally, so that "we'll have them when we need them".  Over-use of globally-defined variables makes a program difficult to understand.  But used carefully, global variables can be a useful tool for improving program performance. 


One last thought:  Have a Happy New Year, and may all your code be bug-free!

Regular Expressions: Analyzing PL/SQL Source

We're going to examine a few themes that we've touched on in earlier posts.  In Get Regular (Expressions) we took a first look at regular expressions, and in Recursive SQL we used the Oracle DBA_DEPENDENCIES view for some sample data.  We'll take another look at a couple of views in the Oracle 11g database, and we'll use regular expressions to dig a little deeper into these views.

We have an old package that's been running for over 15 years.  It's gotten rather long in the tooth, and we need to replace it.   First, we need to find all the other objects that use this package. Oracle's DBA_DEPENDENCIES view will show every object that directly references a package. 

describe dba_dependencies
Name                 Null     Type          
-------------------- -------- ------------- 
OWNER                NOT NULL VARCHAR2(30)  
NAME                 NOT NULL VARCHAR2(30)  
TYPE                          VARCHAR2(18)  
REFERENCED_OWNER              VARCHAR2(30)  
REFERENCED_NAME               VARCHAR2(64)  
REFERENCED_TYPE               VARCHAR2(18)  
REFERENCED_LINK_NAME          VARCHAR2(128) 
DEPENDENCY_TYPE               VARCHAR2(4)   

We plug our schema name and package name in as the referenced_owner and referenced_name, run the query, and the database displays the information we're looking for.

select owner, name, type  
from dba_dependencies
where referenced_owner = 'MY_SCHEMA'
  and referenced_name = 'MY_PACKAGE';

And we see that three other objects use our package:

OWNER       NAME      TYPE             
-------     ------    ------------    
SCHEMA1     PCK1      PACKAGE BODY           
SCHEMA4     PROC1     PROCEDURE        
SCHEMA5     FUNC1     FUNCTION       

But we need to dig a little deeper. Our package has many procedures and functions defined within it. Some of the package functions are obsolete, and some of the package functions need to be rewritten in a new package.  Not only do we want to know what other objects are dependent on our package, we want to know which of the package's functions and procedures are called. The DBA_SOURCE view has all the PL/SQL code compiled into the database.

describe dba_source
Name  Null Type           
----- ---- -------------- 
OWNER      VARCHAR2(30)   
NAME       VARCHAR2(30)   
TYPE       VARCHAR2(12)   
LINE       NUMBER         
TEXT       VARCHAR2(4000) 

We will use two regular expression functions to examine the source.   We will use REGEXP_LIKE to identify the source text that has one of the functions. REGEXP_LIKE returns true if the pattern is found in TEXT and false otherwise.  The lower-case "i" is the match parameter indicating that we want a case-insensitive search.   PL/SQL source is not case sensitive, so the case-insensitive search allows us to find text that is upper, lower, or camel-case.

We will use REGEXP_SUBSTR to identify the function or procedure.  We'll start searching the first position of the TEXT, and we'll return the first occurrence we find.  Again, we will use a case-insensitive search.  Using these two functions, we can perform the same sort of analysis that we we could do by grepping through a code tree on disk. In this example, we're looking to see if specific functions or procedures are used. Here's our query to perform this analysis against the code compiled into the database:

select distinct owner, name, type, 
       regexp_substr(text,
                    'my_package\.(my_func1|my_func2|my_proc1|my_proc2)',1,1,'i') calls
from dba_source
where regexp_like(text,
                  'my_package\.(my_func1|my_func2|my_proc1|my_proc2)','i') 
 ;

The results of the query are every package, procedure, or function that uses the package, and the query identifies what functions and procedures the calling object uses:

OWNER       NAME      TYPE             CALLS
-------     ------    ------------     -------------------
SCHEMA1     PCK1      PACKAGE BODY     my_package.my_func1
SCHEMA1     PCK1      PACKAGE BODY     MY_PACKAGE.MY_FUNC1
SCHEMA4     PROC1     PROCEDURE        my_package.my_proc2
SCHEMA5     FUNC1     FUNCTION         MY_PACKAGE.MY_FUNC2

This query is a useful summary of what's calling my_package and which procedures in my_package are used. We can get more detail from dba_source if we need it. For example, dba_source includes the line number, so the query

select distinct owner, name, type, line, 
       regexp_substr(text,
                    'my_package\.(my_func1|my_func2|my_proc1|my_proc2)',1,1,'i') calls
from dba_source
where regexp_like(text,
                  'my_package\.(my_func1|my_func2|my_proc1|my_proc2)','i') 
 ;

would return

OWNER       NAME      TYPE             LINE      CALLS
-------     ------    ------------     ----      -------------------
SCHEMA1     PCK1      PACKAGE BODY     330       my_package.my_func1
SCHEMA1     PCK1      PACKAGE BODY     621       my_package.my_func1
SCHEMA1     PCK1      PACKAGE BODY     721       MY_PACKAGE.MY_FUNC1
SCHEMA4     PROC1     PROCEDURE        45        my_package.my_proc2
SCHEMA5     FUNC1     FUNCTION         87        MY_PACKAGE.MY_FUNC2

This query shows the line number where the calling object uses our package. This is useful if we need some context to see why the calling object uses our package and what the calling package is doing with the results.

The above examples show us where specific procedures and functions are used in other objects. This is useful if we want a narrow search, but often we want to see every reference to a function or procedure or even a publicly-defined variable in our package. The regular expression for this search is even simpler: PL/SQL  names consistent of letters, numbers, and a few special characters, so when the regular expression can't match any more characters in the expression  [a-z0-9_$#]+, then we have the whole name. Here's the query:

select distinct owner, name, type, 
       regexp_substr(text,
                    'my_package\.[a-z0-9_$#]+',1,1,'i') calls
from dba_source
where regexp_like(text,
                  'my_package\.[a-z0-9_$#]+','i') 
 ;

DBA_DEPENDENCIES and DBA_SOURCE are good repositories to help us understand what objects are using our code and how the objects are using our code.  And, the regular expression functions make searching though these tables much easier. Anytime we can get the database to do the work for us, "that's a good thing".

A Simple Web Client

Cloud  computing and software as a service providers have lots of capacity and attractive rates, so many organizations are taking their applications to the cloud.  After an application becomes cloud-based, many users need to pull some of the data to feed to other systems.  In this post, we'll look at a simple PL/SQL web client  that connects to a web service and downloads data. 

For our data,  we'll use the Data Download Program at the Federal Reserve Bank.  There's a lot of statistical data on the Federal Reserve site, and we will use that data as the source for our simple web client.   The data files are small and free to download, so the Bank gives us a good source of data to use.

From the Data Download Program we click on the Foreign Exchange Rates (G.5 / H.10) link, then we get a page that lets us choose which package we would like.  The H10 radio button is selected by default, so clicking the Download button takes us to the download page.  We could download a CSV into our browser and save the result as an OpenOffice or Excel spreadsheet.  But for production purposes, following these links and downloading data through a web browser is a tedious chore. Notice the Direct Download for Automated Systems link?   We'll copy that link and use it for our demonstration. 

create or replace procedure simple_web_client as
    
  timeout number := 60;  -- timeout in seconds

  /* Below is the URL from the Direct Download for Automated Systems.  Just copy the link
     and paste the text.
  */
  v_url varchar2(1024) :=
  'http://www.federalreserve.gov/datadownload/Output.aspx?rel=H10&series=122e3bcb627e8e53f1bf72a1a09cfb81&lastObs=10&from=&to=&filetype=csv&label=include&layout=seriescolumn&type=package';
   
  v_line_count number := 0;
  v_line_in varchar2(1024);
  v_header varchar2(1024);

  /* UTL_HTTP.REQ is set by the BEGIN_REQUEST function.  REQ includes 
     the URL, method, and http_version.
  */
  req   UTL_HTTP.REQ;

  /* UTL_HTTP.RESP is set by the BEGIN_RESPONSE function.  RESP includes   
     the status, a brief message, and the http version.
  */
  resp  UTL_HTTP.RESP;
    
begin
    
  utl_http.set_transfer_timeout(timeout);         -- set the timeout before opening the request
  req := utl_http.begin_request(v_url);           -- Pass our URL, get a req object back
  
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

  resp := utl_http.get_response(req);             -- Pass our req object, get a resp object back
  utl_http.read_line(resp, v_header, true);       -- Read line of resp into v_header

  dbms_output.put_line('CSV Header = ' || v_header);
  v_line_count := v_line_count + 1;

  loop

    utl_http.read_line(resp, v_line_in, true);    -- csv data
    v_line_count := v_line_count + 1;
    dbms_output.put_line('CSV Data = ' || v_line_in);

  end loop;

exception
  when utl_http.end_of_body then                  -- end of the data
      dbms_output.put_line('Lines read, including header = ' || v_line_count );
      util_http.end_response(resp);               -- close the response object

  when others then
    dbms_output.put_line('Unhandled exception, lines = ' || v_line_count  );
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
 
    -- Do not leave anything open.  Close everything and ignore errors.
    begin
       utl_http.end_response(resp);
       utl_http.end_request(req);
    exception
       when others then null;
    end;
    
end simple_web_client;

Now we execute our client using SQLPLUS or SQL/Developer. If we connect properly, our output will look something like this:

anonymous block completed
CSV Header = "Series Description","Nominal Broad Dollar Index ","Nominal Major Currencies Dollar Index ","Nominal Other Important Trading Partners Dollar Index "
CSV Data = "Unit:","Index:_1973_Mar_100","Index:_1973_Mar_100","Index:_1997_Jan_100"
CSV Data = "Multiplier:","1","1","1"
CSV Data = "Currency:","NA","NA","NA"
CSV Data = "Unique Identifier: ","H10/H10/JRXWTFB_N.B","H10/H10/JRXWTFN_N.B","H10/H10/JRXWTFO_N.B"
CSV Data = "Time Period","JRXWTFB_N.B","JRXWTFN_N.B","JRXWTFO_N.B"
CSV Data = 2013-10-14,ND,ND,ND
CSV Data = 2013-10-15,101.1463,75.7635,128.2686
CSV Data = 2013-10-16,101.0694,75.7839,128.0691
CSV Data = 2013-10-17,100.4167,74.9717,127.6647
CSV Data = 2013-10-18,100.3842,74.8882,127.7015
CSV Data = 2013-10-21,100.6396,75.0142,128.1114
CSV Data = 2013-10-22,100.3595,74.7093,127.8816
CSV Data = 2013-10-23,100.4550,74.8150,127.9575
CSV Data = 2013-10-24,100.5485,74.8527,128.1189
CSV Data = 2013-10-25,100.5134,74.9733,127.8807
Lines read, including header = 16

The Oracle utl_http packages makes reading data from a webserver as simple as reading data from a file. With a simple web client, we can read any data that's available from a web server. Our web service example used CSV-formatted data, but we could download XML data as easily. And for a real hands-free operation, schedule your client using DBMS_SCHEDULE or DBMS_JOB.

There's just one caveat when using the utl_http functions: depending on your DBAs and your installation, you may not have access to use the network functions. If you get an error like

ORA-24247: network access denied by access control list (ACL) 

then it's time to visit the DBAs and request that they add your schema or your user account to the network ACL.