1

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...

benRollag
  • 1,219
  • 4
  • 16
  • 21
Amir
  • 1,919
  • 8
  • 53
  • 105

2 Answers2

5

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.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • ;)) Thanks,I know. actually recently I attend to a Configuration team that they update their software at end of week,so,we need just compile the all scripts that other developer and customer use the new source... the last manager make a batch file that put all the sql code like this: sta "x:\xxx\sss\kkk.sql" sta "xx:\xxx\sss\dddd.sql" sta "x:\xxx\sss\kss.sql" sta "xx:\xxx\sss\mmmdd.sql" ... between run this sql file we recieve error,he put "/" some times and error will solved but i think this work is so boring.in this case i wana try to write a program do this for me. any command? – Amir May 17 '10 at 07:34
  • @rima, is that another question? "/" *is* the command to execute a PL/SQL block in SQL*Plus. Testing may be boring but that's part of the job :) – Jeffrey Kemp May 17 '10 at 07:53
0

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.

blissapp
  • 1,330
  • 12
  • 19