SQL Parsing In Oracle PL/SQL

Lots of time, when you are thinking of creating a utility, we need to dig deep inside so that our utility works in a smart way.

 

I was developing a utility where user would pass the sql statement in their screen & I store that sql query in database. This query is then used by some other procedure and it is called using windows scheduler. The output of query is then saved & a file is created at some particular location.

 

So my main challenge was to allow the user to enter the valid query.  I mean the syntax should be valid (i.e - tables or columns used is query should be present in database). So I was forced to parse the query and check whether that is valid or not without executing the query. 

 

I have created a procedure in Oracle PLSQL which will accept the query as parameter and will output 1 or 0 based on valid parsing. It is simple but my purpose of writing is to let others know that its possible.  

create or replace procedure VerifyQuerySyntax(p_query in clob, isvalid out integer) is
c number;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (c, p_query, DBMS_SQL.Native);
DBMS_SQL.CLOSE_CURSOR (c);
DBMS_OUTPUT.put_line('Correct' );
isvalid:=1;
--Statement parsed successfully
EXCEPTION
WHEN OTHERS THEN
--statement invalid
DBMS_OUTPUT.put_line('Error : ' || Sqlcode||' - '|| sqlerrm );
DBMS_SQL.CLOSE_CURSOR(c);
isvalid:=0;

END VerifyQuerySyntax;

In the above procedure, you can see the I am passing first parameter as query which is of data type CLOB.(query length could be anything)  and second parameter is a out parameter of type int.

 

Note: This is a PL SQL syntax, so you may find it different that what we write in MSSQL.

1. Write your code within try catch block.
2. I have opened a cursor.
3. Passed the  sql statement as string to one of the in built method called PARSE with in DBMS_SQL package. 

DBMS_SQL.PARSE(c,statement,language_flag)

Parameters

c: input argument of type INTEGER that specifies the cursor ID of an open cursor.

statement : The SQL statement to be parsed.

language_flag : This argument is provided for Oracle syntax compatibility. Use a value of 1 or DBMS_SQL.native.

3.Close the cursor.

4.Set the output parameter flag to 1  i.e - isvalid,

If query is successfully parsed then it will set the flag and will come out of try block, But if there is any problem with the sql statement passed then it will move to catch block & set the output parameter to 0 i.e - is valid

 

If you want you can log it in console as well.

 

Lets execute & test it. Let say, I have Customer table in my database, 

Declare
isValid integer;
query varchar2(32767) := 'SELECT * FROM Customer';
Begin
VerifyQuerySyntax(query , isValid );
dbms_output.put_line( 'isValid : '||isValid );
End;

Output: 

Correct

isValid : 1 

Declare
isValid integer;
query varchar2(32767) := 'SELECT * FROM Customer123';
Begin
VerifyQuerySyntax(query , isValid );
dbms_output.put_line( 'isValid : '||isValid );
End;

Output: 

Error : -942 <> ORA-00942: table or view does not exist

isInvalid : 0 

 

I hope this will be helpful when you wish to check your Sql query syntax.Please share your comments and thoughts for this article whether it's good or bad. At the end Sharing is valuable no matter what.