Multiple DROP VIEW/CREATE VIEW in one C# command

Multiple DROP VIEW/CREATE VIEW in one C# command

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

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

Postby 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/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleScript.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();
}
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

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

Postby maxima120 » Wed 10 Aug 2016 11:09

This worked. Thank you!
maxima120
 
Posts: 11
Joined: Tue 15 Mar 2016 14:19


Return to dotConnect for Oracle