Allan’s Musings

Just another weblog

Archive for April, 2009

Brushing up on PL/SQL

Posted by Allan Peda on April 27, 2009

A few weeks ago I came across what might be the best intermediate introduction to PL/SQL I have ever seen. While it is no substitute for the writings of masters such as Steven Feuerstein, it covers some essential aspects of PL/SQL and it clarified concepts such as block scope and dynamic cursors.

Doing SQL from PL/SQL: Best and Worst Practices

The Google html-ized version is available here


Posted in PL/SQL | Tagged: , | Leave a Comment »

Timeout functions

Posted by Allan Peda on April 26, 2009

Recently I had to write some controlling code for batch jobs that are run nightly. the problem was that transient network issues caused jobs to hang. The solution was to force hanging jobs to fail after a specified timeout allowing these jobs could be retried several times.

timeout 5 /usr/local/bin/some_longtask

function timeout {
  if [[ ${1//[^[:digit:]]} != ${1} ]]; then
    echo "First argument of this function is timeout in minutes." >&2
    return 1

  declare -i timeout_minutes=${1:-1}
  # sanity check, can this be run at all?
  if [ ! -x $1 ]; then
    echo "Error: attempt to locate background executable failed." >&2
    return 2

  "$@" &
  declare -i bckrnd_pid=$!
  declare -i jobspec=$(echo kill -9 $bckrnd_pid |\
    at now + $timeout_minutes minutes 2>&1 |\
    perl -ne 's/\D+(\d+)\b.+/$1/ and print')
  # echo kill -9 $bckrnd_pid | at now + $timeout_minutes minutes
  # echo "will kill -9 $bckrnd_pid after $timeout_minutes minutes" >&2
  wait $bckrnd_pid
  declare -i rc=$?
  # cleanup unused batch job
  atrm $jobspec 2>/dev/null
  return $rc

Posted in Programming | Tagged: , | Leave a Comment »