Database name in oracle 12 connection parameters

Question

I am currently working with MS SSIS to copy data from MSSQL to Oracle 12. I cannot figure out how to make connection string to some test database. I found sample connection string for oracle .net provider.

Data Source=
(DESCRIPTION=
    (ADDRESS_LIST=
        (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=172.25.123.27)
            (PORT=1521)
        )
    )
    (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=orcl)
    )
)
DBA Privilege=SYSDBA

Where is the database name? MSSQL has server, instance, user, password parameters. What is "datasource" in this case?

Answer 1

An Introduction

The equivalent of a SQL Server database name in Oracle would be a schema, but that won't help in this case, as you don't specify the schema in an Oracle connection string.

You would either use the (SERVICE_NAME=orcl) part to connect to an Oracle Database Instance (which in part corresponds to a SQL Server instance) or you would insert an alternate (SID=<ORACLE_SID_on_server>) just under the (SERVICE_NAME...) portion of the connection string.

An alternate solution is to use the other parameter (GLOBAL_NAME=<ORACLE_SID_with_Domain>) in the connection string.

But let's start from the beginning. When an Oracle Database Administrator installs an Oracle Database Instance, then they will generally define a name for that instance. The instance is created and contains a heap of internal tables/views and other data dictionary objects. This corresponds to the SQL Server instance (in most cases).

After the initial setup, developers will create schemas and load (user/schema) objects into the newly created schema. The developers will then decide what GRANTs to assign to individual logins (users). This is what corresponds to a SQL Server database (in most cases).

When using a connection string, you are actually connecting to an Oracle Database Instance (in most cases). The login / user you use to initiate the connection will determine what you see in the database instance (e.g. which user schemas).

Answering Your Questions

Where is the database name?

In your case the SERVICE_NAME is the parameter you are looking for. This is the name of the Oracle Database Instance. In your case orcl. However, this could be just a default value. Ask your Oracle DBA for the correct name of the Oracle Database Instance on your server with the IP address 172.25.123.27.

You could alternatively supply the following parameters:

        (SID=orcl)
        (GLOBAL_NAME=orcl.domain.tld)

These parameters have to match the defined parameters of the Oracle Database instance. Ask your Oracle DBA for this information.

MSSQL has server, instance, user, password parameters.

See my explanation above.

What is "datasource" in this case?

Data Source is normally the name you give to the defined connection string. It can be compared to a CNAME / Alias in DNS terms. It's just a name you can give to the portion of the connection string you are defining. I name the following example OR19PROD and added the SID and GLOBAL_NAME parameters for your convenience.

OR19PROD=
(DESCRIPTION=
    (ADDRESS_LIST=
        (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=172.25.123.27)
            (PORT=1521)
        )
    )
    (CONNECT_DATA=
        (SERVER=DEDICATED)
        (SERVICE_NAME=orcl)
        (SID=orcl)
        (GLOBAL_NAME=orcl.domain.com)
    )
)

A user could now connect to the OR19PROD alias (TNSNAME.ora entry) which would redirect to the correct server.

Reference Reading

• Local Naming Parameters in the tnsnames.ora File (Oracle Docs)