How Do I Connect to an Oracle Data Source?
In this article we demonstrate how to connect a Report Template to an Oracle database. For more details about the Connection Editor, see the Connection Editor Reference.
What is Needed to Establish the Connection
All versions of Oracle are supported. If you do not have an Oracle connector or drivers already installed, here are instructions on how to install a lean connector that will enable you to connect to your Oracle database using Report Designer:
To install the ODAC (ODP.NET) Oracle connector (aka Oracle Managed Client), and only the drivers needed, do the following:
- Go to the ‘Oracle Data Access Components’ page: http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html
- Click the ‘Accept License Agreement’ radio button.
- Find the ‘ODP.NET, Managed Driver Xcopy version only’ section.
- Click the ‘ODP.NET_Managed_ODAC…zip’ link (http://download.oracle.com/otn/other/ole-oo4o/ODP.NET_Managed_ODAC12cR4.zip)
- Oracle will request you sign in before download (if you don’t have an account, you must create one).
- After signing in, the necessary .zip file will be downloaded.
- Close all Office files.
- Once you have downloaded the .zip file, extract all the files to your Downloads folder.
- Open a command prompt as Administrator and navigate (cd) to the extracted Downloads folder (i.e. ODP.NET_Managed_ODAC12cR4).
- Run the command:
install_odpm.bat c:\oracle both true- You can have folders other than c:\oracle, but there must be no spaces in the folder name(s) as the .bat file makes that assumption.
- You should see several “The operation completed successfully” prompts.
- You have installed the necessary drivers to connect to your Oracle data source.
Connect Your Report Template to Oracle
From Microsoft Word, Excel, or PowerPoint, navigate to the AutoTag Manager tab. Once on the AutoTag Manager tab, click on the upper half of the Data Sources button. This will bring up the Connection Editor.
- In the Connection Editor window click on the New tab.
- Click on Oracle Managed Driver in the SQL section. The Details pane will change to prompt you for your data source details.
Do not use the ‘OracleClient (deprecated)’ option if it appears. It has been deprecated because it is unreliable.
- Enter a name for your data source connection.
- Enter the URI for your Oracle database. The Windward public Oracle data source is oracle.windward.net.
- Fill in your credentials. The oracle.windward.net username is "hr" and the password is "hr" (without the quotes).
- In the Display Tables section select User & System to retrieve the metadata of the tables created within a database and the system tables used to administer and configure each database.
- You can test your Oracle connection by clicking the Test button. If all parameters and credentials are correct and a connection is made, you will receive a successful notification.
- Click on Close to save your information
Other (Optional) Connection Editor Fields
| Field | Description |
|---|---|
| Display Tables |
User Owned is selected by default. Select User & System to retrieve the metadata of the tables created for a database and the system tables used to administer and configure each database. |
| Read in metadata |
Check this to read in additional information from the database, primarily the descriptions of tables, views, and columns, and the primary key - foreign key relationships. You should always keep this checked. |
| Use Connection String |
You can enter the connection string directly instead of entering the Database and Credentials. When unchecked it will display the connection string Report Designer generated from your settings. |
| Root Directory |
This is the optional default directory for any Import Tag requests where the requested file does not have an absolute path. If left blank, Report Designer will use the directory where the data source file is located. If the data source is not a file it will use the directory where the template is located. |
0 Comments
Add your comment