Problem solve Get help with specific problems with your technologies, process and projects.

Accessing an ODBC data source, part 1

In my Oracle forms, I want to access another ODBC data source from our current database. How can I use command OPEN_CONNECTION in library EXEC_SQL?
Yes, Webforms use the Oracle Open Client adapter (OCA) to connect to a different database and can be deployed on the Web. The method that you use will depend on the database version you are using.

However, when connecting to non-Oracle data sources like SQL Server, MS Access, DB2 etc., Data Block wizard will not be able to retrieve the available tables for the users. The Oracle Open Client Adapter provides a number of scripts for generating data dictionary views/tables on some non-Oracle databases.

This note lists the various scripts available to generate data dictionary tables/views in some non-Oracle databases. This note explains the steps to configure an ODBC driver for connecting a SQL Server 7.0 database and create a block based on a table (employee) from a sample database (PUBS) in SQL Server.

Following the various .SQL files and their location within the Oracle Home.

Database View Creation Script + Location
Sybase SQL Server 4.x $ORACLE_HOMEoca60sqlsqlsrvsqsvubld.sql
Sybase SQL Server 4.x $ORACLE_HOMEoca60sqlsqlsrvsqsvubld.sql
Microsoft SQL Server 6.x/7.x $ORACLE_HOMEoca60sqlsqlsrvsqs60bld.sql
Oracle RDB (6.1 and Greater) $ORACLE_HOMEoca60sqlrdbrdbvubld.sql
Oracle Lite (3.0 and Greater) $ORACLE_HOMEoca60sqlpolitepolvubld.sql
Sybase 10/11 $ORACLE_HOMEoca60sqlsybasesybvubld.sql
DB2 for AS 400 $ORACLE_HOMEoca60sqldb2_400db4vubld.sql
DB2 for AIX $ORACLE_HOMEoca60sqldb2_aixdb2vubld.sql
DB2 for MVS $ORACLE_HOMEoca60sqldb2_mvsdbvubld.sql
Informix $ORACLE_HOMEoca60sqlinformixinfvubld.sql
Ingres $ORACLE_HOMEoca60sqlingresingvubld.sql
Non-Stop SQL (Tandem) $ORACLE_HOMEoca60sqlnonstopnsvubld.sql

Click for part 2 on configuring an ODBC data source.

Dig Deeper on Oracle E-Business Suite