OWB - Creating an ODBC Non-Oracle Module as data source (with Excel)
About
Oracle Warehouse Builder use the gateway capabilities of the oracle database to create Non-Oracle Module.
This article will create an ODBC Non-Oracle Module on Excel data source.
Articles Related
Set up the ODBC connectivity to a data source
To set up the ODBC connectivity to a data source, you have perform the steps :
- Create the Excel Sheet
- Create an ODBC System Dsn Connexion
- Create the Heterogeneous Services Initialization File
- Set up the listener on the agent
- Create a Net Service Name
of this article : Oracle Database - How to read data through an ODBC connection with Gateway (Heterogeneous services)
Creating an ODBC Module
After you set up the ODBC connectivity to a data source, you must create an ODBC module to import the metadata from this data source. The ODBC node is available under the Databases node in the Projects Navigator. To create an ODBC module:
- Right-click ODBC and select New ODBC Module. The Create Module Wizard is displayed.
- In the Name and Description page, provide a name, description (optional), and select a module status.
- In the Connection Information page, either select an existing location or click Edit to open the Edit Non-Oracle Location Dialog Box and provide the connection details (see the paragraph below for more details)
- In the Summary page, verify the details and click Finish.
At the end of the module wizard, you can see than the Non-Oracle Module is an Oracle Database - Gateway (Heterogeneous services) application.
The newly created ODBC module is now available under the ODBC node.
Edit Non-Oracle Location Dialog Box
Select the Connection Type to be one of :
- Host:Port:Service,
- Database Link,
- or SQL*Net.
Host:Port:Service
If you selected Host:Port:Service, then provide the following connection details:
- User Name/Password: You can provide a dummy user name and password as you are not connecting to an Oracle database.
- Host: Machine on which the database is hosted.
- Port: SQL port number of the database.
- Service Name: The SID_NAME that you specify in the listener.ora file.
- Schema: You can leave this field empty if you are not importing from a schema.
Database Link
If you selected Database Link, then provide the following connection details:
- From Location: An existing location where the database link is defined
- Database Link Name: The object name of the database link
- Schema: The schema where the source data is stored or the target objects will be deployed.
SQL*Net
For SQL*Net, provide the following connection details:
- User Name: The database user credential that has permission to access the schema location. When connecting to a database that does not have user names, enter any text as a mock user name.
- Password: The password associated with user name. When connecting to a database that does not require password, enter any text as a mock password.
- Oracle Database - Net Service Name (tnsname): The name of the predefined connection.
- Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. Select this option when connecting to a database in a different network.
Click Test Connection to verify that you have provided the correct details and click OK to set the connection details.
Import
When you want to import the metadata, you will see the name define in the creation step of the excel file.
