Why should you choose Oracle for a database application?

M + M acadGraph LineLetter

Use ODBC connections in Oracle databases

In the past, we have already described various options for using data from external sources in AutoCAD Map via the ODBC connection. In this example we want to show you how you can use the tables of an Access database application in Oracle for the visualization of spatial data. Access is synonymous with all relational databases (e.g. MS SQL Server, MySQL) that can be accessed via an ODBC connection. You can then execute SQL queries from Oracle via the ODBC connection, but also add and modify data records.

The following steps are only required on the database server to establish a connection to an ODBC data source:

  1. Create ODBC DSN
  2. Create the Oracle initialization file
  3. Create listener
  4. Create TNS name
  5. Create a database link
  6. Create synonyms or views

A prerequisite for successful access to the Access database or other database is a driver that adds a provider to the Windows ODBC administration via which the external database can be addressed. For all common databases, providers can usually be found for download on the Internet. In the case of the Access database, you can use the Access Database Engine for Office 2010 x64, the installation of which we have already described in another LineLetter.


Create the Oracle initialization file

Next, register the ODBC data source in the Oracle. When starting the listener service, an initialization file must be found in $ ORACLE_HOME / hs / admin that contains the name of the ODBC data source.


To do this, make a copy of the initdg4odbc.ora file and rename it so that it begins with init and is followed by the data source name.
Then open the file in an editor and enter the name of the system DSN or the complete DSN information after HS_FDS_CONNECT_INFO.
After HS_DB_NAME, insert the name of the database as it should be recognized within the Oracle environment. Please note that this name can be a maximum of 8 characters long. Ideally, write the DB_NAME and the DSN name in capital letters.

Save and close the file.

Create listener

In the $ ORACLE_HOME / network / admin directory, open listener.ora in an editor and add another entry to the list of listeners. Then save and close the file.
So the list of listeners could look like this:

In order for the changes to take effect, we will restart the listener later.

Create TNS name

With the change in the listener list, the database can now accept the request to the ODBC database. However, so that you can also send a request to the ODBC database, the list of TNS names must be extended by the corresponding entry.

In the same directory $ ORACLE_HOME / network / admin open tnsnames.ora in an editor and add another entry. Save and close the file.

Create a database link

Start the MS-DOS command prompt with administrative privileges and enter the commands lsnrctl stop and lsnrctl start one after the other to restart the listener. In the response you can already see the additional connection to the ODBC database.

Now log in to the database and create a public database link as a test to check the functionality.

Username and password must match the access data that you entered in the advanced settings in the ODBC administration. If, on the other hand, you have not assigned a user name and password in the Access database and the login data in the advanced settings of the ODBC connection remain empty, you can enter any login data when creating the database link.

Now you can also manipulate data sets. Make sure here too that the transaction must be concluded with COMMIT in order to apply the changes.

However, you should consider whether the database connection should really be available PUBLIC, i.e. for everyone reading and writing. But that depends on the respective situation and the underlying task.
For example, if only a table is to be referenced for read access, create a view of the ODBC table in a separate user and assign GRANT SELECT authorization to the authorized industry models.
In the final example we want to demonstrate how you can automatically create views for all tables of the ODBC data source and use them in an industry model.

Please note that special characters in the names of tables, views and field names in Oracle are problematic. Rename this in the Access database if necessary. You can also overwrite field names within Oracle.

You can then link the values ​​in views or save references from the ODBC data source in the industry model.

The human and machine team at acadGraph GmbH wishes you every success.