Proceeding in an orderly fashion with Oracle

PL/SQL has many endearing features for the programmer and one or two which are less so. One of its nicer features is the ability to define procedures which can then be used as if they were a part of the language. Procedures may define and use variables which are local to that procedure. An extension to this is the concept of packages: bundles of procedures which can be used as complete libraries and slotted in as 'black boxes' wherever they're required. Packages may define variables which are local to the package but global to the procedures contained by the package.

Procedures.

A procedure consists of a name (with optional parameters), IS, BEGIN, any number of statements and END. As with C, Perl and similar languages, each statement is terminated with a ';' (semi-colon). Here's a simple example...

PROCEDURE GetSalesList(Customer IN VARCHAR2, Process in VARCHAR2)
IS
BEGIN
IF Process = 'PURGE'
THEN
DELETE FROM SalesFeed
WHERE CustId in (SELECT distinct Id
FROM Customers
WHERE Name = Customer);
ELSE
INSERT INTO Invoice (CustomerId, TranDate, Value)
SELECT CustId,
FeedDate,
Amount
FROM SalesFeed
WHERE CustId in (SELECT distinct Id
FROM Customers
WHERE Name = Customer);
END IF;
END;

Thre's effectively no limit to how large and complex procedures may be. Once installed in a package, procedures may be called by any query which has access to the package, making this a very useful facility.
:

SQL*Plus: Oracle's Swiss Army Knife

For a very long time, Oracle has shipped with the SQL*Plus application as standard.

There are different versions for different operating systems but users will, most commonly, come across the versions for Microsoft Windows and those for the Unix family. The difference between these is that the Windows version is a fairly basic application whereas that for Unix is a very powerful tool indeed because it allows you to use it as a standard command line application like any other, so permitting its use in complicated shell scripts.

What a lot of people don't realise is: there's a CLI version of SQL*Plus installed along with the not terribly useful Windows version which, within the limitations of Windows, permits all the flexibility of the Unix variant.

Once you know this, your ability to automate Oracle tasks on Windows is increased dramatically. While not quite as flexible as the Unix version, due to the limitations of Windows, there are still many things you can do that are just too much of a pain when using the GUI version.

Some Awk Tips and Tricks

Finding the length of a line.

A colleague needed to find the length of a particular line in a file. He discovered that using 'wc' gave the wrong result (as in "head -2 filename | tail -1 | wc -c"). Here's what he came up with instead. Note the parentheses...

cat filename | awk '{ if ( NR == 2 ) {print length($0); exit; } } '

Sizing a directory.

This uses Awk's ability to do arithmetic across multiple input lines to produce a count, total and average file size for a directory or a supplied pattern. It's a usefull tool for quick 'n' dirty system admin...

echo "file counter and sizer"
echo "----------------------"
if [[ -z $1 ]]
then
echo "Sizing entire directory"
else
echo "Sizing files for pattern [$1]"
fi

ls -l >/tmp/fsz.$$_1

# -------------------------------
# Remove any directory entries...
# -------------------------------
grep -v ^total /tmp/fsz.$$_1 | grep -v ^d >/tmp/fsz.$$
rm /tmp/fsz.$$_1
# ------------------------
# Set up the search job...
# ------------------------
if [[ -z $1 ]]
then
cat /tmp/fsz.$$
| awk '{s += $5}; END
{printf "\nThere are %d files matching pattern\nAverage size is %f\nTotal size is %f\n", NR, s/NR, s}'
else
grep $1 /tmp/fsz.$$ | awk '{s += $5}; END {printf "\nThere are %d files matching pattern\nAverage size is %f\nTotal size is %f\n", NR, s/NR, s}'
fi
rm /tmp/fsz.$$


Don't use awk - use nawk!

I couldn't work out why this wouldn't work when I ran it using awk (as it worked fine on another machine). It turned out that it would perform admirably if I ran it using nawk instead. It's worth trying this out on your own machine and seeing what happens...

nawk '{ if(substr($0,405,2)=="LS") print $0 }' sourcefile.dat | head -10000 > targetfile.dat
: