7

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?

Ted Elliott
  • 3,415
  • 1
  • 27
  • 30

3 Answers3

7

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!

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
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.

alextansc
  • 4,626
  • 6
  • 29
  • 45
Carl
  • 2,483
  • 4
  • 28
  • 30
0

To expand on Tony's answer, you can use an Anonymous block to do this, you will just have to make sure the string is working as you expect. This is a DOWN AND DIRTY example, pretty much splitting on the ; and creating the block.

using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace ODPSample
{
    class Class1
    {

        private static string formatAnonBlock(string userData)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Begin ");
            string[] statements = userData.Split(';');
            foreach (string s in statements)
            {
                if (s.Length > 0)
                {
                    sb.AppendFormat(" EXECUTE IMMEDIATE '{0}';", s.Replace("'", "''"));
                }
            }
            sb.Append(" END ; ");
            return sb.ToString();
        }
        static void Main(string[] args)
        {
            Console.WriteLine("Demo: Anon Block");

            // Connect
            string connectStr = "User Id=scott;Password=tiger;Data Source=database";

            string userInputtedSQL;
            userInputtedSQL = "Create table ABC(val varchar2(50)); insert into ABC values('123');insert into ABC values('567');";

            string anonBlock;
            anonBlock = formatAnonBlock(userInputtedSQL);
            Console.WriteLine(anonBlock);

            OracleConnection connection = new OracleConnection(connectStr);
            OracleCommand cmd = new OracleCommand(anonBlock, connection);


            try
            {
                connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            Console.WriteLine("Done");
        }
    }
}
Harrison
  • 8,970
  • 1
  • 32
  • 28