How can i set the Oracle schemaname at runtime ?
How can i set the Oracle schemaname at runtime ?
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)
we want to use mulitple schema's within the same server/user/password environment.
how can we set the desired schemaname (in C# code)
You can make a simple change to the template in order to remove schema from the generated code.
Replace the
line with this one:
In case you need to change the current schema try to execute the following code:
Replace the
Code: Select all
[Table(Name = @"")]
Code: Select all
[Table(Name = @"")]
Code: Select all
ALTER SESSION SET CURRENT_SCHEMA = "Schema1"
What is the best way to hook the Alter Session ?
What is the best way to hook the Alter Session with the new schemaname into the DataContext ?
Try the following code:
Code: Select all
dataContext.ExecuteCommand("ALTER SESSION SET CURRENT_SCHEMA = \"Schema1\"").
I cannot find this template, where is it normally stored?AndreyR wrote:You can make a simple change to the template in order to remove schema from the generated code.
Replace theline 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
[Table(Name = @"")]
Code: Select all
ALTER SESSION SET CURRENT_SCHEMA = "Schema1"
What do I do after having created a new template? It does not have any effect...?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.
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");
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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.
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 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.
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
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
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
-
- Posts: 4
- Joined: Mon 27 Aug 2012 15:14
Re: How can i set the Oracle schemaname at runtime ?
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
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
-
- Posts: 4
- Joined: Mon 27 Aug 2012 15:14
Re: How can i set the Oracle schemaname at runtime ?
Any update on this issue? Thanks!
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48
Re: How can i set the Oracle schemaname at runtime ?
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:
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.
-
- Posts: 4
- Joined: Mon 27 Aug 2012 15:14
Re: How can i set the Oracle schemaname at runtime ?
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
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