How can I execute multiple Oracle SQL statements using .NET

Question

As part of our build process, we want to execute SQL scripts consisting of DDL and DML statements against a new database instance.

The ADO.NET connection / command cannot handle this without parsing and separating scripts.

The sqlplus command line utility can only execute scripts interactively and is not suitable for use in packages.

What am I missing? How can i execute sql scripts using oracle?

Answer 1

Why do you think SQLPlus is not suitable for use in packages? It is quite common to use it to run these types of scripts - you can pass the script to SQLPlus when you call it if you want, i.e.

 sqlplus scott/ tiger@someDatabase @someScript.sql 

This is a fairly common way to deploy assemblies.

If the problem is with the way SQL * Plus handles errors, you can simply add a line

 WHENEVER SQLERROR EXIT SQL.SQLCODE 

to abort and throw the Oracle error number that was detected. The documentation for the WHENEVER SQLERROR command also provides a number of other parameters.

Answer 2

Devdimi,

I agree with Eric K.

And yes, you can include DDL in an anonymous block.

 DECLARE BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE foo'; END; 

Remember that DDL commits before and after it starts. So, as part of the transaction, this sucks.

Answer 3

I think you can do this if you complete the statement inside DECLARE / BEGIN / END. I no longer have access to Oracle, so I cannot test it. Example:

 DECLARE BEGIN INSERT INTO ...; UPDATE something ...; END; 

Since you want to execute DDL, use EXECUTE IMMEDIATE .