How can I execute multiple Oracle SQL statements with .NET

Question

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?

Answer 1

Why do you believe that the SQL*Plus 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 SQL*Plus 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.

Answer 2

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.

Answer 3

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.