validating a plsql block

Question

I am preparing a screen in which user will input an anonymous block (declare ... begin ... end) of plsql, and i will save it's block into a file an run it when it is necessary. But I want to validate the user's input against my database whether it has syntax errors or not before execution. when i googled it i found that I can use antlr, but i could not found any working sample. can anyone show me a sample, I am open for other solutions within the java and plsql context.

Are you sure you want to allow users to run commands in your DB ? how will you prevent him from spoiling your DB (intentionally or by mistake) ? — A.B.Cade
– A.B.Cade, Commented Feb 15, 2012 at 10:05
my application would be used by system administrators. misusage is not a concern for now. — dursun
– dursun, Commented Feb 15, 2012 at 11:04
@dursun Systems administrators are just as fallible as any other human being. One little typo and it could all go horribly wrong ... — Burhan Ali
– Burhan Ali, Commented Feb 16, 2012 at 12:02
@BurhanAli ok you right, But this is what the system administrators need. they don t want to connect to DB from any other tool. they are taking all the responsibility for their own actions. typo is on their own responsibility. How can i convince them to use db tools like toad or sqlplus. do you have any experience on such issues. — dursun
– dursun, Commented Feb 17, 2012 at 7:17

Vincent Malgrat · Accepted Answer · 2012-02-15 11:05:41Z

3

You can parse the plsql command with DBMS_SQL:

SQL> CREATE OR REPLACE PROCEDURE parse(p_command VARCHAR2) AUTHID CURRENT_USER IS
  2     l_cursor INTEGER;
  3  BEGIN
  4     l_cursor := dbms_sql.open_cursor;
  5     dbms_sql.parse(l_cursor, p_command, dbms_sql.native);
  6     dbms_sql.close_cursor(l_cursor);
  7  EXCEPTION
  8     WHEN OTHERS THEN
  9        dbms_sql.close_cursor(l_cursor);
 10        RAISE;
 11  END;
 12  /

Procedure created

SQL> exec parse ('BEGIN NULL;END;');

PL/SQL procedure successfully completed

SQL> exec parse ('BEGIN incorrect_statement;END;');

begin parse ('BEGIN incorrect_statement;END;'); end;

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'INCORRECT_STATEMENT' must be declared

Be careful with what you parse though: DDL will be executed on parse (!).

answered Feb 15, 2012 at 11:05

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dursun Over a year ago

@RogerCornejo how would it be dangerous

Roger Cornejo Over a year ago

@dursun - basically, the code will run anything, that could include SQL Injection ("a hackers gold mine").

marciel.deg Over a year ago

Not working for 'begin :x := y; end;' but works for 'declare x number; begin x := y; end;' in Oracle 11.

Collectives™ on Stack Overflow

validating a plsql block

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related