IsSQLValid function-check your queries before you run them

As I announce in my previous topic Query by Form (QBF) for Apex in dynamic SQL execution (regardless SQL is "hidden as a string" in some PLSQL part or composed from several string parts to one SQL command) checking of SQL before execution is something that I really often use. In this way any exception is prevented and sent through p_error parameter as regular varchar message.

Implementation

Implementation is compound of one function which should be created in any schema with create procedure privilege:

CREATE OR REPLACE  FUNCTION isSqlValid (
           p_Select IN  VARCHAR2
         , p_Error  OUT VARCHAR2
        ) RETURN BOOLEAN
IS
/*------------------------------------------------------------------------------------
 NAME    : isSqlValid.sql

 PURPOSE : Functions which parse SQL and doesn't execute it. Good for checking
           regularity of SQL

 Date    : 11.02.2004.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : create in some "tools" like schema, create public synonym and grant execute
           to public so any user can use it!

 Changes (DD.MM.YYYY, Name, CR/TR#):
------------------------------------------------------------------------------------ */
  vCursor NUMBER;
BEGIN
  p_Error := 'Unhandled exception. Not regular function output. Please check isSQLValid function code!!';
  vCursor := DBMS_Sql.open_cursor;
  DBMS_Sql.parse(vCursor, p_Select, DBMS_Sql.native);
  DBMS_Sql.close_cursor(vCursor);
  p_Error := null;
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      p_Error := SQLERRM;
      IF DBMS_Sql.is_open(vCursor) THEN
        DBMS_Sql.close_cursor(vCursor);
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
         NULL;
    END;
    RETURN FALSE;
END isSqlValid;
/

After that create public synonym and grant execute to public. In mine case owner schema is damirv:

SQL> grant execute on damirv.issqlvalid to public;

Grant succeeded.

SQL> create or replace public synonym issqlvalid for damirv.issqlvalid ;

Synonym created.

SQL>

isSqlValid in action

Let us first show several SQLs how they look in ordinary SQL prompt. For this example only first one is intentionally correct and all others has some errors:

SQL> select 1 from dual;

1
----------
1

SQL> selct 1 from dual;
SP2-0734: unknown command beginning "selct 1 fr..." - rest of line ignored.
SQL> select * from sys.v$database;
select * from sys.v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create or replace public synonym emp for hr.emp;
create or replace public synonym emp for hr.emp
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create table xxx(c1 varchar2(4200));
create table xxx(c1 varchar2(4200))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL>

Now let us see how isSqlValid function deals with all kinds of error:

declare
  o_msg varchar2(1000);
BEGIN
  dbms_output.put_line('select 1 from dual');
  if isSQlValid('select 1 from dual',o_msg) then
    dbms_output.PUT_LINE (chr(9)||'OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('"slct" is instead of "select" at the beggining');
  if isSQlValid('selct 1 from dual',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('grant select is missing');
  if isSQlValid('select * from sys.v$database',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('Not enough privileges ');
  if isSQlValid('create or replace public synonym emp for hr.emp',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('Bad DDL ');
  if isSQlValid('create table xxx(c1 varchar2(4200))',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;
END;
/

And here is the result:

select 1 from dual
OK
"slct" is instead of "select" at the beginning
     NOT OK: ORA-00900: invalid SQL statement
grant select is missing
     NOT OK: ORA-00942: table or view does not exist
Not enough privileges
     NOT OK: ORA-01031: insufficient privileges
Bad DDL
     NOT OK: ORA-00910: specified length too long for its datatype

PL/SQL procedure successfully completed.

SQL>

As you can see wide range of SQL errors are predicted. Regardless I have in mine 99.99% of cases used this function for "select type" of query (as you can see p_select is the name of the first function parameter), you see that DDL are also supported.

DDL in action

According your mails I have reedit this blog and post easy DDL test, which work as well. First test that create table is working:

SQL> declare
  2    o_msg varchar2(1000);
  3  begin
  4    if not isSqlValid('create table xx (c1 varchar2(3))',o_msg) then
  5      dbms_output.put_line(o_msg);
  6    else
  7      dbms_output.put_line('OK!');
  8    end if;
  9  end;
 10  /
OK!

PL/SQL procedure successfully completed.

SQL>

Now let us see if this object exists:

SQL> desc xx
ERROR:
ORA-04043: object xx does not exist

So far so good. Let's wide example and try to run IsSqlValid against previously created xx table (now should be some errors raised!):

SQL> create table xx (c1 varchar2(3));

Table created.

SQL> declare
  2    o_msg varchar2(1000);
  3  begin
  4    if not isSqlValid('create table xx (c1 varchar2(3))',o_msg) then
  5      dbms_output.put_line(o_msg);
  6    else
  7      dbms_output.put_line('OK!');
  8    end if;
  9  end;
 10  /
ORA-00955: name is already used by an existing object

PL/SQL procedure successfully completed.

SQL>

Work as hell!

But I still haven't tested if DDL in isSqlValid function has any influence on any previously started transaction (as DDL really do if you perform them normally). Event thought I do think it should not have in percussion, such a task use on your risk...

Cheers!