How I can verify my PL/SQL syntax

Sorry my English is bad. I hope you can get what I want.

I have lots of *.sql files that I want to write a program to compile them and inform me if there is any issues (problems or mistakes).

One of my friends writes an IDE for java, as I remember he use javac to generate the error codes.开发者_开发问答 On the other hand I've seen the IDEs Visual Studio or Netbean tell you automatically if there are errors. So now I want to know any one has any idea how you can do that with sql files?

In other words I want to write an Editor for SQL files(PL/SQL) that compile my code and tell me what is my error.

This problem raise up when I try to compile all of them in SQL PLUS, it's so boring.

Please help me...

---

A .sql file could contain many different things - DDL, SQL queries, DML, PL/SQL anonymous blocks, as well as CREATE commands for views and stored procedures/functions/packages.

You need to know what are in those .sql files. If you just run them blindly in SQL*Plus, you don't know what they might do - I could give you the set of .sql scripts in my home folder, and you'd find that your database would be in a pretty bad state if you just ran them randomly - some scripts create/modify/drop tables, or delete or modify data, some scripts COMMIT their changes (so issuing a ROLLBACK after running the script won't help you), other scripts start or stop the database :)

If you know that all your .sql scripts contain nothing but CREATE commands for views or stored procedures, function and/or packages, you can just run them all from the SQL*Plus command line - you can generate a script quite easily that runs them all one after each other - then check the USER_OBJECTS/ALL_OBJECTS/DBA_OBJECTS for anything where STATUS = 'INVALID' and query USER_ERRORS for any compiler errors. Remember, however, even this approach is destructive because it will overwrite any existing stored procedures etc. that were in the database you run them in.

---

SQL*Plus can be run on the command line. Therefore you can use it in a manner similar to your friend's use of javac.

> sqlplus username/password@connection_identifier @scriptToExecute.sql

Remember that your actions can have consequences, so you will want to implement rollback for sql and perhaps temporary naming for ddl/dml commands.

Or alternatively, download Oracle's free SQL Developer tool that already does all that.