Why should you choose Oracle for a database application?
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:
- Create ODBC DSN
- Create the Oracle initialization file
- Create listener
- Create TNS name
- Create a database link
- 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 ODBC DSN
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.
Save and close the file.
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.
- What is the limit to dreaming
- Jews believe that God is almighty
- What are your memories of the 1940s
- How can we attach documents in the iPhone
- How did Bardock travel back in time
- What if an AI became president?
- Humility is a dying virtue
- How often does a crocodile eat
- You can be hypnotized for 9 months
- What are the skills that distinguish paralegal and attorney?
- There are unbreakable ceramic cups
- Why do I become estranged from classmates
- What causes numbness while running
- How do philanthropists and humanists differ?
- Did Michael Jackson really walk the moon
- What are the topics in Pulp Fiction
- A long CSS selector is bad
- Is internal security affected by the government shutdown?
- What if you are on Wikipedia
- When can I join Swiggy?
- Who is the greatest content creator on the internet
- What class of drugs is alprazolam
- How can I get very strong bones
- Why is my father old