3

Please consider following scenario:

  • I have a Text-Area and a Button on UI.
  • User will enter a PL/SQL block in Text-Area.
  • When user will press Button, I want to check for syntax and semantic errors in that block without executing it.

I would really like a solution where I don't have to install anything more.

Environment information:

  1. Java 1.6.31
  2. Oracle database 11g
VishalDevgire
  • 4,232
  • 10
  • 33
  • 59
  • is that a requirement for SO to solve? – SMA Nov 24 '14 at 12:26
  • 1
    http://stackoverflow.com/q/3970643/85371; You can always ask for an explain plan. That won't be without server load, but it won't execute the statement either (and check the validity of all names referenced) – sehe Nov 24 '14 at 12:27
  • 1
    unless you use some 3rd party library, you will have to write your own custom rules and apply them over the query string – MihaiC Nov 24 '14 at 12:29
  • @MihaiC I thought so but i will stick around just in case. :) – VishalDevgire Nov 24 '14 at 12:31
  • 3
    Maybe the discussion on [akstom](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3648254441315) will help? – mikron Nov 24 '14 at 12:52
  • Are you parsing anonymous blocks or potentially create procedure/function statements? It's [almost impossible to disable PL/SQL](http://stackoverflow.com/questions/9182315/how-to-disable-pl-sql-in-oracle-queries), but you could determine the type first. It'll be very difficult to prevent people executing DDL, not matter what you do. – Ben Nov 24 '14 at 13:44

4 Answers4

3
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 hit http://www.softpedia.com/get/Internet/Servers/Database-Utils/EasySQL-Checker-for-Oracle.shtml

tomasb
  • 1,663
  • 2
  • 22
  • 29
2

It is difficult and you probably have to consider all kind of possiblities and make sure that the users do not wreck you database by making sure to grant very little rights etc. You got the point.

This is not the full solution but it points you into the right direction.

You could try to embed it into a CREATE or REPLACE PROCEDURE and fetch errors. Something like this:

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 trouble with an anonymous block would be that it is right away executed. But that way you only execute a createion of the procedure which does the compile. If you have several users you probably want to create different procedure names or you want to create different schemas even to prevent conflicts. As I said this is not the full solution but just pointing into some direction.

"ORA-23344 success with compilation error" can be used to fetch compile errors.

hol
  • 8,255
  • 5
  • 33
  • 59
1

I think you need a PL/SQL interpreter. It can check the given almost the syntax of the code. If you want to get full check, it is not easy, you have to check the DB objects, properties. permissions etc.

You can create yourself a PL/SQL interpreter to ensure you requirements, OR you can try this parser: https://github.com/porcelli/plsql-parser

By the way, I will be in trouble with "execute immediate" -calls.

danka
  • 51
  • 5
1

If you do have access to a running Oracle system which contains the tables in question, you can use dbms_sql.parse() to check if a given piece of SQL is valid or not.

Regular DML statements are not execute through parse(), but DDL will be executed immediately. So you might want to check if the SQL is not a DDL statement (or better, only allow certain statements to begin with).

Note that if the database you are connecting to, does not contain the tables used in the SQL, parse() will throw an error even if the statement is syntactically correct.