Oracle PL/SQL: Testing SQL Select Statements

I've just developed this relatively simple SQL function (checkSQL) that takes a single string as it's parameter and attempts to execute the SQL against the current database. It works by looking through the and identifying the parameters and then attempting to guess a valid value for the parameter to allow the SQL to execute.

The aim isn't to return records, it's just to test that the SQL is valid.

We use this to check to make sure our reports (in SSRS) will work against a newly regenerated Noetix schema, I'm hoping to find some time to expand on this into a proper reporting regression testing suite but this will take quite a while.

Here is the SQL;

DECLARE
  FUNCTION checkSQL(v_SQL IN VARCHAR2) RETURN VARCHAR2 AS
    v_TestSQL       VARCHAR2(30000) := REPLACE(UPPER(v_SQL),
                                               ' 00:00:00',
                                               '');
    v_Result        NUMBER;
    v_Parametername VARCHAR2(50);
  BEGIN
    WHILE INSTR(v_TestSQL, ':') > 0 LOOP
      v_Parametername := SUBSTR(v_TestSQL, INSTR(v_TestSQL, ':'), 50);
      FOR v_CharNo IN 0 .. 255 LOOP
        IF INSTR(':ABCDEFGHIJKLMNOPQRSTUVWXYZ_', CHR(v_CharNo)) = 0 THEN
          v_ParameterName := REPLACE(v_Parametername, CHR(v_CharNo), '@');
        END IF;
      END LOOP;
      v_ParameterName := SUBSTR(v_ParameterName,
                                1,
                                INSTR(v_ParameterName, '@') - 1);
      dbms_output.put_line(v_ParameterName);
   
      IF (v_ParameterName LIKE '%START%' Or v_ParameterName LIKE '%END%' Or
         v_ParameterName LIKE '%DATE%') THEN
        v_TestSQL := REPLACE(v_TestSQL, v_ParameterName, '''01-JAN-2000''');
      ELSE
        v_TestSQL := REPLACE(v_TestSQL, v_ParameterName, '''0''');
      END IF;
    END LOOP;
 
    EXECUTE IMMEDIATE 'SELECT 1 FROM (' || v_TestSQL ||
                      ') UNION SELECT 1 FROM DUAL'
      INTO v_Result;
 
    RETURN 'OK';
  EXCEPTION
    WHEN OTHERS THEN
      RETURN SQLERRM;
  END;
BEGIN
  -- Test statements here
  :Test := checkSQL('SELECT SYSDATE FROM DUAL');
END;

C# Programming Test #1

Interview test
Ever needed a simple test to get that interviewee talking? The point of this Knol is to provide you with a small code sample to discuss with an applicant.

Given the size of the world and the number of companies in it it's pretty unlikely that either a) whoever you give this test to (as a company) will have read this, or b) if you memorise this (as an interviewee) that this will be the test the company uses.

It's just a bit of fun that comes after I realised that I'd written some technically correct but logically incorrect code. Hope it will be useful to someone someday!

Here is the code snippet;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace SimpleFileRenamer
{
  class Program
  {
    static void Main(string[] args)
    {
      string[] filePaths = Directory.GetFiles(Directory.GetCurrentDirectory());
      if (filePaths.Count() > 0)
      {
        StreamReader file = null;
        string line;
        try
        {
          file = new StreamReader(Directory.GetCurrentDirectory() + @"\Settings.txt");
          while ((line = file.ReadLine()) != null)
            foreach (string OldName in filePaths)
            {
              string[] sRename = line.Split('|');
              string NewName = Path.GetFileName(OldName).Replace(sRename[0], sRename[1]);
              if (Path.GetFileName(OldName) != NewName)
                Directory.Move(OldName, 
                  Path.GetDirectoryName(OldName) + @"\" + NewName);
            }
        }
        finally
        {
          if (file != null)
            file.Close();
        }
      }
    }
  }
}


The first thing to say is that this code compiles and runs. This test isn't about asking people how much they know of the C# syntax and how easily they could write applications using Notepad to compile later. If you like people like that then you're probably after a much longer series of tests!

Question One: What does it do?

A fairly good answer would be: Renaming files based on a series of rules read in from a file.

That's an excellent high-level description that you could give to anyone who would instantly know what was going on. Of course Software Engineers tend to answer in a more complicated way so you can expect some mention of "in the same directory", "using old/new values in the file separate by | (pipe)", "renaming only files where the rules in the settings file require it", etc.

As a quick example of how it works if our Settings.txt file contained two rows:
Old|
_|

And you had the files; Execution_Test.txt and OldData.txt in your directory then after running the application these would be changed to ExecutionText.txt and Data.txt.

Simple eh?

Now here are a (non-exhaustive) list of things that could be raised during a discussion;

• Lack of comments. Always a good one for the interviewee to raise!
• There is no error-checking so errors drop right out with the ugly "... has stopped working" windows error message. This can happen in multiple places; Reading from Settings.txt (does it even exist for a start!), renaming a locked file, invalid text in Settings.txt, etc.
• Variable naming. Very inconsistent; file, NewName, sRename, etc.
• BUG: The application will try and rename itself and the settings.txt file - ideally both of these should be ignored.
• BUG: If you have two entries in Settings.txt (say Old|New, and Yes|No) then if you have a file which combines the two entries (OldYes.txt, NewYes.txt, etc) then the application will crash as when it tries to do the second rename the file will no longer exists - the application should read settings and loop files rather than reading files and looping settings.
• BUG (Obscure): If you are simply changing case (Old > old) then Directory. Move will raise an error, you need to use a temporary file in this special case.

I'm sure there are more (I should really have written these down as I thought of them rather than trying to remember a few days later), let me know all the ones I've missed by commenting below.