You Asked

I have a predicate column in a table which stores a text which must be a valid WHERE clause

SEQ PREDICATE
--- ---------
1 EMPNO in ( select mgr from emp) AND ( COMISSION is null or SAL > 100 )
2 RTRIM(LAST_NAME) LIKE '%KYTE%'

How to parse the predicate in PL/SQL and determine if it is a valid WHERE CLAUSE?

What is required:
1. I need to valid whether the PREDICATE is syntactically correct?

What is not required:
1. I do not need to check if the column EMPNO, COMMISSION exists in database. This is because the columns might not exist in the same database.
2. I do not have to check if the table that the columns belong to have required grants, access for the schema

and Connor said...

You can use dbms_sql.parse, for example

SQL> declare
  2      p_query varchar2(32767) := 'select * from scott.emp where EMPNO in ( select mgr from emp) AND ( COMM is null or SAL > 100 )';
  3      l_theCursor     integer default dbms_sql.open_cursor;
  4  begin
  5      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> declare
  2      p_query varchar2(32767) := 'select * from scott.emp where EMPNO in ( select mgr from emp) AND  ( ( COMM is null or SAL > 100 )';
  3      l_theCursor     integer default dbms_sql.open_cursor;
  4  begin
  5      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 5

That is a full parse, so if you just want to know if the SQL is syntactically correct (even if the tables etc do not exist), then thats a tougher proposition.

You would need to run it through parse, and based on the error codes, decide whether you would allow the SQL to be deemed valid.

For example, if checking "where EMPNO = 10", you could do

SQL> declare
  2      p_query varchar2(32767) := 'select * from dual where EMPNO = 10';
  3      l_theCursor     integer default dbms_sql.open_cursor;
  4  begin
  5      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00904: "EMPNO": invalid identifier
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 5

and then catch a 904 as "ok", because its not necessarily a syntax error, whereas something like

SQL> declare
  2      p_query varchar2(32767) := 'select * from dual where A'' = 10';
  3      l_theCursor     integer default dbms_sql.open_cursor;
  4  begin
  5      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 5

*would* be defined as syntax error.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 19, 2017 - 11:33 am UTC

syntax check - few more doubts

Joyan Sil, February 22, 2017 - 4:47 am UTC

Thanks for the solution. I have 2 questions:

1. If a SQL statement has multiple errors, then is there a chance that the invalid syntax check will be overridden by invalid table/column error.
Say I have this query: SELECT 1 FROM DUAL WHERE EMPNO IN (100, 200;

The above query has 2 errors:
i). EMPNO is not a valid column of DUAL
ii). The right parenthesis of IN clause is not closed.

When I execute the query "SELECT 1 FROM DUAL WHERE EMPNO IN (100, 200;", what error will I get:
Will I get "ORA-00907: missing right parenthesis" or will I always get "ORA-00904: "EMPNO": invalid identifier"

In other words, in which order the parsing is done?
Will it always first check for the syntax and then check for valid table names/column names?
If this is the case, I can safely use your suggested approach, since the tables will not exist in the schema while parsing.

2. Will the below implementation scale up if I apply the function against all rows of a large table. Any performance enhancement hint?

LiveSQL link: https://livesql.oracle.com/apex/livesql/file/content_EL2HYLZDJROK64SOMELFG8ORC.html

set serverout on
create or replace function fn_check_syntax(p_predicate varchar2)
return varchar2
is
l_status varchar2(20) DEFAULT 'VALID';
l_sql varchar2(4000);
begin
l_sql := 'SELECT 1 FROM DUAL WHERE '||p_predicate;
EXECUTE IMMEDIATE l_sql;

return l_status;
exception when others then
if SQLCODE in (-902, -904)
then
return 'VALID';
else
return SQLERRM;
end if;
end;
/

create table user_predicate (seq_num number, predicate varchar2(4000));
/

insert into user_predicate (seq_num, predicate) values (1, 'EMPNO in ( select mgr from emp) AND ( ( COMM is null or SAL > 100 )');
insert into user_predicate (seq_num, predicate) values (2, 'EMPNO in ( select mgr from emp)');

SELECT seq_num, fn_check_syntax(predicate) status from user_predicate;

February 23, 2017 - 2:32 am UTC

To my knowledge we do a validity check first (ie, is it possible that this query *could* be valid), then a semantic check (ie, can it be valid on *this* database).

In terms of performance, obviously each one will likely be a hard parse, so I wouldnt be keen on running tens of thousands of them.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

syntax check - few more doubts

More to Explore

PL/SQL demos

PL/SQL docs

Parse a valid WHERE clause syntax using PL/SQL

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

syntax check - few more doubts

More to Explore

PL/SQL demos

PL/SQL docs