{"id":13563,"date":"2020-03-27T17:43:16","date_gmt":"2020-03-27T21:43:16","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=13563"},"modified":"2023-12-08T10:26:09","modified_gmt":"2023-12-08T15:26:09","slug":"connecting-iri-software-oracle","status":"publish","type":"post","link":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/","title":{"rendered":"Connecting IRI Software to Oracle"},"content":{"rendered":"<h2><b>Overview<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Connecting to a database like Oracle can be the most frustrating part of application installation and configuration. The many options available with JDBC and ODBC drivers can be daunting. The acronyms alone are enough to cause a headache.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The IRI software stack uses both JDBC and ODBC drivers for different purposes. <\/span><a href=\"https:\/\/www.iri.com\/products\/workbench\"><span style=\"font-weight: 400;\">IRI Workbench<\/span><\/a><span style=\"font-weight: 400;\"> requires JDBC connections for access to the data and metadata in databases. The use of IRI Workbench is totally optional. However, the database metadata integration, plus various wizards and editors make it much easier to create and maintain the job scripts and configuration files used by the IRI data processing engine, <\/span><a href=\"https:\/\/www.iri.com\/products\/cosort\/sortcl\"><span style=\"font-weight: 400;\">SortCL<\/span><\/a><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">IRI Workbench, with its JDBC connections, is also necessary for running IRI DarkShield\u00ae, for the discovery and masking of semi-structured and unstructured data stores and documents. ODBC drivers and connections are required for the runtime execution of IRI Voracity\u00ae ETL jobs, and component tools like CoSort\u00ae, RowGen\u00ae, FieldShield\u00ae, and NextForm\u00ae that also source and target data in databases.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The instructions and examples in this article are based on the <\/span><a href=\"https:\/\/www.oracle.com\/technetwork\/database\/enterprise-edition\/oraclenetservices-neteasyconnect-133058.pdf\"><span style=\"font-weight: 400;\">Oracle Easy Connect (EZCONNECT)<\/span><\/a><span style=\"font-weight: 400;\"> method. If for any reason your Oracle databases can not use EZCONNECT, you will have to rely on documentation provided by Oracle, and other resources, to configure your connections.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Follow along with our YouTube video!<\/strong><\/p>\n<p><iframe loading=\"lazy\" title=\"Orcale DB Tutorial\" width=\"1140\" height=\"641\" src=\"https:\/\/www.youtube.com\/embed\/FTkQ4vpSoi0?start=305&#038;feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" allowfullscreen><\/iframe><\/p>\n<h2><b>Gather Information<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">There are several details that you will need to know about your database before configuring any connection. This information should be available from a database administrator. If you administer the database yourself, this information can be found in the <\/span><i><span style=\"font-weight: 400;\">listener.ora<\/span><\/i><span style=\"font-weight: 400;\"> configuration file.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Collect the details shown in Table 1 for the database that requires a connection. This information is needed for both JDBC and ODBC connections.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Table 1 &#8211; Details needed to create a database connection.<\/strong><\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-13570 size-large\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-1-1024x207.png\" alt=\"Empty table\" width=\"1024\" height=\"207\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-1-1024x207.png 1024w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-1-300x61.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-1-768x156.png 768w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-1.png 1244w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">More about the information needed:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Host may be a hostname, or an IP address. Hostnames may be one word, with an implied domain, a fully qualified name including the domain.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Port is a single integer number. The default is 1521 but may be changed by the database administrator.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Every database instance has an SID. The SID must be unique if there is more than one instance sharing the same host and port. Sometimes the instance is referenced by a Service Name, instead of the SID. If the database that you are connecting to has a Service Name, you should use it.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Username should be the Oracle username associated with the schema or schemas you will be working with. It is best to choose a user with a limited scope of schema privileges. Using a SYSTEM user account may result in an unmanageable number of schemas and tables being visible.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Password must match the specified username.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Once the correct information has been gathered, you are ready to create the ODBC and JDBC connections.<\/span><\/p>\n<h2 id=\"odbc\"><b>ODBC<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">The ODBC manager, and drivers, come in two bit architectures, 32-bit, and 64-bit. The bit architecture of the manager and driver <\/span><b>must<\/b><span style=\"font-weight: 400;\"> match the bit architecture of the application using the connection. On Windows\u00ae, IRI runtime engines are often the 32-bit variety, even when running on a 64-bit operating system. On Linux and Unix, 64-bit applications prevail.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Before choosing the bit architecture of your ODBC drivers, it is important to know the bit architecture of the IRI runtime product using SortCL, like CoSort or FieldShield. If you&#8217;re unsure of the bit architecture of your IRI engine, you can check it using the version command. Form the command line, execute:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">sortcl \/v<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In IRI Workbench, the <\/span><i><span style=\"font-weight: 400;\">Help &gt; Version Information<\/span><\/i><span style=\"font-weight: 400;\"> menu item will display the same results. The bit architecture can be found in the version string, as shown in Table 2.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Table 2 &#8211; Determining CoSort bit architecture.<\/strong><\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-large wp-image-13575 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-2-1024x88.png\" alt=\"CoSort version\" width=\"1024\" height=\"88\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-2-1024x88.png 1024w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-2-300x26.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-2-768x66.png 768w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-2.png 1193w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Determine the correct bit architecture. You will need it to identify the correct ODBC Manager to use, and to download and install the proper ODBC drivers. The use of ODBC Managers and drivers on Linux, Unix, and the Mac, is more complicated, and beyond the scope of this article.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The examples here will use the ODBC Manager supplied by Microsoft with Windows. If you use another operating system and need help, contact your IRI representative.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">ODBC connections are identified with a unique identifier known as a Data Source Name (DSN). Microsoft supplies the <\/span><b>ODBC Data Source Administrator<\/b><span style=\"font-weight: 400;\"> program with Windows, for defining and configuring DSNs.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It is important to choose the ODBC Administrator program with the correct bit architecture to match the application needing database access, and the drivers. Choose the program from Table 3. It is confusing that the 32-bit program is in the folder SysWOW64, while the 64-bit program is in the folder System32.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Table 3 &#8211; ODBC Data Source Administrator programs.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-13579 size-full aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-3.png\" alt=\"ODBC version\" width=\"644\" height=\"102\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-3.png 644w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-3-300x48.png 300w\" sizes=\"(max-width: 644px) 100vw, 644px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">If you have an Oracle database server installed on your workstation, then you probably have an Oracle ODBC driver already installed. You can check for the presence of an Oracle ODBC driver by running the ODBC Data Source Administrator program.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Look in the <\/span><i><span style=\"font-weight: 400;\">Drivers<\/span><\/i><span style=\"font-weight: 400;\"> tab for an entry similar to the one shown in Figure 4, The name <\/span><b>must<\/b><span style=\"font-weight: 400;\"> start with the word <\/span><b>Oracle<\/b><span style=\"font-weight: 400;\">. You <\/span><b>cannot<\/b><span style=\"font-weight: 400;\"> use the driver named <\/span><b>Microsoft ODBC for Oracle<\/b><span style=\"font-weight: 400;\">, this one will <\/span><b>not<\/b><span style=\"font-weight: 400;\"> work.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 4 &#8211; Oracle driver in ODBC Data Source Administrator program.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13581 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-4.png\" alt=\"ODBC admin\" width=\"594\" height=\"421\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-4.png 594w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-4-300x213.png 300w\" sizes=\"(max-width: 594px) 100vw, 594px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">The drivers are listed in alphabetical order. If you do not see an Oracle driver present, then you will need to install an Oracle client. Oracle has a different version of the client for each database version. Typically, you can use a newer client with an older database, but not necessarily the other way around.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Suppose that you want to install the 32-bit client for Oracle 18c. At the time this document was written, the installer was available for download from Oracle. Go to the <\/span><a href=\"https:\/\/www.oracle.com\/database\/technologies\/oracle18c-windows-180000-downloads.html\"><span style=\"font-weight: 400;\">download page<\/span><\/a><span style=\"font-weight: 400;\">, and look for the text <\/span><i><span style=\"font-weight: 400;\">Oracle Database 18c Client (18.3) for Microsoft Windows x86 (32-bit)<\/span><\/i><span style=\"font-weight: 400;\">, as shown in Figure 5.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Download the linked file <\/span><i><span style=\"font-weight: 400;\">NT_180000_client.zip<\/span><\/i><span style=\"font-weight: 400;\">. You will need an online Oracle Account to download the client installer; you can register for free if you do not have a login.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 5 &#8211; Oracle client download which includes ODBC and JDBC drivers.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-5.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13582 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-5.png\" alt=\"ODBC client download\" width=\"860\" height=\"351\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-5.png 860w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-5-300x122.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-5-768x313.png 768w\" sizes=\"(max-width: 860px) 100vw, 860px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Unzip the downloaded zip file into a temporary folder to install the client. Go to the temporary folder, and look in the <\/span><i><span style=\"font-weight: 400;\">client32<\/span><\/i><span style=\"font-weight: 400;\"> folder for <\/span><i><span style=\"font-weight: 400;\">setup.exe<\/span><\/i><span style=\"font-weight: 400;\">, you will run this program to install the client. You will need Administrator privileges to install the client.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Choose an installation type as shown in Figure 6. For just the ODBC driver, JDBC driver, and SQL*Plus command line utility, choose the <\/span><i><span style=\"font-weight: 400;\">Instant Client<\/span><\/i><span style=\"font-weight: 400;\"> type. If you want to install all of the management tools, choose the <\/span><i><span style=\"font-weight: 400;\">Administrator<\/span><\/i><span style=\"font-weight: 400;\"> type.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Follow the instructions in the installer wizard. You can probably accept the default values for any additional options. There is a help button at the bottom of the installer wizard. For more help on this particular installer, Oracle has additional <\/span><a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/ntcli\/index.html\"><span style=\"font-weight: 400;\">documentation available online<\/span><\/a><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 6 &#8211; Oracle Client installation type.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-6.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13583 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-6.png\" alt=\"Client install wizard\" width=\"786\" height=\"593\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-6.png 786w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-6-300x226.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-6-768x579.png 768w\" sizes=\"(max-width: 786px) 100vw, 786px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Once you have the ODBC drivers installed, you can configure a DSN for an Oracle database connection. Run the ODBC Data Source Administrator program specified in Table 3. Select the tab to create either a <\/span><i><span style=\"font-weight: 400;\">User DSN<\/span><\/i><span style=\"font-weight: 400;\"> or a <\/span><i><span style=\"font-weight: 400;\">System DSN<\/span><\/i><span style=\"font-weight: 400;\">. a User DSN will only be visible to the user who created it.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">A System DSN is visible to all users of that workstation. It is most typical to create a User DSN, so select that tab. Select the <\/span><i><span style=\"font-weight: 400;\">Add<\/span><\/i><span style=\"font-weight: 400;\"> button to create a new connection DSN. Select the correct Oracle driver as shown in Figure 7, then select the <\/span><i><span style=\"font-weight: 400;\">Finish<\/span><\/i><span style=\"font-weight: 400;\"> button.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 7 &#8211; Selecting the Oracle ODBC driver.<\/strong><\/p>\n<p style=\"text-align: center;\"><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-7.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-13586\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-7.png\" alt=\"New data source\" width=\"464\" height=\"352\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-7.png 464w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-7-300x228.png 300w\" sizes=\"(max-width: 464px) 100vw, 464px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">You will be presented with the <\/span><i><span style=\"font-weight: 400;\">Oracle ODBC Driver Configuration<\/span><\/i><span style=\"font-weight: 400;\"> dialog, like the one shown in Figure 9. Name your connection. This is the DSN. It is best to avoid whitespace and punctuation in the DSN, except for possibly underscores, hyphens, and periods.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Make the name descriptive of the database to which you are connecting. The TNS Service Name can vary based on the Oracle connection method being used.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The example in Figure 8 uses EZCONNECT, and the example details that were gathered in Table 8.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Table 8 &#8211; Details for an example database connection.<\/strong><\/p>\n<p><a href=\"http:\/\/www.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-8.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-large wp-image-13587 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-8-1024x203.png\" alt=\"Complete table\" width=\"1024\" height=\"203\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-8-1024x203.png 1024w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-8-300x60.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-8-768x152.png 768w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-table-8.png 1244w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">The format for an EZCONNECT connection string is: <\/span><b>host<\/b><span style=\"font-weight: 400;\">:<\/span><b>port<\/b><span style=\"font-weight: 400;\">\/<\/span><b>ServiceName<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Note that there is no separate place in the dialog to save the password. But Oracle allows you to save the password by appending it to the Username, separating it with a forward slash, like shown in the example details in Figure 9.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Although the password is stored in plain text, the DSN is only visible to the user who is logged onto the workstation at the time, and does not pose a significant security risk in most cases. If you have an IT department with security policies, you may not be allowed to store your password this way. The password may be typed in whenever a connection is established.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It is also possible to store the password in IRI Workbench, where it will be applied to jobs that access the data through this DSN. However, if allowed, saving your password in the DSN as shown below is the most convenient method.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 9 &#8211; Driver configuration dialog with example details.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-9.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13588 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-9.png\" alt=\"ODBC driver configuration\" width=\"559\" height=\"433\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-9.png 559w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-9-300x232.png 300w\" sizes=\"(max-width: 559px) 100vw, 559px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">After configuring these details, select the <\/span><i><span style=\"font-weight: 400;\">Test Connection<\/span><\/i><span style=\"font-weight: 400;\"> button. Hopefully, you will see the <\/span><i><span style=\"font-weight: 400;\">Connection Successful<\/span><\/i><span style=\"font-weight: 400;\"> result, as shown in Figure 10.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If there is a problem with your details or connection, or you have omitted storing the password, you will be presented with the Oracle ODBC Driver Connect dialog, as shown in Figure 11. This will give you the opportunity to supply missing details, or change the details already entered.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If edits enable the connection to succeed, they will have to be applied in the configuration dialog shown in Figure 9 before selecting <\/span><i><span style=\"font-weight: 400;\">OK<\/span><\/i><span style=\"font-weight: 400;\">. There are four tabs of options at the bottom of the configuration dialog. Change these options only if you are sure that they are needed.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 10 &#8211; A successful connection test.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-10.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13589 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-10.png\" alt=\"Connection successful\" width=\"160\" height=\"133\" \/><\/a><\/p>\n<p style=\"text-align: center;\"><strong>Figure 11 &#8211; A chance to try other details, or enter a password.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-11.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13590 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-11.png\" alt=\"ODBC driver connect\" width=\"266\" height=\"214\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Now that you hopefully have a working ODBC DSN, you can set up a JDBC connection in IRI Workbench. You need to create a JDBC connection in IRI Workbench with the same exact details that were used in the ODBC DSN. The ODBC DSN and JDBC connection will be linked together in the preferences in IRI Workbench later.<\/span><\/p>\n<h2><b>JDBC<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Selecting JDBC drivers is a little easier than ODBC, as JDBC drivers do not have any bit architecture. Depending on how you installed the Oracle ODBC client, you probably have the JDBC drivers already installed. Look for a folder named <\/span><i><span style=\"font-weight: 400;\">jdbc<\/span><\/i><span style=\"font-weight: 400;\">, under the install folder for the Oracle client. Inside of the <\/span><i><span style=\"font-weight: 400;\">jdbc<\/span><\/i><span style=\"font-weight: 400;\"> folder should be a folder named <\/span><i><span style=\"font-weight: 400;\">lib<\/span><\/i><span style=\"font-weight: 400;\">, with the JDBC java archive (JAR) files inside. The name of the JAR file to use will vary by Oracle and Java version, in general, you will use the driver file with a name like <\/span><i><span style=\"font-weight: 400;\">odbc8<\/span><\/i><span style=\"font-weight: 400;\">.jar, where the number will relate to the version of Java being used, and may be different.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">If the JAR files are not already installed, by either the database server or client installation, then you can search for an appropriate download on the <\/span><a href=\"https:\/\/www.oracle.com\/database\/technologies\/appdev\/jdbc-ucp-183-downloads.html\"><span style=\"font-weight: 400;\">Oracle website<\/span><\/a><span style=\"font-weight: 400;\">.<\/span><\/p>\n<h2><b>IRI Workbench<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">IRI Workbench is the Integrated Development Environment (IDE) built on Eclipse\u2122 for creating, maintaining, and executing job scripts for all IRI products. In addition to working with jobs for IRI products like CoSort, RowGen, FieldShield, and NextForm, it is also the main user interface for the Voracity platform which includes them.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">A JDBC connection within IRI Workbench is used for many purposes, including:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Examining data values for job design, data classification, and data discovery.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Browsing table and schema structure.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Automatic and manual metadata extraction, conversion, and exchange.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Executing SQL commands and queries, and examining results.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Bulk extraction and loading of data values to and from flat files.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">Generation of entity relationship (ER) diagrams.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">There are several ways database connections can be created in IRI Workbench. The most common way is with the Data Source Explorer (DSE) view. The DSE view is a navigator, normally docked along the left hand edge of the IRI Workbench window. If it has been closed, there are two easy ways to open it again:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">From the main menu, select <\/span><i><span style=\"font-weight: 400;\">Window &gt; Perspective &gt; Reset Perspective<\/span><\/i><span style=\"font-weight: 400;\">.<\/span><\/li>\n<li style=\"font-weight: 400;\"><span style=\"font-weight: 400;\">From the main menu, select <\/span><i><span style=\"font-weight: 400;\">Window &gt; Show View &gt; Data Source Explorer<\/span><\/i><span style=\"font-weight: 400;\">.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">A database connection in IRI Workbench is referred to as a <\/span><i><span style=\"font-weight: 400;\">Connection Profile<\/span><\/i><span style=\"font-weight: 400;\">, sometimes shortened to just <\/span><i><span style=\"font-weight: 400;\">Profile<\/span><\/i><span style=\"font-weight: 400;\">. To create a new profile from the DSE view, select the <\/span><i><span style=\"font-weight: 400;\">New Connection Profile<\/span><\/i><span style=\"font-weight: 400;\"> button from the mini-toolbar in the view, as shown in Figure 12.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 12 &#8211; New Connection Profile toolbar button in DSE.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-12.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-13591 size-full\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-12.png\" alt=\"New Connection Profile\" width=\"368\" height=\"104\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-12.png 368w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-12-300x85.png 300w\" sizes=\"(max-width: 368px) 100vw, 368px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">On the first page of the <\/span><i><span style=\"font-weight: 400;\">New Connection Profile<\/span><\/i><span style=\"font-weight: 400;\"> wizard, choose <\/span><b>Oracle<\/b><span style=\"font-weight: 400;\"> from the list of <\/span><i><span style=\"font-weight: 400;\">Connection Profile Types<\/span><\/i><span style=\"font-weight: 400;\">. Give the profile a name, similar to the DSN. Make it descriptive, and avoid unnecessary white space or punctuation. Figure 13 shows this page with the same name used for the ODBC DSN.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 13 &#8211; Oracle Connection Profile wizard page.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-13.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13593 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-13.png\" alt=\"New Connection Profile wizard\" width=\"466\" height=\"497\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-13.png 466w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-13-281x300.png 281w\" sizes=\"(max-width: 466px) 100vw, 466px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Select the <\/span><i><span style=\"font-weight: 400;\">Next &gt;<\/span><\/i><span style=\"font-weight: 400;\"> button to go to the next page of the wizard. You will need to create a new driver definition using the icon button as shown in Figure 14.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 14 &#8211; New Driver Definition button.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-14.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13594 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-14.png\" alt=\"New Driver Definition\" width=\"566\" height=\"180\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-14.png 566w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-14-300x95.png 300w\" sizes=\"(max-width: 566px) 100vw, 566px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><i><span style=\"font-weight: 400;\">New Driver Definition<\/span><\/i><span style=\"font-weight: 400;\"> wizard has three tabs. On the first tab, <\/span><i><span style=\"font-weight: 400;\">Name\/Type,<\/span><\/i><span style=\"font-weight: 400;\"> select the <\/span><i><span style=\"font-weight: 400;\">Oracle Thin Driver<\/span><\/i><span style=\"font-weight: 400;\"> template version to match your database version. Figure 15 shows the selection of the template for Oracle 18c database.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 15 &#8211; Name\/Type tab of the New Driver Definition wizard page.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13595 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\" alt=\"New Driver Definition wizard\" width=\"586\" height=\"408\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png 586w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15-300x209.png 300w\" sizes=\"(max-width: 586px) 100vw, 586px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">The second tab, <\/span><i><span style=\"font-weight: 400;\">JAR List<\/span><\/i><span style=\"font-weight: 400;\">, is for specifying the JDBC driver library file. Select the existing JAR file in the <\/span><i><span style=\"font-weight: 400;\">JAR files<\/span><\/i><span style=\"font-weight: 400;\"> list. Then select the Edit JAR\/Zip button on the right, to browse for the location of the JAR file.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Find the location of the Oracle JDBC driver library, discussed in the JDBC section of this document. Figure 16 shows the <\/span><i><span style=\"font-weight: 400;\">JAR list<\/span><\/i><span style=\"font-weight: 400;\"> tab after locating the JDBC driver file.\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 16 &#8211; The JAR list after location the JDBC driver file.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-16.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13596 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-16.png\" alt=\"JAR list\" width=\"586\" height=\"408\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-16.png 586w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-16-300x209.png 300w\" sizes=\"(max-width: 586px) 100vw, 586px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">The third and final tab is the driver <\/span><i><span style=\"font-weight: 400;\">Properties<\/span><\/i><span style=\"font-weight: 400;\"> tab, shown in Figure 17. The only change that you may need to make on this tab is to add a trailing slash to the Connection URL property if it is not already present.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 17 &#8211; The driver properties tab with the trailing slash on the Connection URL.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-17.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13597 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-17.png\" alt=\"Driver properties\" width=\"586\" height=\"408\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-17.png 586w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-17-300x209.png 300w\" sizes=\"(max-width: 586px) 100vw, 586px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Select the OK button to complete the driver definition. Now you can fill in the connection details properties with the same values that were used from Table 8 in the ODBC DSN creation section, and select the <\/span><i><span style=\"font-weight: 400;\">Save password<\/span><\/i><span style=\"font-weight: 400;\"> checkbox.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Figure 18 shows the Connection Details page, all ready to try the <\/span><i><span style=\"font-weight: 400;\">Test Connection<\/span><\/i><span style=\"font-weight: 400;\"> button. If you do not see a <\/span><i><span style=\"font-weight: 400;\">Success<\/span><\/i><span style=\"font-weight: 400;\"> message box, check the details on the Error message box for additional information. This may indicate a problem with the database server, network connection, or the connection details.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 18 &#8211; The completed connection details page, ready to test the database connection.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-18.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-13598 size-full\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-18.png\" alt=\"New Connection Profile properties\" width=\"511\" height=\"558\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-18.png 511w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-18-275x300.png 275w\" sizes=\"(max-width: 511px) 100vw, 511px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">If the test is successful, you can select the <\/span><i><span style=\"font-weight: 400;\">Finish<\/span><\/i><span style=\"font-weight: 400;\"> button, and be connected to the database. Even if the test fails, you may still wish to select the <\/span><i><span style=\"font-weight: 400;\">Finish<\/span><\/i><span style=\"font-weight: 400;\"> button, this will preserve the work that you have done so far, while you research the error. You will be able to edit the connection profile properties later, fix any problems, and try again.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Figure 19 shows the DSE view after connecting successfully. Oracle uses a large number of internal schemas, and depending on the privileges granted to your user, you may see quite a few schemas besides the ones with the data values of interest to you.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The connection profile can be filtered at the Schema, and even table, level. This will make it easier to locate and work with the data values of interest to you.\u00a0\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 19 &#8211; DSE view with no schema filtering.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-19.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13599 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-19.png\" alt=\"DSE schemas \" width=\"316\" height=\"432\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-19.png 316w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-19-219x300.png 219w\" sizes=\"(max-width: 316px) 100vw, 316px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">The simplest type of schema filtering will hide all of the schemas except the one owned by the configured user. Right-click the Schemas folder to display the context menu. Select the <\/span><i><span style=\"font-weight: 400;\">Properties<\/span><\/i><span style=\"font-weight: 400;\"> from the context menu, as shown in Figure 20.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 20 &#8211; Selecting schema properties from the context menu.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-20.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13600 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-20.png\" alt=\"Schema properties\" width=\"262\" height=\"148\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">This will display the <\/span><i><span style=\"font-weight: 400;\">Filter Properties<\/span><\/i><span style=\"font-weight: 400;\"> dialog, shown in Figure 21. Start by deselecting the <\/span><i><span style=\"font-weight: 400;\">Disable filter<\/span><\/i><span style=\"font-weight: 400;\"> checkbox near the bottom. Then, fill in the name of the USER schema in the Expression group at the top. Select the Apply and Close button when you are done.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This will enable simple filtering, showing and processing only the schemas which match the expression. Much more complex filtering can be performed. Check out the IRI Blog article <\/span><a href=\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-filtering-iri-workbench\/\"><span style=\"font-weight: 400;\">Table Filtering in IRI Workbench<\/span><\/a><span style=\"font-weight: 400;\"> for more information.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 21 &#8211; Simple schema filtering.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-21.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-13601 size-full\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-21.png\" alt=\"Schema filtering\" width=\"818\" height=\"558\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-21.png 818w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-21-300x205.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-21-768x524.png 768w\" sizes=\"(max-width: 818px) 100vw, 818px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Refresh the Schema list from the same context menu used before. The only schema now visible is the one belonging to the user, as shown in Figure 22.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 22 &#8211; DSE view after schema filtering.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-22.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13602 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-22.png\" alt=\"Schema with filter\" width=\"315\" height=\"170\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-22.png 315w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-22-300x162.png 300w\" sizes=\"(max-width: 315px) 100vw, 315px\" \/><\/a><\/p>\n<h2><b>IRI Data Connection Registry<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">All that remains to be done is to reconcile the ODBC DSN and the JDBC connection profile. When creating jobs in IRI Workbench using the JDBC connection profile, it is necessary to know which ODBC DSN corresponds to the same database and user.\u00a0\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Open the Data Connection Registry in the Preferences by selecting <\/span><i><span style=\"font-weight: 400;\">Window &gt; Preferences<\/span><\/i><span style=\"font-weight: 400;\"> from the main menu. Navigate to <\/span><i><span style=\"font-weight: 400;\">IRI &gt; Data Connection Registry<\/span><\/i><span style=\"font-weight: 400;\"> in the tree on the left hand side. The registry page is shown in Figure 23.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 23 &#8211; The Data Connection Registry preference page.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-23.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-13603 size-full\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-23.png\" alt=\"Data Connection Registry preferences\" width=\"846\" height=\"470\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-23.png 846w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-23-300x167.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-23-768x427.png 768w\" sizes=\"(max-width: 846px) 100vw, 846px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">There should be a row in the table for every DSN that has been configured in the ODBC Data Source Administrator. If you do not see the DSN that matches your Data Connection Profile, try selecting the <\/span><i><span style=\"font-weight: 400;\">Refresh<\/span><\/i><span style=\"font-weight: 400;\"> button on the right hand side of the page.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Highlight the row for the DSN that should be mapped to your Data Connection Profile, then select the <\/span><i><span style=\"font-weight: 400;\">Edit<\/span><\/i><span style=\"font-weight: 400;\"> button on the right hand side of the page. This will display the <\/span><i><span style=\"font-weight: 400;\">Data Connection Registry<\/span><\/i><span style=\"font-weight: 400;\"> dialog, shown in Figure 24.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 24 &#8211; The Data Connection Registry dialog.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-24.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13604 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-24.png\" alt=\"Data Connection Registry\" width=\"511\" height=\"572\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-24.png 511w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-24-268x300.png 268w\" sizes=\"(max-width: 511px) 100vw, 511px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">You may optionally apply the same schema filter as was used in the Data Connection Profile. If you did <\/span><b>not<\/b><span style=\"font-weight: 400;\"> save the username or\u00a0 password in the DSN, you may optionally save it in this dialog. The only <\/span><i><span style=\"font-weight: 400;\">required <\/span><\/i><span style=\"font-weight: 400;\">information in this dialog is the <\/span><i><span style=\"font-weight: 400;\">Connection Profile<\/span><\/i><span style=\"font-weight: 400;\"> at the bottom.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Select the OK button when you are done. Make sure that the checkbox in the <\/span><i><span style=\"font-weight: 400;\">Active<\/span><\/i><span style=\"font-weight: 400;\"> column is selected, as shown in Figure 25, and then select the <\/span><i><span style=\"font-weight: 400;\">Apply and Close<\/span><\/i><span style=\"font-weight: 400;\"> button.<\/span><\/p>\n<p style=\"text-align: center;\"><strong>Figure 25 &#8211; Properly configured DSN entry in the Data Connection Registry.<\/strong><\/p>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-25.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-13605 size-full aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-25.png\" alt=\"Data Connection Registry complete\" width=\"846\" height=\"470\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-25.png 846w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-25-300x167.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-25-768x427.png 768w\" sizes=\"(max-width: 846px) 100vw, 846px\" \/><\/a><\/p>\n<p><span style=\"font-weight: 400;\">Congratulations, you are now ready to begin working with the data in your connected Oracle database. See <\/span><a href=\"https:\/\/www.iri.com\/services\/training\/courseware\"><span style=\"font-weight: 400;\">this index page<\/span><\/a><span style=\"font-weight: 400;\"> of how-to Blog articles on working with Oracle (and other) data from IRI Workbench.<\/span><\/p>\n<h2><b>Glossary<\/b><\/h2>\n<p><a href=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-glossary.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-13567 aligncenter\" src=\"\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-glossary.png\" alt=\"Glossary\" width=\"898\" height=\"871\" srcset=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-glossary.png 898w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-glossary-300x291.png 300w, https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-glossary-768x745.png 768w\" sizes=\"(max-width: 898px) 100vw, 898px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Overview Connecting to a database like Oracle can be the most frustrating part of application installation and configuration. The many options available with JDBC and ODBC drivers can be daunting. The acronyms alone are enough to cause a headache.\u00a0 The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires<\/p>\n<div><a class=\"btn-filled btn\" href=\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\" title=\"Connecting IRI Software to Oracle\">Read More<\/a><\/div>\n","protected":false},"author":2,"featured_media":13595,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[91,3],"tags":[44,1067,100,1468,520,850,956,75,50,68,161],"class_list":["post-13563","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-iri-workbench","category-vldb-operations","tag-cosort","tag-database-connections","tag-etl","tag-ezconnect","tag-iri-fieldshield","tag-iri-workbench","tag-jdbc","tag-odbc","tag-oracle","tag-sortcl","tag-windows"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Connecting IRI Software to Oracle - IRI<\/title>\n<meta name=\"description\" content=\"The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for access to the data and metadata\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Connecting IRI Software to Oracle - IRI\" \/>\n<meta property=\"og:description\" content=\"The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for access to the data and metadata\" \/>\n<meta property=\"og:url\" content=\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"IRI\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-27T21:43:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-08T15:26:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\" \/>\n\t<meta property=\"og:image:width\" content=\"586\" \/>\n\t<meta property=\"og:image:height\" content=\"408\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Don Purnhagen\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Don Purnhagen\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\"},\"author\":{\"name\":\"Don Purnhagen\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/5f6325245947c8b8f0a0a76ef81580ec\"},\"headline\":\"Connecting IRI Software to Oracle\",\"datePublished\":\"2020-03-27T21:43:16+00:00\",\"dateModified\":\"2023-12-08T15:26:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\"},\"wordCount\":3122,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\",\"keywords\":[\"CoSort\",\"database connections\",\"ETL\",\"EZCONNECT\",\"IRI FieldShield\",\"IRI Workbench\",\"JDBC\",\"ODBC\",\"Oracle\",\"SortCL\",\"Windows\"],\"articleSection\":[\"IRI Workbench\",\"VLDB\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\",\"url\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\",\"name\":\"Connecting IRI Software to Oracle - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\",\"datePublished\":\"2020-03-27T21:43:16+00:00\",\"dateModified\":\"2023-12-08T15:26:09+00:00\",\"description\":\"The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for access to the data and metadata\",\"breadcrumb\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage\",\"url\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\",\"contentUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png\",\"width\":586,\"height\":408,\"caption\":\"New Driver Definition wizard\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/beta.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Connecting IRI Software to Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#website\",\"url\":\"https:\/\/beta.iri.com\/blog\/\",\"name\":\"IRI\",\"description\":\"Total Data Management Blog\",\"publisher\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/beta.iri.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#organization\",\"name\":\"IRI\",\"url\":\"https:\/\/beta.iri.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"contentUrl\":\"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"width\":750,\"height\":206,\"caption\":\"IRI\"},\"image\":{\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/5f6325245947c8b8f0a0a76ef81580ec\",\"name\":\"Don Purnhagen\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/39a152e4ffaf1f20e40d0c6e56a9a91d?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/39a152e4ffaf1f20e40d0c6e56a9a91d?s=96&d=blank&r=g\",\"caption\":\"Don Purnhagen\"},\"url\":\"https:\/\/beta.iri.com\/blog\/author\/donp\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Connecting IRI Software to Oracle - IRI","description":"The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for access to the data and metadata","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/","og_locale":"en_US","og_type":"article","og_title":"Connecting IRI Software to Oracle - IRI","og_description":"The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for access to the data and metadata","og_url":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/","og_site_name":"IRI","article_published_time":"2020-03-27T21:43:16+00:00","article_modified_time":"2023-12-08T15:26:09+00:00","og_image":[{"width":586,"height":408,"url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png","type":"image\/png"}],"author":"Don Purnhagen","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Don Purnhagen","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#article","isPartOf":{"@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/"},"author":{"name":"Don Purnhagen","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/5f6325245947c8b8f0a0a76ef81580ec"},"headline":"Connecting IRI Software to Oracle","datePublished":"2020-03-27T21:43:16+00:00","dateModified":"2023-12-08T15:26:09+00:00","mainEntityOfPage":{"@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/"},"wordCount":3122,"commentCount":0,"publisher":{"@id":"https:\/\/beta.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage"},"thumbnailUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png","keywords":["CoSort","database connections","ETL","EZCONNECT","IRI FieldShield","IRI Workbench","JDBC","ODBC","Oracle","SortCL","Windows"],"articleSection":["IRI Workbench","VLDB"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/","url":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/","name":"Connecting IRI Software to Oracle - IRI","isPartOf":{"@id":"https:\/\/beta.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage"},"image":{"@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage"},"thumbnailUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png","datePublished":"2020-03-27T21:43:16+00:00","dateModified":"2023-12-08T15:26:09+00:00","description":"The IRI software stack uses both JDBC and ODBC drivers for different purposes. IRI Workbench requires JDBC connections for access to the data and metadata","breadcrumb":{"@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#primaryimage","url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png","contentUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png","width":586,"height":408,"caption":"New Driver Definition wizard"},{"@type":"BreadcrumbList","@id":"https:\/\/beta.iri.com\/blog\/iri\/iri-workbench\/connecting-iri-software-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/beta.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Connecting IRI Software to Oracle"}]},{"@type":"WebSite","@id":"https:\/\/beta.iri.com\/blog\/#website","url":"https:\/\/beta.iri.com\/blog\/","name":"IRI","description":"Total Data Management Blog","publisher":{"@id":"https:\/\/beta.iri.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/beta.iri.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/beta.iri.com\/blog\/#organization","name":"IRI","url":"https:\/\/beta.iri.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","contentUrl":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","width":750,"height":206,"caption":"IRI"},"image":{"@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/5f6325245947c8b8f0a0a76ef81580ec","name":"Don Purnhagen","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/beta.iri.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/39a152e4ffaf1f20e40d0c6e56a9a91d?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/39a152e4ffaf1f20e40d0c6e56a9a91d?s=96&d=blank&r=g","caption":"Don Purnhagen"},"url":"https:\/\/beta.iri.com\/blog\/author\/donp\/"}]}},"jetpack_featured_media_url":"https:\/\/beta.iri.com\/blog\/wp-content\/uploads\/2020\/03\/iri-oracle-figure-15.png","_links":{"self":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts\/13563"}],"collection":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/comments?post=13563"}],"version-history":[{"count":30,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts\/13563\/revisions"}],"predecessor-version":[{"id":17917,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/posts\/13563\/revisions\/17917"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/media\/13595"}],"wp:attachment":[{"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=13563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=13563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beta.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=13563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}