CODEWITHSUNDEEP
pythonsqldjangooracle-database
sbose
sbose

Reputation: 1811

How do I force Django to connect to Oracle using Service Name

Q : How do specify that Django needs to connect to Oracle DB using the service name and not SID ?

Hi,

I am currently telling my Django configuration to connect to Oracle using my SID.

However, I'll need to connect using the service name and not the SID. 

APP_DATABASES={
    'default': {
            'ENGINE': 'django.db.backends.oracle',
            'NAME': 'myservice',
            'USER': 'system',
            'PASSWORD': 'admin123',
            'HOST': '192.168.1.45',
            'PORT': '1699',
    }
}

This works fine.

However, When I replace the 'NAME' with the service name as follows 

 'default': {
                'ENGINE': 'django.db.backends.oracle',
                'NAME': 'myservice.bose.com',
                'USER': 'system',
                'PASSWORD': 'admin123',
                'HOST': '192.168.1.45',
                'PORT': '1699',
        }

I get a 

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Clearly Django is telling Oracle to connect using the SID which is not what I want Django to do.

How do specify that Django needs to connect to Oracle DB using the service and not SID ?

Note : I've tested that the service name mentioned above. It works great from Oracle SQL Developer.

Thanks - would really appreciate leads.

Upvotes: 13

Views: 18463

Answers (5)

caot
caot

Reputation: 3328

Here is the way that works:

myservice =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1699))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = myservice_name)
 )
)

myservice = ''.join(trc_scan.split())

'default': {
  'ENGINE': 'django.db.backends.oracle',
  'NAME': myservice,
  'USER': 'someuser',
  'PASSWORD': 'somepsw',
}

Don't put any of the following characters into user name and/or password:

@/(

Upvotes: 0

cheow.thianliang
cheow.thianliang

Reputation: 41

I use tnsnames.ora. It works for me on Django 1.7. These are the steps:

  1. Add an entry in tnsnames.ora for the connection.

    myservice =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1699))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = myservice.bose.com)
 )
)

  2. Change Django database settings to 

    'default': {
  'ENGINE': 'django.db.backends.oracle',
  'NAME': 'myservice',
  'USER': 'system',
  'PASSWORD': 'admin123',
}

For details, please refer to Connecting Django to Oracle database using service name

Upvotes: 4

sbose
sbose

Reputation: 1811

Thanks guys, There's a "documented" solution to this:

        'default': {
                'ENGINE': 'django.db.backends.oracle',
                'NAME': 'host.db.com:1699/oracle_service.db.com',
                'USER': 'user',
                'PASSWORD': 'pass',
        }

Note: The HOST and PORT keys need to be left out of the dictionary - else Django will try connecting with the complete "NAME" as an SID.

Upvotes: 27

dparkeruk
dparkeruk

Reputation: 181

Looking at the code that nickzam pasted:

import cx_Oracle as Database

def _connect_string(self):
    settings_dict = self.settings_dict
    if not settings_dict['HOST'].strip():
        settings_dict['HOST'] = 'localhost'
    if settings_dict['PORT'].strip():
        dsn = Database.makedsn(settings_dict['HOST'],
                               int(settings_dict['PORT']),
                               settings_dict['NAME'])
    else:
        dsn = settings_dict['NAME']
    return "%s/%s@%s" % (settings_dict['USER'],
                         settings_dict['PASSWORD'], dsn)

.. it is clear that if you do not specify a 'PORT' parameter, the 'NAME' parameter is used 'as is'. Therefore, passing an Oracle connect string as the 'NAME' parameter will do the trick (if you remove the 'PORT' parameter).

Basically something like this will work:

'default': {
    'ENGINE': 'oraclepool',
    'NAME': '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydbhostname.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myservicename.example.com)))',
    'USER': 'scott',
    'PASSWORD': 'tiger',
}

I have tried this using a SCAN hostname for HOST and verified that this works too.

WARNING: My tests so far have been limited to checking whether the connect string is accepted, a connection is made and my app is served successfully, accessing data. Before relying on this configuration I would advise more aggressive testing 8)

Upvotes: 8

nickzam
nickzam

Reputation: 813

Behind the scenes Django uses cx_Oracle library to connect to Oracle database. Source: https://github.com/django/django/blob/master/django/db/backends/oracle/base.py

import cx_Oracle as Database

def _connect_string(self):
        settings_dict = self.settings_dict
        if not settings_dict['HOST'].strip():
            settings_dict['HOST'] = 'localhost'
        if settings_dict['PORT'].strip():
            dsn = Database.makedsn(settings_dict['HOST'],
                                   int(settings_dict['PORT']),
                                   settings_dict['NAME'])
        else:
            dsn = settings_dict['NAME']
        return "%s/%s@%s" % (settings_dict['USER'],
                             settings_dict['PASSWORD'], dsn)

Function cx_Oracle.make_dsn() supports optional parameter service_name (excerpt from cx_Oracle docs):

cx_Oracle.makedsn(host, port, sid[, service_name])

Return a string suitable for use as the dsn for the connect() method. This string is identical to the strings that are defined by the Oracle names server or defined in the tnsnames.ora file. If you wish to use the service name instead of the sid, do not include a value for the parameter sid and use the keyword parameter service_name instead. Note This method is an extension to the DB API definition.

Unfortunately, Django is not passing service_name parameter on connection.

If you really need it, add feature request to Django or patch your local version of Django to support SERVICE_NAME parameter (bad idea, you will need to support it by yourself):

def _connect_string(self):
    settings_dict = self.settings_dict
    if not settings_dict['HOST'].strip():
        settings_dict['HOST'] = 'localhost'
    if settings_dict['PORT'].strip():
        if not 'SERVICE_NAME' in settings_dict:
            dsn = Database.makedsn(settings_dict['HOST'],
                                   int(settings_dict['PORT']),
                                   settings_dict['NAME'])
        else:
            dsn = Database.makedsn(host=settings_dict['HOST'],
                                   port=int(settings_dict['PORT']),
                                   service_name=settings_dict['SERVICE_NAME'].strip())

    else:
        dsn = settings_dict['NAME']
    return "%s/%s@%s" % (settings_dict['USER'],
                         settings_dict['PASSWORD'], dsn)

Then change NAME to SERVICE_NAME variable to your connection 'default':

 'default': {
            'ENGINE': 'django.db.backends.oracle',
            'SERVICE_NAME': 'myservice.bose.com',
            'USER': 'system',
            'PASSWORD': 'admin123',
            'HOST': '192.168.1.45',
            'PORT': '1699',
    }

Later, I am going to add it as a pull request to Django source.

Upvotes: 3

Related Questions