OraDirect for .NET2 sending multiple statements in one call

OraDirect for .NET2 sending multiple statements in one call

Postby 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.
xcbroker
 
Posts: 21
Joined: Tue 07 Apr 2009 21:25

Postby 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.
Shalex
Devart Team
 
Posts: 7293
Joined: Thu 14 Aug 2008 12:44

Postby 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.
xcbroker
 
Posts: 21
Joined: Tue 07 Apr 2009 21:25

Postby 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.
Shalex
Devart Team
 
Posts: 7293
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle