CODEWITHSUNDEEP
.netsqloracle-database
devdimi
devdimi

Reputation: 2462

How can I execute multiple Oracle SQL statements with .NET

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?

Upvotes: 2

Views: 5596

Answers (3)

erikkallen
erikkallen

Reputation: 34421

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.

Upvotes: 1

Mark Brady
Mark Brady

Reputation:

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.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231851

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.

Upvotes: 2

Related Questions