How to split Oracle sql statements for ADO.NET

.net, ado.net, database, oracle

Solution

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!

Problem

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?

Original source