I am having DB server on another machine and having asp.net application installed on local machine. I want to connect remote DB and execute my scripts, since my local machine doesn't have oracle installed I have go-ogled and tried few links it tells me editing transnames.ora file adding one new entry But this will not work since my local machine doesnt contain sqlplus So I would like to now what are the tools I will need to download on local application server to run my scripts.
2 Answers
You need to install oracle driver to communicate with remote server.
There are multiply options:
This 2 will have tnsnames.ora file that you we told about:
- Full oracle client
- Oracle instant client (small in size)
Tool specific:
- oracle jdbc driver for java
- cx_oracle for python
- something else for other tool
OS specific:
- on windows you can setup an ODBC driver to connect to ORACLE
Thanks abhi, filename is corrected.
After installing a client, you need to know where is your database server. In simple situation you need host, port and sid. entries in tnsnames.ora look like this:
connectionName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = yourHost)
(Port = yourPort)
)
)
(CONNECT_DATA =
(SID = yourSID)
)
)
You fill in all information and save the file. after that you can check connection ( I don't know, if tnsping is shipped with instant client)
>tnsping connectionName
OK (description of a connection)
Useful tip: you can just go to remote server (or some other PC that have db access already configured) and tnsping some connection (you probably already have a standard name for it). than just grap the output in brackets and put it into your tnsnames.ora.
your connection string:
username/password@connectionname
btw, instead of conenctionname you could put the whole connection descriptions (from tnsnames.ora)
username/password@(description=...)
5 Comments
abhi
There is no transnames.ora file. Please correct your answer. The file is tnsnames.ora
Rajubhai
Ok, after installing Oracle instant client, what steps I have to perform,I mean what things I have to edit in transnames.ora.I am currently using oracle 12c so in that I have one user named:c##test and password:test runs under:orcl SID.And after doing necessary changes what will be my final connect statement?
Rajubhai
Ok, after installing Oracle instant client, what steps I have to perform,I mean what things I have to edit in transnames.ora.I am currently using oracle 12c so in that I have one user named:c##test and password:test runs under:orcl SID.And after doing necessary changes what will be my final connect statement?
Rajubhai
I am having service name named :orcl in my local how can i use that.. and can I use same 1521 port in my local?
vav
for service, you will need to modify entry in tnsnames: (CONNECT_DATA = (SERVICE_NAME = yourservice) ) ). Yes, you put there incoming port for the DB server (not your own custom port). Default port is 1521
Now you can use the Oracle Managed Driver which does not require the installation of the Oracle Client on the machine. This is extremely helpful as Oracle Client installations are painful. It requires nothing more than putting the driver in your bin directory and providing an appropriate connection string and provider name.
To install the managed driver via NuGet run...
Install-Package odp.net.managed
You may need to change the provider name (I believe it's Oracle.ManagedDataAccess off the top of my head).
Example connection string...(replace MyHost, MyPort (usually 1521), MyOracleSid, myUsername, and myPassword with appropriate info).
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));
uid=myUsername;pwd=myPassword;
