Oracle Developer and ODBC
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The Oracle database is a powerful tool. It has evolved over several decades into what you see today. Oracle Developer is also a powerful tool, specifically designed to take advantage of the Oracle database power. Therefore, it is difficult to imagine why someone would want to connect Oracle Developer to anything but an Oracle database. However, there may be that need and it is nice to know it is possible.
The following is a possible scenario for wanting to connect Oracle Developer to another ODBC style database. Jim is an application developer recently assigned to a new enterprise-wide application for his company. He has just finished up a hugely successful development project, and is tired of everyone stopping by his office to congratulate him on a job well done. Unfortunately, his new project is about to begin, and he wants to get away from the office and do some preliminary design work. Jim decides to grab a laptop and head to his cabin in the mountains. He already has Oracle Developer loaded, but no Oracle database. He does have a copy of Microsoft Access, and wants to use that rather than spend the next several hours trying to load the Oracle database and risk running out of disk space.
There are a few problems with the previously mentioned scenario, such as the seemingly unending congratulations, but nonetheless somewhat believable. At the risk of sounding blasphemous, Microsoft Access is a great little single-user database. I would not run a company on it, but it is more than adequate to do some application prototyping.
Connecting to ODBC
Oracle Developer uses the Oracle Open Client Adapter (OCA) to connect to non-Oracle data sources. If you go into Forms Developer's online help system and look up ODBC, you will see information on "Accessing non-Oracle data sources." The version of Forms Developer that I used to write my book and create the example for this article includes specific instructions for the following data sources:
- Personal Oracle Lite
- Microsoft SQL Server (versions 4.2, 6.0, and 6.5)
- Microsoft Access (version 2.0 or higher)
- Oracle RDB (versions 5.0, 6.1, and 7.0)
- Sybase SQL Server (version 4.9, system 10, and system 11)
- Tandem NonStop SQL
- Informix SE and Online (version 5.x, 6.x, and 7.x)
This is just a partial list because OCA will connect to a number of ODBC database. However, Oracle only tests and verifies the databases on the list.
Jim wants to use Microsoft Access 2000, which is a later release than 2.0, so he should be fine. The first thing he does is make sure that Access is properly installed on his system. He then checks it out by creating a table or two and populating them with test data. Next, he makes sure that the ODBC driver is properly configured.
Configuring ODBC
When Jim installed Access on his machine, it made sure to update the necessary ODBC drivers so that other applications could access any databases he created. When Jim created a test database with a couple of tables and some sample data, he performed the first steps necessary to be able to use Oracle Developer with Access. Jim needs to complete the setup by going into the ODBC configuration program and finish setting up the database. Jim does this by following these steps:
Click on Start, Settings, Control Panel.
Double-click on the ODBC Data Sources icon.
Select MS Access Database from the list, and click the Configure button. If it does not exist, Jim needs to add one by clicking on the Add button, selecting Microsoft Access Database, and clicking on the Finish button.
Assign a name to the database. Jim is using the name ODBCTest.
Assign a description about the connection.
Click on the Select button. A File dialog box appears, in which the test database file can be chosen. Jim chooses the name db3 because it seems like a good idea at the time. (You can choose something a little more descriptive.)
Click on the Advanced button for the option of assigning a username and password. Doing this allows one to limit access to the database. More importantly, it allows one to create connections strings similar to Oracle's. Jim is using the combination of test for the username and test for the password. It is not original, but very easy to remember for this exercise.
Click OK to close the Set Advanced Options dialog box.
Click OK to close the ODBC Microsoft Access Setup dialog box.
Click OK to close the ODBC Data Source Administrator dialog box.
You are now ready to connect Oracle developer to your Access database.
Configuring Oracle Developer
Jim is ready and excited to connect Oracle Developer to his Access database. He starts everything up, and he enters the correct connection string—as specified in the directions. Anxiously, he waits only to receive a nice error message. When Jim installed Oracle Developer, he didn't plan on ever having to use it with a non-Oracle database. Therefore, he did not bother to install the OCA package.
This is easily solved by running the Oracle installer (it is found in the Oracle for Win9x menu) and installing the Oracle Open Client Adapter for ODBC. This should be found with the distribution that Oracle Developer came with.
Now that OCA is properly installed, Jim is ready to prototype his Oracle Developer Applications running against a Microsoft Access database. The last thing he needs to do is provide the proper connection string. At the login screen, he has to provide the username and password specified above. For Jim, it was a test for both values. The database name will be the following:
ODBC:ODBCTest
The first ODBC tells the connection network to connect to a database using the ODBC driver. "ODBCTest" gives the name of the database. Jim can now connect to Access and use Oracle Developer to create applications.
Possible Problems
As with anything involving computers, a lot can go wrong. If you find that it takes about 20 to 30 seconds to connect to the ODBC database, you may want to modify your tnsnames.ora file, and remove the HOSTNAME value from the names.directory_path entry. According to Oracle documentation, this is a defect that will be fixed in some future version of the connection software.
As you create your application, it may be impossible to see a list of tables in the Access database. This is because Oracle Developer is looking for specific data dictionary information. Access does not use the same data dictionary as Oracle, so it is necessary to fool Access (or any other ODBC database) into displaying its information using tables that Oracle Developer will be looking for. This is done by running a script provided by Oracle. For Access, you run the following command:
UBT32.EXE test/test@ODBC:ODBCTest @accdmbld.sql
You may have to provide the entire path of the accdmbld.sql script (which is found in the $ORACLE_HOME\oca60\60\sql\access directory). After the script is run, you should be able to see your database objects within the Access database. There are scripts for several other ODBC databases, as well. If a script does not exist for your particular one, you need to take an existing script and modify it to match your database's data dictionary. Although not exactly simple, it should be completed fairly easily with the help of your ODBC database's data dictionary documentation.