Executing multiple statements in single sql statement with semicolon?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
anand123
Posts: 17
Joined: Thu 30 Jan 2014 08:58

Executing multiple statements in single sql statement with semicolon?

Post by anand123 » Tue 02 Aug 2016 15:23

is there any possibility to execute multiple sql statements as single sql and return dataset?

Code: Select all

Select * From Table1; Select * From Table2; Select * From Table3;
this statement works in ado.net, but doesnt work with oracle using DevArt.

is there any possibility in DevArt?

regards,
Anand

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Executing multiple statements in single sql statement with semicolon?

Post by Pinturiccio » Wed 03 Aug 2016 15:14

You can create one PL/SQL block, returning result sets via cursor output parameters. And use OracleCommand with this PL/SQL block.

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

Here is an example of using OracleScript for getting results of all the select statements:

Code: Select all

static void Main(string[] args)
{
        OracleConnection conn = new OracleConnection("your connection string");
        conn.Open();
        OracleScript script = new OracleScript("Select * From Table1; Select * From Table2; Select * From Table3;", conn);
        OracleDataReader reader;
        OracleDataSet ds = new OracleDataSet();

        while(script.ExecuteNext(out reader))
        {
                var dt = ds.Tables.Add();
                dt.Load(reader);
        }
        conn.Close();
}

Post Reply