Why the semicolon couldn't place in the CommandText of a OracleCommand when C#

Question

Why the semicolon(';') couldn't place in the CommandText of a OracleCommand when C#

Just like below:

string sql = "select * from table1;";
OracleCommand oc = new OracleCommand(sql , con);
oc.CommandType = CommandType.Text;
oc.ExecuteNonQuery();

The result would turn out to be a error.

Why? Can any one tell me the reason?

Answer 1

In the comments, we've worked out the answer for you. Just so you get an official "answer", here it is from the OracleOverflow site (see below):

An OracleCommmand can run only 1 sql or plsql statement at a time. There is no need of separating multiple statements.

The semicolon is part of documentation to divide multiple statements. It is not part of the single DDL statement. For an OracleCommand there is no need to append a semicolon because it can only handle one statement at a time.

Edit: This was the original link, which is now dead: http://www.oracleoverflow.com/questions/145/running-ddl-through-an-oraclecommand-without-the-semicolon

Alternative: http://web.archive.org/web/20100806014022/http://www.oracleoverflow.com/questions/145/running-ddl-through-an-oraclecommand-without-the-semicolon

Question:

When trying to run a series of DDL commands through an OracleCommand object, I get an ORA-00911 'invalid character' error if I include the final semicolon, even though this is part of the SQL syntax in the documentation. It also means that you can only execute SQL one statement at a time, rather than executing a whole SQL script at once, which seems very odd to me.

After googling for a bit, I found that a workaround is to wrap the DDL in an EXECUTE IMMEDIATE PL/SQL statement, but again it fails if the DDL has a semicolon at the end (apart from PL/SQL create statements, which require the semicolon).

Does anyone know the reason for this behaviour? The semicolon is in the syntax documentation, so I would have assumed it is always required, but it fails if the semicolon is included...

Answer here:

An OracleCommmand can run only 1 sql or plsql statement at a time. There is no need of separating multiple statements.

The semicolon is part of documentation to divide multiple statements. It is not part of the single DDL statement. For an OracleCommand there is no need to append a semicolon because it can only handle one statement at a time.

ADDED: You can bundle more than one statement into an BEGIN ... END; Block (yes, this ends with semicolon). But this statements should be DML, DDL or PL/SQL function calls. You cannot bundle multiple SELECT statements into an BEGIN ... END; Block.

Comments:

Why OracleCommand has this limitation of only being able to run 1 statement at a time?

It's based on DbCommand specification in ADO.Net. It's not a limitation of OracleCommand only.

SqlCommand allows multiple statements in a single command, that must just be an 'addition' for SQL Server.