Multiple SQL statements

[LonelyPixel]

Can I execute multiple SQL statements in a single OracleCommand? I need to run a script that checks/drops/creates tables, sequences and all the things my application needs. This works fine with standard providers for SQL Server, MySQL and SQLite, but I have trouble using it with Oracle. There are begin/end blocks in my script, and if I just put them together, I get the error "found 'begin'". If I put a slash between them, I get the error "found '/'". Any ideas?

Here's an example that fails after the first statement:

Code: Select all

begin
	execute immediate 'drop table "Companies"';
exception
	when others then if sqlcode != -942 then raise; end if;
end;
/

begin
	execute immediate 'drop table "People"';
exception
	when others then if sqlcode != -942 then raise; end if;
end;
/

[Pinturiccio]

OracleCommand class is intended for the execution of a single command. If you want to execute several statements at once, use the OracleScript class. For more information, please refer to http://www.devart.com/dotconnect/oracle ... cript.html

You can use the following code for executing your command:

Code: Select all

OracleConnection conn = new OracleConnection("your connection string");
conn.Open();

OracleScript script = new OracleScript(@"begin
execute immediate 'drop table Companies';
exception
when others then if sqlcode != -942 then raise; end if;
end;
/

begin
execute immediate 'drop table People';
exception
when others then if sqlcode != -942 then raise; end if;
end;
/
", conn);
script.Execute();