CODEWITHSUNDEEP
databaseoracle-databasesqlplusdatabase-administration
Rajubhai
Rajubhai

Reputation: 107

Cross Server script execution oracle

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.

Upvotes: 0

Views: 66

Answers (2)

vav
vav

Reputation: 4694

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=...)

Upvotes: 1

mason
mason

Reputation: 32694

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;

Upvotes: 0

Related Questions