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!