2

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?

EndangeredMassa
  • 17,208
  • 8
  • 55
  • 79
devdimi
  • 2,432
  • 19
  • 18

3 Answers3

2

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I already tried this - when error occurs sqlplus just carries on with the rest of the script, which is undesirable. sqlplus does not return error information, no process exit code, nothing I guess it can generate log files, but do I have to parse a log file just to find out what went wrong? – devdimi Feb 09 '09 at 15:54
  • You should be able to use the WHENEVER SQLERROR command to control that behavior. See expanded answer. – Justin Cave Feb 09 '09 at 15:59
  • OK, WHENEVER SQLERROR EXIT FAILURE will do the job Thanks for the fast response! I guess oracle takes some time to get used to – devdimi Feb 09 '09 at 16:07
2

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.

  • Yes it works, I just didn't know about the WHENEVER SQLERROR command. It actually makes exactly what I need. I can't use anonymous procedure because of the automatic commits. – devdimi Feb 09 '09 at 17:34
  • DDL automatically commits no matter where it is called from. It's the nature of DDL and Oracle. No two ways about it. –  Feb 09 '09 at 17:52
1

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.

erikkallen
  • 33,800
  • 13
  • 85
  • 120
  • the problem is that we also have DDL, which I think can't be executed in anonymous stored procedure But I will try that too, thanks. – devdimi Feb 09 '09 at 16:08