As a part of our build process we want to execute SQL Scripts consisting of DDL and DML statements against a fresh database instance.
ADO.NET Connection/Command can't handle this without parsing and splitting the scripts.
The sqlplus command line utility can execute scripts only interactively, and isn't suited for batch usage.
What am I missing? How can one execute sql scripts when using oracle?
Why do you believe that the SQLPlus utility isn't suited for batch usage? It's pretty common to use it for running these sorts of scripts-- you can pass the script to SQLPlus when you invoke it if you'd like, i.e.
sqlplus scott/tiger@someDatabase @someScript.sql
That is a pretty common way of deploying builds.
If the problem is with the way SQL*Plus is handling errors, you can simply add the line
WHENEVER SQLERROR EXIT SQL.SQLCODE
to abort and throw the Oracle error number that was encountered. The documentation for the WHENEVER SQLERROR command provides a number of other options as well.
Devdimi,
I agree with Erik K.
And yes you can include DDL in an anonymous block.
DECLARE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE foo';
END;
Remember DDL does a commit BEFORE and AFTER it runs. So as part of a transaction it kinda sucks.
I think you can do it if you wrap the statement inside DECLARE/BEGIN/END. I don't have access to Oracle anymore so I can't test it, though. Example:
DECLARE
BEGIN
INSERT INTO ...;
UPDATE something ...;
END;
Since you want to execute DDL, use EXECUTE IMMEDIATE.
