Parsing PL / SQL Code to Check Syntax and Semantic Errors
Please consider the following scenario:
- I have a Text Area and a Button in the UI.
- The user enters a PL / SQL block in the Text Area .
- When the user clicks the button , I want to check for syntax and semantic errors in this block without executing .
I would really like a solution where I no longer need to install anything.
Environment Information:
- Java 1.6.31
- Oracle Database 11g
source to share
SQL> explain plan for select from dual;
explain plan for select from dual
*
ERROR at line 1:
ORA-00936: missing expression
SQL> explain plan for select * from dual;
Explained.
or
declare
c integer := dbms_sql.open_cursor();
begin
dbms_sql.parse(c, 'select * emp', dbms_sql.native);
dbms_sql.close_cursor(c);
end;
/
Error at line 1
ORA-00923: FROM keyword not found where expected
ORA-06512: at "SYS.DBMS_SQL", line 1053
ORA-06512: at line 4
or click http://www.softpedia.com/get/Internet/Servers/Database-Utils/EasySQL-Checker-for-Oracle.shtml
source to share
This is tricky and you will probably have to consider all sorts of possibilities and make sure that users don't destroy your database by granting very few rights, etc. You make sense.
This is not a complete solution, but it points in the right direction.
You can try to build it into errors CREATE or REPLACE PROCEDURE
and samples. Something like that:
declare
text_area varchar2(4000) := 'declare x number; begin xy := x + 1; end;';
begin
execute immediate 'create or replace procedure DUMMY#__ IS BEGIN null; begin '|| text_area ||' end; END;';
exception
-- see comment below about error handling
when others then
-- signal yourself it went wrong
RAISE;
end;
The problem with anonymous block would be that it is executed immediately. But this way you are only performing the compilation procedure. If you have multiple users, you probably want to create different procedure names, or you want to create different schemas even to avoid conflicts. As I said, this is not a complete solution, but just an indication of some direction.
The "ORA-23344 compilation error" can be used to retrieve compilation errors.
source to share
I think you need a PL / SQL interpreter. He can check the given almost syntax of the code. If you want to get full check, it is not easy, you need to check DB objects, properties. permissions, etc.
You can create a PL / SQL interpreter to enforce your requirements, or you can try this parser: https://github.com/porcelli/plsql-parser
By the way, I will have problems with "executing immediate" -collections.
source to share
If you have access to a running Oracle system containing these tables, you can use dbms_sql.parse()
to check if a given chunk of SQL is actually valid or not.
Regular DML statements are not executed through parse()
, but DDL will be executed immediately. This way you can check if SQL is not a DDL operation (or better, only allow certain statements).
Note that if the database you are connecting to does not contain tables used in SQL, it parse()
will throw an error, even if the statement is syntactically correct.
source to share