cx_Oracle & Connecting to Oracle DB Remotely

Question

How do you connect to a remote server via IP address in the manner that TOAD, SqlDeveloper, are able to connect to databases with just the ip address, username, SID and password?

Whenever I try to specify and IP address, it seems to be taking it locally.

In other words, how should the string for cx_Oracle.connect() be formatted to a non local database?

There was a previous post which listed as an answer connecting to Oracle via cx_Oracle module with the following code:

#!/usr/bin/python

import cx_Oracle
connstr='scott/tiger'
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

curs.execute('select * from emp')
print curs.description
for row in curs:
    print row
conn.close()

Segarra

Answer 1

I like to do it this way:

ip = '192.168.0.1'
port = 1521
SID = 'YOURSIDHERE'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)

db = cx_Oracle.connect('username', 'password', dsn_tns)

One of the main reasons I like this method is that I usually have a TNSNAMES.ORA file lying around someplace, and I can check that the dsn_tns object will do the right thing by doing:

print dsn_tns

and comparing the output to my TNSNAMES.ORA

Uncivil

FYI - this doesn't work when connecting to 11g RAC that requires a service name rather than a SID. The connstr method works as a service name. – Narceine

This works for me using cx_Oracle-5.1.3-11g.win32-py2.7 and connecting to oracle 10g where as user/pass@host:port/dbname always fails. thank you. – Sojourn

You can find IP with this SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual; and SID with this SELECT sys_context('USERENV', 'SID') FROM DUAL; – Watertight

Answer 2

You can specify the server in the connection string, e.g.:

import cx_Oracle
connstr = 'scott/tiger@server:1521/orcl'
conn = cx_Oracle.connect(connstr)

• "server" is the server, or the IP address if you want.
• "1521" is the port that the database is listening on.
• "orcl" is the name of the instance (or database service).

Johannajohannah

This may not work in all environments. In my case it doesn't work with Oracle InstantClient 11g and Python 2.6 on 64-bit Windows 7. The DSN method is more portable. – Koval

@Craig: thanks for the heads-up - can you tell me how it didn't work? What error is reported? – Johannajohannah

ORA-12514. Basically the listener on server doesn't recognize orcl as an instance. I couldn't make heads or tails of that, so I fired up a packet tracer, and the DSNs that are generated (for the Connect packet) are significantly different. The server is Oracle 10g, which may be significant. – Koval

I re-ran my packet traces. A connection made with user/pass@host:port/dbname ends up using dbname as the SERVICE_NAME instead of the SID, which fails for Oracle InstantClient 11g when talking to an Oracle 10g server. – Koval

I have the same problem, How did you suggest i connect using cx_oracle. – Bromleigh

Answer 3

import cx_Oracle

CONN_INFO = {
    'host': 'xxx.xx.xxx.x',
    'port': 12345,
    'user': 'user_name',
    'psw': 'your_password',
    'service': 'abc.xyz.com',
}

CONN_STR = '{user}/{psw}@{host}:{port}/{service}'.format(**CONN_INFO)

connection = cx_Oracle.connect(CONN_STR)

Telemotor

Answer 4

Instead of specifying the SID, you can create a dsn and connect via service_name like:

import cx_Oracle
ip = '192.168.0.1'
port = 1521
service_name = 'my_service'
dsn = cx_Oracle.makedsn(ip, port, service_name=service_name)

db = cx_Oracle.connect('user', 'password', dsn)

The benefit of using the service name instead of the specific instance identifier (SID), is that it will work in a RAC environment as well (using a SID won't). This parameter is available as of cx_Oracle version 5.1.1 (Aug 28, 2011)

Zoi

@A.Raouf: See here for an explanation – Zoi

Answer 5

import cx_Oracle
dsn = cx_Oracle.makedsn(host='127.0.0.1', port=1521, sid='your_sid')
conn = cx_Oracle.connect(user='your_username', password='your_password', dsn=dsn)
conn.close()

Rh

If your password have special symbols use this case! – Glamour

How to make sure the connection is encrypted? – Trisa

Answer 6

import cx_Oracle
ip = '172.30.1.234'
port = 1524
SID = 'dev3'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)

conn = cx_Oracle.connect('dbmylike', 'pass', dsn_tns)
print conn.version
conn.close()

Simard

This only works if the user has a SID, which was deprecated in favor of service_names years ago. Other answers explain the difference. Update: other answers already show the syntax you gave. – Epi