Multiple DROP VIEW/CREATE VIEW in one C# command

[maxima120]

I use devart for Oracle in C# asp.net server code.

normally if I want to run multi-statement I do:

Code: Select all

var s = "BEGIN INSERT...; INSERT...; END;"
var c = new OracleCommand(s, cnn);
c.ExecuteNonQuery();

Now I need to drop 15 tables for each users, 100 user. 1500 tables.

When I tried the same BEGIN DROP..; DROP..; END; approach I got

pls-00103: Encountered the symbol "DROP" when expecting one of the following...

Running it one by one is seriously bad idea. I had a lots of problem with devart in the course of 8 yrs.. I wont be surprised that its not possible.

But perhaps someone knows how to do it..

[Pinturiccio]

This is the designed behaviour. Oracle does not allow using DROP statements in a PL/SQL block.

There are two workarounds:
1. You can use dynamic SQL statements. Your query will be like the following:
BEGIN execute IMMEDIATE 'DROP TABLE table1'; ... ;execute IMMEDIATE 'DROP TABLE tableN'; END;

2. You can use the OracleScript class instead of OracleCommand. With the OracleScript class you don’t need a PL/SQL block - just delimit your queries with semicolons. For more information, please refer to https://www.devart.com/dotconnect/oracl ... cript.html

Here is a small example of code using OracleScript for dropping several tables:

Code: Select all

static void Main(string[] args) {
	OracleConnection conn = new OracleConnection("your connection string");
	conn.Open();

	OracleScript script = new OracleScript("DROP TABLE table1; ... ;DROP TABLE tableN;", conn);
	script.Execute();
	conn.Close();
}

[maxima120]

This worked. Thank you!