ddl in code or script

Question

I need to run ddl commands (e.g. CREATE, DROP) during application flow (not just one-time initialiation). Is it better to write one big .sql script to do it and run the script from code using a command line, or is it preferred to run each sql command separately from my java/c# code?

one big .sql pros:

• script can be easily reused by dba, and by developers during development
• easier to edit large sql in a native editor

one big .sql cons:

• My application will executes an external .sql script via command line. If anything fails, I have no way to know where exactly it failed (either than parsing logs) or take a specific application action after a failure of a particular command

Answer 1

I would use prcedural language API (package or some set of functions and procedures) on DB side for creating and dropping these tables.

It allows you to execute these in creator privileges, and avoids potential security risk on the application side.