Using Easy Connect (EZCONNECT) Naming Method to Connect to Oracle Databases

Easy Connection (EZCONNECT) is a method of connecting to an Oracle database that eliminates the need for service name look up. All of the information needed to resolve the connection is included in the connection string. With 10g and higher installations of the Oracle Client and Database Easy Connect naming is automatically configured at installation time.

Easy Connect is an extension the host naming method by including an optional database service name and port number to the database host name. Below is the format for an Easy Connect connection string.

username/password@[//]host[:port][/service_name]

// – Specify // for a URL. (optional)
host – Host name or IP address of the database host (require)
port – Defaults to 1521. (optional)
service_name – Defaults to the host name. If the database server name matches the host this parameter is optional.

When using the Easy Connect method the connection string is transformed to a valid connect descriptor. You can see the conversion by issuing a tnsping similar to example below.

C:\Users\ejenkinson>tnsping win11g

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 23-AUG-2010 17:12:22

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

C:\Users\ejenkinson>tnsping odwin/win11g

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 23-AUG-2010 17:12:44

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=win11g))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521)))
OK (20 msec)

C:\Users\ejenkinson>

Note how the first tnsping failed but the second succeeded. All that was provided was the host name and the database service name. Note in the second example that the EZCONNECT adapter was used to resolve the name. The EZCONNECT adapter transformed to the odwin/win11g to

(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT=1521)) 
  (CONNECT_DATA=(SERVICE_NAME=win11g)))

Below is an example of making a connection using the Easy Connect method.

C:\Users\ejenkinson>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 23 17:49:23 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect ejenkinson/ejenkinson@odwin/win11g
Connected.
SQL>

If you want to be prompted for the password you can use the following.

C:\Users\ejenkinson>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 23 17:53:01 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect ejenkinson@"odwin/win11g"
Enter password:
Connected.
SQL>

If the double quotes are omitted the connection string will not be properly converted to a valid connect descriptor and the connection attempt will fail.

C:\Users\ejenkinson>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 23 17:55:10 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect ejenkinson@odwin/win11g
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


SQL>

While Easy Connect makes connecting to an Oracle database simple it is not suitable for all environments. Environments that require additional information in the connection descriptor such as connection pooling, external procedures calls or heterogeneous services or environments that utilize protocols other than TCP/IP.

1 thought on “Using Easy Connect (EZCONNECT) Naming Method to Connect to Oracle Databases”

Leave a Reply

Your email address will not be published. Required fields are marked *