Run multiple commands in one ExecuteScalar in Oracle

Question

I have a batch of sql statements such as ...

insert into.... ; insert into.... ; delete .........;

etc

When i try to execute them against oracle it gives me this error (ORA-00911 Invalid Character)

now i can understand that this is because of the semicolon between the statements, i tried this on SQL Server and it worked but in Oracle no luck so far.

Is there a way to run multiple statements against oracle by using the ExecuteScalar or some other function?

---

DUPLICATE: How can I execute multiple Oracle SQL statements with .NET

Romanist

Answer 1

Try wrapping with a BEGIN..END

BEGIN insert into.... ; insert into.... ; delete .........; END;

Intrepid

worked for me in SQL Developer. After I remembered the ; after END. – Haema

Answer 2

trying the BEGIN END did not work for me.

What i did was make a new method that given a connection(i try to minimize my open connections) it splits the statements using the ; as a delimiter and runs each one seperatly

    private void ExecuteSql(string statements, IDbConnection conn)
    {
        IDbCommand cmd = conn.CreateCommand();
        string[] commands = statements.Split(new string[] { ";\r\n", "; ", ";\t", ";\n" }, StringSplitOptions.RemoveEmptyEntries);
        foreach (string c in commands)
        {
            cmd.CommandText = c;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }

        cmd.Dispose();
    }

My inspiration came from this post after Petros told me about it

PS you may need to change it according to your needs, in my case i require the connection to be open, and closed accordingly if something happens from the caller.

Romanist