Q

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.

This was first published in November 2003

Dig deeper on Oracle E-Business Suite

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close