Multiple SQL statements

Multiple SQL statements

Postby 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;
/
LonelyPixel
 
Posts: 1
Joined: Thu 05 Mar 2015 16:29

Re: Multiple SQL statements

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


Return to dotConnect for Oracle