Executing multiple queries in a single Oracle command in C #

Question

I am using visual studio 2013 and oracle database. I can execute several table creation queries at once in one oracle, is this possible? I try to follow but not working

OracleCommand cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "create table test(name varchar2(50) not null)"+"create table test2(name varchar2(50) not null)"; //+ "create table test3(name varchar2(50) not null)"+"create table test3(name varchar2(50) not null)"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); 

Received error in cmd.ExecuteNonQuery ();

Answer 1

To execute several commands, put them in the begin ... end; block begin ... end; . And for DDL statements (like create table ), run them using execute immediate . This code worked for me:

 OracleConnection con = new OracleConnection(connectionString); con.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = "begin " + " execute immediate 'create table test1(name varchar2(50) not null)';" + " execute immediate 'create table test2(name varchar2(50) not null)';" + "end;"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); con.Close(); 

Additional Information: Executing SQL Scripts with Oracle.ODP

Answer 2

You tried

 cmd.CommandText = "create table test(name varchar2(50) not null);"+"create table test2(name varchar2(50) not null);";