Problems using @schema for a stored procedure

[Michael86]

Hello,
I’m using the Package Library Devart.Data.Oracle to connect to a Database. The connection itself works fine. After that, I’m using the OracleCommand to execute commands on the Database. Most of the time the commands are stored procedures, so I’m setting the commandtype to StoredProcedure. It looks like that.

Code: Select all

OracleCommand orCmd = new OracleCommand("Package.Procedure", databaseConnection);
orCmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleCommandBuilder.DeriveParameters(orCmd);
…

That works fine.
But now a have to call a Procedure from an other databaseschema and use the same database connection. I found out, that it can be done using an @ to set the other schema. But if I’m using the same commands as before

Code: Select all

OracleCommand orCmd = new OracleCommand("Package.Procedure@NewSchema", databaseConnection);
orCmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleCommandBuilder.DeriveParameters(orCmd);
…

I’m getting an Exception that the “Object does not exist” as soon as I use DeriveParameters or, if I skip DeriveParameters, ExecuteReader. At first I thought it could be the access rights, but I can use the @schema in a command direkt on the database.
I tried some different ways and found out that a select with a @schema an text as commandtype works.

Code: Select all

OracleCommand orCmd = new OracleCommand("Select * from table@Schema", databaseConnection);
orCmd.CommandType = System.Data.CommandType.Text;
orReader = orCmd.ExecuteReader();
…

The problem is I have to use the package to access the database and I don’t know what else I can do. I hope you can help me.

[Pinturiccio]

We have reproduced the issue.

The following code invokes the issue:

Code: Select all

OracleCommand orCmd = new OracleCommand("Package.Procedure@NewSchema", databaseConnection);
orCmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleCommandBuilder.DeriveParameters(orCmd); 

We will investigate the possibility to make such code work correctly and notify you about the result as soon as possible.
As a workaround, you can use the following code:

Code: Select all

OracleCommand orCmd = new OracleCommand("begin Package.Procedure@schema(:param1, param2,...); end;", databaseConnection);
orCmd.CommandType = System.Data.CommandType.Text;
OracleCommandBuilder.DeriveParameters(orCmd);

[Michael86]

Thanks for your help. I will use the posted workaround for now.