How to split Oracle sql statements for ADO.NET

Question

What is the proper way to split up SQL statements to send to an Oracle ADO.NET client? For instance, lets say you have the following code in a text file and want to execute these statements:

CREATE TABLE foo (bar VARCHAR2(100));
INSERT INTO foo (bar) VALUES('one');
INSERT INTO foo (bar) VALUES('two');

I believe trying to send all those in one Command will cause Oracle to complain about the ";". My first thought would be to split on ";" character, and send them one at a time.

But, Stored procedures can contain semi-colons as well, so how would I make it so the split routine would keep the whole stored proc together? Does it need to look for begin/end statements as well, or "/"?

Is there any difference in these respects between ODP.NET and the Micrsoft Oracle Provider?

Answer 1

Without the DDL, you could create an anonymous PL/SQL block by surrounding the statements with BEGIN and END:

BEGIN
  INSERT INTO foo (bar) VALUES('one');
  INSERT INTO foo (bar) VALUES('two');
END;

To perform DDL (like CREATE TABLE) you would need to use dynamic PL/SQL:

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE foo (bar VARCHAR2(100))';
  EXECUTE IMMEDIATE 'INSERT INTO foo (bar) VALUES(:v)' USING 'one';
  EXECUTE IMMEDIATE 'INSERT INTO foo (bar) VALUES(:v)' USING 'two';
END;

The INSERTS are also dynamic, as the table does not exist prior to running the block and so it would fail to compile.

NOTE: This would be an unusual requirement: applications should not normally be creating tables!

Answer 2

A company called devart (www.devart.com) publish a library called dotConnect for Oracle.

This library contains a class called OracleScript which has the ability to separate a SQL script containing multiple statements.