How can i set the Oracle schemaname at runtime ?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
JosR
Posts: 2
Joined: Wed 12 May 2010 09:17

How can i set the Oracle schemaname at runtime ?

Post by JosR » Wed 12 May 2010 09:21

in the lqml object, the schemaname is fixed.

we want to use mulitple schema's within the same server/user/password environment.

how can we set the desired schemaname (in C# code)

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 13 May 2010 11:38

You can make a simple change to the template in order to remove schema from the generated code.
Replace the

Code: Select all

[Table(Name = @"")]
line with this one:

Code: Select all

[Table(Name = @"")]
In case you need to change the current schema try to execute the following code:

Code: Select all

ALTER SESSION SET CURRENT_SCHEMA = "Schema1"

JosR
Posts: 2
Joined: Wed 12 May 2010 09:17

What is the best way to hook the Alter Session ?

Post by JosR » Mon 17 May 2010 08:51

What is the best way to hook the Alter Session with the new schemaname into the DataContext ?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 18 May 2010 14:30

Try the following code:

Code: Select all

dataContext.ExecuteCommand("ALTER SESSION SET CURRENT_SCHEMA = \"Schema1\"").

Mac
Posts: 12
Joined: Sun 23 Aug 2009 22:33

Post by Mac » Wed 02 Jun 2010 09:14

AndreyR wrote:You can make a simple change to the template in order to remove schema from the generated code.
Replace the

Code: Select all

[Table(Name = @"")]
line with this one:

Code: Select all

[Table(Name = @"")]
In case you need to change the current schema try to execute the following code:

Code: Select all

ALTER SESSION SET CURRENT_SCHEMA = "Schema1"
I cannot find this template, where is it normally stored?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 02 Jun 2010 12:56

Go to Tools->Entity Developer->Templates.
You will have to create a new template, copy the code from an existing C# or VB.NET template and make the mentioned corrections.

Miros
Posts: 45
Joined: Thu 20 Jan 2011 10:12
Contact:

Post by Miros » Wed 23 Feb 2011 23:52

AndreyR wrote:Go to Tools->Entity Developer->Templates.
You will have to create a new template, copy the code from an existing C# or VB.NET template and make the mentioned corrections.
What do I do after having created a new template? It does not have any effect...?

Wouldn't it be much easier for everyone if you added an option in the wizard to disable the prepending of the schema?

Any way, in my opinion, it comes close to a bug that this code has no effect on the source database:

Code: Select all

dataContext.ChangeDatabase("DBNAME");

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 25 Feb 2011 17:30

After the modified template is saved, it should become available in the Template drop-down list of the Generation node in the Model Settings dialog. Please select this template and re-generate the code for your model.

Thank you for your suggestions, we will consider possible ways to simplify removing the schema name from the generated code, as well as supporting the OracleConnection.ChangeDatabase method.

Miros
Posts: 45
Joined: Thu 20 Jan 2011 10:12
Contact:

Post by Miros » Fri 08 Apr 2011 10:20

StanislavK wrote:After the modified template is saved, it should become available in the Template drop-down list of the Generation node in the Model Settings dialog. Please select this template and re-generate the code for your model.

Thank you for your suggestions, we will consider possible ways to simplify removing the schema name from the generated code, as well as supporting the OracleConnection.ChangeDatabase method.
Any news on your considerations for this?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 11 Apr 2011 14:59

We plan to implement the ChangeDatabase method in one of the nearest builds. As for changing the schema name in the model, we cannot provide any timeframe for this at the moment.

We will post here when any new information concerning either of features is available.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 21 Apr 2011 17:11

We have implemented the OracleConnection.ChangeDatabase method (it changes the default schema used for the current connection). Also, the 'Preserve schema name in storage' check box is now available in the model settings dialog; you can disable this check box to prevent using the schema name in the generated code.

These features are available in the latest 6.30.144 Beta build of dotConnect for Oracle. The new build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For more information about the fixes and improvements available in dotConnect for Oracle 6.30 Beta, please refer to
http://www.devart.com/forums/viewtopic.php?t=20828

Limin Zhang
Posts: 4
Joined: Mon 27 Aug 2012 15:14

Re: How can i set the Oracle schemaname at runtime ?

Post by Limin Zhang » Mon 27 Aug 2012 16:50

Hi,

Thanks to the above posts, I tried the following.

.Clear all classes, methods, and others from model files
.Uncheck 'Preseve schema name'
.Add tables and stored procedures.

The schema name was gone from the attributes of generated table classes. However the generated the generated stored procedures still have schema name.

I removed the schema from the generated file in Visual studio text editor. Then ran my program. I got the error 'ORA-04043: object XXXX does not exist.', in which XXXX is the name of a stored procedure. The stack traces is the following.

[OracleException (0x80004005): ORA-04043: object XXXX does not exist]
Devart.Data.Oracle.a2.b(Int32 A_0) +219
Devart.Data.Oracle.bc.a(String A_0, Int32 A_1) +999
Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name) +986
Devart.Common.DbCommandBase.CreateParameters() +151
Devart.Data.Oracle.OracleCommand.h() +4
Devart.Data.Oracle.OracleCommandBuilder.DeriveParameters(OracleCommand command) +53
Devart.Data.Oracle.Linq.Provider.OracleDataProvider.GetProcParameters(String mappedName, DbParameterCollection& paramNames) +129
Devart.Data.Linq.Mapping.e.a(DataProvider A_0) +316
Devart.Data.Linq.Mapping.e..ctor(an A_0, MethodInfo A_1, DataProvider A_2) +1579
Devart.Data.Linq.Mapping.an.a(DataProvider A_0) +281
Devart.Data.Linq.DataContext.a(Object A_0, MappingSource A_1, Type A_2) +663
Devart.Data.Linq.DataContext..ctor(String connectionString, MappingSource mapping) +50

Because the exception happens in the constructor of DataContext, I could not change the schema by 'dataContext.ExecuteCommand("ALTER SESSION SET CURRENT_SCHEMA = \"Schema1\"").'.

Can we set the schema before or in the constructor of DbContext? Or is there any better way to handle multiple schemas?

I am using DotNetConnectForOracle6.x. After checking the latest version, I did not find anything related with this. Please let me know if I miss anything.

Thank you

Limin Zhang
Posts: 4
Joined: Mon 27 Aug 2012 15:14

Re: How can i set the Oracle schemaname at runtime ?

Post by Limin Zhang » Tue 28 Aug 2012 15:45

Any update on this issue? Thanks!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Re: How can i set the Oracle schemaname at runtime ?

Post by StanislavK » Tue 28 Aug 2012 16:08

According to the stack trace, the issue occurs when trying to describe a stored procedure. In previous versions of LinqConnect, Oracle procedures that return result sets were described when preparing metadata for the context. In the latest versions, the complete procedure metadata is stored in the mapping source (e.g., in the mapping attributes), thus instantiating the context should not result in the specified error. In particular, this means that with the newer versions you can change the schema before actually executing stored procedures.

JIC: besides that, the 'Initialization Command' connection string parameter is available since the 7.0 version of dotConnect for Oracle. This parameter allows specifying the command that will be executed just after opening the connection.

As for the error itself, please check the following:
  • whether the stored procedures belong to the same user as the tables;
  • whether the procedures are declared in a package;
  • whether the user specified in the connection string is the same user to whom the procedures belong.
If possible, please send us the declarations of these procedures and/or the model you are working with, so that we are able to analyze the issue in more details.

Limin Zhang
Posts: 4
Joined: Mon 27 Aug 2012 15:14

Re: How can i set the Oracle schemaname at runtime ?

Post by Limin Zhang » Tue 28 Aug 2012 17:21

Thank you for the quick reply.

Currently we are using DotConnect for Oracle 6.x. (Entity Developer 4.2.120) Does it support 'The complete procedure metadata is stored in the mapping source (e.g., in the mapping attributes)'? If not, what the earliest version support this feature? I checked http://www.devart.com/dotconnect/oracle ... story.html and found no information related to this.

As per your request, I list the function I encountered the error.

[Function(Name=@"COM_UTIL.LOG_PROCESS_ERROR")]
public void LogProcessError([Parameter(Name="P_PROCESS_RUN_ID", DbType="NUMBER")] System.Nullable<decimal> P_PROCESS_RUN_ID, [Parameter(Name="P_ERROR_MSG", DbType="VARCHAR2")] string P_ERROR_MSG)
{
IExecuteResult _LogProcessErrorResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), P_PROCESS_RUN_ID, P_ERROR_MSG);
}

CREATE OR REPLACE package MyCompany.com_util
as
procedure log_process_error(p_process_run_id in number
, p_error_msg in varchar2
);
end com_util;
/

Thanks

Post Reply