Problems using @schema for a stored procedure

Problems using @schema for a stored procedure

Postby Michael86 » Mon 23 Jan 2012 08:52

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.
Michael86
 
Posts: 2
Joined: Mon 23 Jan 2012 08:03

Postby Pinturiccio » Thu 26 Jan 2012 13:00

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);
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Postby Michael86 » Fri 27 Jan 2012 11:17

Thanks for your help. I will use the posted workaround for now.
Michael86
 
Posts: 2
Joined: Mon 23 Jan 2012 08:03


Return to dotConnect for Oracle