How I can verify my PL/SQL syntax

Question

Hi all Sorry my English is bad.I hope u can get what I want.

I have lots of *.sql files that i want to write a program to compile them and if there is any issue(problem or mistake) report me.

One of my friend write an IDE for java,as I remember he use javac to generate the codes error,in other hand maybe u see when u try to write code in a Visual stadio or Netbean the IDE generate errors for u.so now I want to know any one have any idea how I can do it for my sql files?

In other mean I want to write a 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...

Answer 1

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.

Answer 2

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.