Oracle Equivalent of MySQL's LIMIT (Or how to page results in Oracle)

I'm really just posting this for my own benefit but if you're reading this and it has been helpful, drop me a comment to let me know.

SELECT
  *
FROM (
  SELECT 
    a.*,
    ROW_NUMBER() OVER (ORDER BY column_to_sort) rn
  FROM
    my_table a
  WHERE
    my_column_to_filter = 'my_value_to_filter'
 )
WHERE
  rn >= my_starting_row AND rn <= my_ending_row

Evaluating Backreferences in Oracle REGEXP_REPLACE

Here's the problem. I want to use Oracle's REGEXP_REPLACE function to replace a string of digits with a zero-padded string. In Perl I would do it like this:

#--- Change xyz123 to xyz000123
my $string = "xyz123";
$string =~ s/(\d+)$/sprintf("%06d", $1)/e;

So here is what I started with in Oracle PL/SQL:

my_string := 'xyz123'
regexp_replace(my_string, '(\d+)$', lpad('\1', 6, 0));

And what I got was xyz0000123. What I discovered was that the LPAD function was being evaluated before the backreference (\1) was. So no matter what I did, the LPAD function always saw the string to be padded as 2 characters long.

So to get around this, I wrote another function called evalpad. With this, I do the replacement in 2 steps. The first is to replace the string with an embedded pad psuedo-function. Then I call a second function to do the actual replacement. Here's the code:

create or replace function evalpad (
  strIn in varchar
)
return
  varchar
as
  retval varchar(255);
  arg1 varchar(50);
  arg2 varchar(50);
  padded_number varchar(100);
begin
  retval := strIn;
  while (regexp_instr(retval, 'pad\((\d+)') > 0) loop
    arg1 := regexp_substr(retval, 'pad\((\d+)');
    arg1 := regexp_replace(arg1, 'pad\(', '');
    arg2 := regexp_substr(retval, 'pad\((\d+),\s*(\d+)');
    arg2 := regexp_replace(arg2, 'pad\((\d+),\s*', '');
    padded_number := lpad(arg1, arg2, 0);
    retval := regexp_replace(retval, 'pad\((\d+),\s*(\d+)\)', padded_number, 1, 1);
  end loop;
  return retval;
end;

Then in my original function I do this:

my_string := 'xyz123'
regexp_replace(my_string, '(\d+)$', 'pad(\1, 6)');
my_string := evalpad(my_string);

Catching and Logging JavaScript Errors

As browser-based applications become more JavaScript centric, it becomes harder to debug user issues. For years I have been logging and handling server side errors, but only recently have I started logging JavaScript errors. And it turns out the solution was very simple.

First, the JavaScript code. Near the top of my main JavaScript file that I include on every page of my application, I put this code that I found online and modified for my own use. I should note here that I am using jQuery (and you should to).

window.onerror = function(m,u,l){
    $.post(
        basePath + "/js_error.cfm", 
        {
            msg: m,
            url: u,
            line: l,
            window: window.location.href
        }
    );
    return true;
}

All this code is doing is catching untrapped errors and posting them to a page on the server. Now for the server-side code:

    


#errorReport#

The ColdFusion script write the error to a log file and then emails me the report.

For now this will blindly send me error reports in the background. But in the future I would like to pop up a form to the user to get more information such as asking what they were trying to do when they got the error. I built a prototype using the jQuery UI dialog module. However, I need to work out some more UX questions like:
- How often do I ask for input especially if it's an error they are getting on every page?
- Should I pilot this dialog to select users first?
- Would a live chat with tech support be an option (using web sockets)?

SMB/UNC Path Translator Bookmarklet

I needed a quick way to translate a UNIX-style SMB path to a Windows-style UNC path and vice versa.

For example:

From: smb://my-server/path/to/file
To: \\my-server\path\to\file

OR

From: \\my-server\path\to\file
To: smb://my-server/path/to/file

So I created the bookmarklet below. To use it, drag the link below to your bookmark bar. Then click it, enter a path, and click OK. It will return a translated path that you can then copy and paste.

Path Translator

Using KDiff3 on Mac with Subversion

I love TortoiseSVN. So I was quite disappointed when I moved from a PC to a Mac and was no longer able to use it. There are several alternatives for Mac, but the one function that I haven't been able to easily find is a way to do a graphical diff between my working copy and the current version of the file. So I wrote a Perl script to do it for me. To get this to work, there were a few prerequisites:

- Install KDiff3
- Save this file in a directory that is part of the PATH
- Create a temp directory in my home directory

Then I just open a Terminal window to the directory where my working copy resides and issue the command svndiff filename. I'm sure it's not perfect but it does the trick for me.

By the way, if you have questions or suggestions or just find this useful, please drop me a comment. Thanks.

#!/usr/bin/perl
###############################################################################
#     Program : svndiff
#     Written : 09/27/2012
#      Author : carmond
# Description : Opens KDiff3 to compare working copy of file with 
#               current version in Subversion (ver 1.6) repository
#      Syntax : svndiff <filename>
#    Modified :
###############################################################################

($file) = @ARGV;

if (!file) {
 print "Syntax: svndiff <filename>\n";
 edit;
}

if (!(-e $file)) {
 print "$file does not exist\n";
 exit;
}

if ($file =~ m|/|) {
 $file =~ m|(.+)/(.+)|;
 $path = $1;
 $file = $2;
}
else {
 $path = `pwd`;
 chomp $path;
}

$svn_entries = "$path/.svn/entries";
if (!(-e $svn_entries)) {
 print "Could not find $svn_entries\n";
 exit;
}

open (IN, $svn_entries);
for ($i = 1; $i <= 5; $i++) {
 $repository = <IN>;
 chomp $repository;
}
close (IN);

$temp_file = "$ENV{'HOME'}/temp/$file";
unlink $temp_file if $temp_file;

$cmd = "svn export $repository/$file $temp_file";
`$cmd`;

if (!(-e $temp_file)) {
 print "Error getting file from repository\n";
 exit;
}

$cmd = "/Applications/kdiff3.app/Contents/MacOS/kdiff3 $temp_file $path/$file";
`$cmd &`;

Exporting the Windows Task Manager Process List

I'm trying to make a case for a new computer at work. One of the things I want to show (besides the incredibly slow boot time) is that I am maxing out my RAM. To do that, I wanted to get a list of tasks running that I could import into a spreadsheet to figure out how how much memory my day-to-day applications consume (Outlook, Eclipse, SQL Developer, Firefox, etc.) versus all of those background processes that I don't even know what they do. Here's how I did it:

Start -> Run -> cmd
tasklist > tasklist.txt

Then open tasklist.txt in Excel as a fixed-width file and go to work.

Change Folder Name Using mod_rewrite

For anybody out there that may be needing to do a simple directory name replacement using mod_rewrite:

RewriteEngine On
RewriteRule ^(.+)\/abc\/(.+)$ $1\/xyz\/$2

So http://blah.com/abc/image.jpg becomes http://blah.com/xyz/image.jpg