How to connect c# with Oracle database specifying the schema name?

Question

I have a problem trying to connect to oracle database specifying the schema name, when the schema name is different than the user name.

I used to connect using the next String.

But in this case the Schema of the database was the same as the user name.

     String con = "data source= (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = " + host + ")(PORT = " + port + ")))(CONNECT_DATA =(SERVICE_NAME = " + servicename + ")));
USER ID=" + user + ";
PASSWORD=" + pass;

So the query for login was like

SELECT * FROM usuarios WHERE usuario='545478';

Now i have a new user for the conexion, diferent that the Schema, so now i need to change all the sql queries using the Schema name. For example if the schema name is PRODUCTION_DB the query needs to be change to:

SELECT * FROM PRODUCTION_DB.usuarios WHERE usuario='545478';

But I don't want to change all the queries in my code. I prefer to specified the database name in the connection like in MySQL.

How can I change the connection string to add the database name?

For example:

String con = "data source= (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = " + host + ")(PORT = " + port + ")))(CONNECT_DATA =(SERVICE_NAME = " + servicename + ")));
USER ID=" + user + ";PASSWORD=" + pass;
 DATABASE = "PRODUCTION_DB";

are you using 32 bit Oracle Client..? if so you do not have to setup the connection string to use TnsNames you can still use this style in your app.Config or Web.Config I am currently using Oracle and it works this way. <connectionStrings> <add name="DbConn" connectionString=" Data Source=DataBaseName;User Id=xxx;Password=xxxx;"/> — MethodMan
– MethodMan, Commented Jun 1, 2016 at 17:26
hi, im using the app.config but i dont understand the point. — mavi
– mavi, Commented Jun 1, 2016 at 18:23
what's not to understand.. perhaps you should read up on what the Configuration Class is and how it works — MethodMan
– MethodMan, Commented Jun 1, 2016 at 20:10
Im already talked to oracle database support. They told me that it is not possible. I need to specified the owner in my queries. So, do you really think you know more than them? Your solution is usuless. — mavi
– mavi, Commented Jun 1, 2016 at 21:18
that's why I currently have it working and with the 32bit Oracle Client on a 64 bit machine you need to set the project from AnyCPU to x86 good luck I have mine working with the simple example I posted in the comments earlier.. — MethodMan
– MethodMan, Commented Jun 1, 2016 at 21:20

Gunawan Widyatmaji · Accepted Answer · 2021-07-06 05:10:49Z

3

It can be done in the following way:

OracleConnection connection = new OracleConnection(connectionString); 
connection.Open();
OracleCommand cm = connection.CreateCommand();
cm.CommandText = "ALTER SESSION SET CURRENT_SCHEMA = PRODUCTION_DB";
cm.ExecuteNonQuery();

answered Jul 6, 2021 at 5:10

Gunawan Widyatmaji

312 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick.Mc Over a year ago

More ways of doing the same thing here: dba.stackexchange.com/questions/27869/…

krokodilko · Accepted Answer · 2016-06-01 17:37:39Z

-1

Try this connection string:

 string connectionString = "user id=uat;password=*****;data source=localhost:1521/orcl";
 OracleConnection connection = new OracleConnection(connectionString);
 connection.Open();

where:

orcl - is my database name (SID)
uat - is a schema name (user name)
localhost:1521 - are a server name (in my case: localhost) and a port.

answered Jun 1, 2016 at 17:37

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

2 Comments

mavi Over a year ago

hi @kordirko, thanks for helping me. I need to specified 6 things. 1.-hostname, 2.- password. 3.- host ip 4.- port 5.- SID and 6.- DataBaseName. In your example you said that SID is the same as the name of the DataBase, but in my case isn't. Another idea?

Aymane EL Jahrani Over a year ago

Needs a bit more details, but this definitely helps ! In this weird Oracle world, a schema corresponds to a "user".

Collectives™ on Stack Overflow

How to connect c# with Oracle database specifying the schema name?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related