Multiple SQL statements

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
LonelyPixel
Posts: 1
Joined: Thu 05 Mar 2015 16:29

Multiple SQL statements

Post by LonelyPixel » Thu 05 Mar 2015 16:33

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

Re: Multiple SQL statements

Post by Pinturiccio » Fri 06 Mar 2015 14:36

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();

Post Reply