Multiple DROP VIEW/CREATE VIEW in one C# command

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
maxima120
Posts: 11
Joined: Tue 15 Mar 2016 14:19

Multiple DROP VIEW/CREATE VIEW in one C# command

Post by maxima120 » Tue 09 Aug 2016 18:48

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Multiple DROP VIEW/CREATE VIEW in one C# command

Post by Pinturiccio » Wed 10 Aug 2016 09:27

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
Posts: 11
Joined: Tue 15 Mar 2016 14:19

Re: Multiple DROP VIEW/CREATE VIEW in one C# command

Post by maxima120 » Wed 10 Aug 2016 11:09

This worked. Thank you!

Post Reply