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;