OraDirect for .NET2 sending multiple statements in one call

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
xcbroker
Posts: 21
Joined: Tue 07 Apr 2009 21:25
Contact:

OraDirect for .NET2 sending multiple statements in one call

Post by xcbroker » Wed 30 Sep 2009 16:17

I am using OraDirect for .NET 2.0 version 4.60.33 and want to call Oracle with two statements in one call. The following is the code snippet:

try {
ArrayList sqlParams = BuildParameters();

using (OracleConnection conn = new OracleConnection(connectionString))
{
CommandType commandType = CommandType.Text;
OracleParameter[] parameters = (OracleParameter[])sqlParams.ToArray(typeof(OracleParameter));

OracleHelper.ExecuteDataset(conn,
commandType,
operation,
parameters);
}
catch (OracleException oex)
{
}

In the above code, I would like to pass two statements in operation string, something like the following:

Set role read_all_role;
select * from apc_ar_customers;

However this did not work and returned syntax error. Please help.

Thanks.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 02 Oct 2009 16:07

The OracleCommand.CommandText property can contain only one statement without semicolon in the end. If you want executing several statements, please execute command several times or take advantage of using OracleScript.

xcbroker
Posts: 21
Joined: Tue 07 Apr 2009 21:25
Contact:

Post by xcbroker » Tue 06 Oct 2009 00:38

Would the following command text work?

BEGIN
Set role read_all_role;
select * from apc_ar_customers;
END

Could you show me an example where using OracleScript so that we can specify Oracle command as Text to work with multiple statements? I am having problem with my Oracle environment and working on it to get it resolve so I cannot myself verify at the moment.

Thanks.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 06 Oct 2009 14:37

Record set can be retrieved only via cursor parameter. Please create a stored procedure where you can set the necessary role and return the record set using a cursor. As an alternative, you can create a user account at your server where this role is set by default.

Post Reply