Devart Forums

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.

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.

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.

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.