How to validate the syntax of a Oracle dynamic SQL before execution ?

Problem Statement:
You want to validate the syntax of a SQL before execution.

Solution: The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. We can leverage DBMS_SQL.PARSE function to validate the syntax.

We will first define a function to accept the SQL statement as the parameter and parse the SQL statement.

/*
 * ---------------------------------------------------------------------------
 *  Function : check_syntax
 *    Input  : sql statement
 *   Output  : Number
 * ---------------------------------------------------------------------------
 */
 
 FUNCTION check_syntax
    ( p_query IN CLOB )
  RETURN INTEGER
  IS
  l_cursor NUMBER := dbms_sql.open_cursor;
  BEGIN
     BEGIN
        DBMS_SQL.PARSE (l_cursor, p_query, DBMS_SQL.native);
     EXCEPTION
        WHEN OTHERS THEN
            DBMS_SQL.CLOSE_CURSOR (l_cursor);
            RETURN -1;
     END;
     DBMS_SQL.CLOSE_CURSOR (l_cursor);
     RETURN 0;
  END check_syntax;

In the above function, l_cursor, is opened using the open_cursor function. The given SQL statement is then parsed using DBMS_SQL.PARSE which retunr -1 if there is a Syntax issue with the SQL. We will now run the function by passing a sample SQL.

Example 1 : Passing Valid SQL to the function

<p>DECLARE
l_rc   VARCHAR2(100);
l_sql_stmnt CLOB;
BEGIN
l_sql_stmnt := 'SELECT 1 FROM DUAL' ;
l_rc   := my_package.check_syntax(l_sql_stmnt);
IF l_rc = 0 THEN
  dbms_output.put_line(' ** SQL Validation Pass - ' || l_sql_stmnt);
ELSE
  dbms_output.put_line(' ** SQL Validation Fail - ' || l_sql_stmnt);
END IF;
END;</p>

Output

<p> ** SQL Validation Pass - SELECT 1 FROM DUAL</p>

Example 2 : Passing In-Valid SQL to the function

<p>DECLARE
l_rc   VARCHAR2(100);
l_sql_stmnt CLOB;
BEGIN
l_sql_stmnt := 'SELECT 1 FROM DUALED' ;
l_rc   := my_package.check_syntax(l_sql_stmnt);
IF l_rc = 0 THEN
  dbms_output.put_line(' ** SQL Validation Pass - ' || l_sql_stmnt);
ELSE
  dbms_output.put_line(' ** SQL Validation Fail - ' || l_sql_stmnt);
END IF;
END;</p>

Output

<p> ** SQL Validation Fail - SELECT 1,2,4 FROM DUALED</p>