Execute multiple queries in single Oracle command in C#

Question

I am using visual studio 2013 and oracle database.I want execute multiple create table queries at once in single oraclecommand is it possible ? I am trying following 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();

Got error at cmd.ExecuteNonQuery();

Ponder Stibbons · Accepted Answer · 2015-08-10 11:22:52Z

21

In order to execute more than one command put them in begin ... end; block. And for DDL statements (like create table) run them with 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();

More info: Executing SQL Scripts with Oracle.ODP

answered Aug 10, 2015 at 11:22

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sam Over a year ago

what would be the index of the first column of second query ? textbox1.Text = oraReder.GetString(index).ToString();

Govind chouhan Over a year ago

4 Comments

BugFinder Over a year ago

you might need a begin and end statement. "doesnt work" isnt helpful.

Hanni Over a year ago

Tried with begin and end statement but same error. @BugFinder

Hanni Over a year ago

Error message "An unhandled exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll " at ExecuteNonQuery(); @BugFinder

sam Over a year ago

@BugFinder what would be the index of the first column of second query ? textbox1.Text = oraReder.GetString(index).ToString();

Collectives™ on Stack Overflow

Execute multiple queries in single Oracle command in C#

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related