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

Mitch Wheat · Accepted Answer · 2009-03-26 14:00:57Z

8

Try wrapping with a BEGIN..END

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

answered Mar 26, 2009 at 14:00

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Blandford Over a year ago

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

Community · Accepted Answer · 2017-05-23 12:25:02Z

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.

Collectives™ on Stack Overflow

Run multiple commands in one ExecuteScalar in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related