Overview
This knowledgebase article describes how to establish a connection between M-Files to an Oracle database.
Solution
First, install Oracle's database drivers on the M-Files server computer. NOTE: M-Files server service must be offline during the driver installation.
- You can get the drivers from http://www.oracle.com/technetwork/topics/winx64soft-089540.html. At this time, driver version 12.1.0.2.0 has been successfully tested.
- You need both "Instant Client Package - Basic" and "Instant Client Package - ODBC" from the page above.
- Select "Accept License Agreement", then download the required packages and follow the installation instructions at the very bottom of the page.
- In addition to the instructions on Oracle's download page, you also need to open an elevated command prompt (run the command prompt as administrator) and run odbc_install.exe in the directory where you unzipped the drivers.
- A full server reboot after the installation is needed.
After driver installation, create an ODBC System DSN (Control Panel > Administrative Tools > ODBC Data Sources (64-bit))
- Use System DSN instead of User DSN, as the User DSNs are not visible to the M-Files Server.
- Give the data source a descriptive name, so you can later identify it when connecting to it via M-Files.
- Provide all the connection settings, including any required authentication details, in the System DSN configuration.
In M-Files Admin, configure a Connection to External Database from the object type or value list which you want to populate from Oracle.
- The data source name you specified for your System DSN should be visible in the OLE DB provider list. Select it to create a connection string to the database.
- Use regular SQL syntax for the SELECT statement.
Notes:
- To use 64-bit version of the database driver, M-Files server installation must also be in 64-bit. You can find a 32-bit version of the driver here: http://www.oracle.com/technetwork/topics/winsoft-085727.html.
- Because M-Files Admin can only find the ODBC connections on the local machine, it must be run on the server itself, not with a remote vault connection from another workstation.
- If the driver is not found correctly in M-Files Admin, you might need to add the path to the Oracle ODBC driver's installation folder in Windows System Variables as described at the bottom of the Oracle driver download page: http://www.oracle.com/technetwork/topics/winx64soft-089540.html
- In case of connection problems, ensure that M-Files server can connect to Oracle database server over TCP. This can be easily tested with command prompt command: telnet <servername> <port>. The default port is 1521.
- If you get the error "ORA-12154: TNS: could not resolve the connect identifier specified", it means that either the driver does not find TNSNAMES.ORA or SQLNET.ORA file, or these files are not correctly configured. See the solution to this problem below.
- General information on how to create connections to external databases can be found in the M-Files User Guide: https://www.m-files.com/user-guide/latest/eng/#Connection_to_external_database.html
Configuring TNSNAMES
Defining ODBC connections to Oracle database differs to some extent from many other common databases such as MS SQL Server, MySQL or Pervasive.
Before creating a connection, you must define a TNS name of the database to be connected. For instance, these settings can be done by using Oracle Net Developer. The TNS names are stored in a file named TNSNAMES.ORA. The file has the following structure:
|
demo.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = Servername) (Port = 1521) ) ) (CONNECT_DATA = (SID = DEMO) ) ) |
In the same folder as TNSNAME.ORA, there should also be a file named SQLNET.ORA. Its contents are typically similar to:
|
AUTOMATIC_IPC = OFF TRACE_LEVEL_CLIENT = OFF names.directory_path = (TNSNAMES) names.default_domain = world name.default_zone = world |
Once these files are found, you should define this location in the Environment variables of the M-Files Server. To open Environment variables, select properties of the computer > Advanced settings > Environment variables. Below are illustrated screenshots of these features in Windows 7.
Ensure that in the environment variables there is a variable TNSNAMES defined, and its value points to the folder where TNSNAMES.ORA and SQLNET.ORA are located. Also make sure that these files are located on the local computer. If the folder is on network, make sure that TNSNAMES value refers to it with UNC path since mapped network drives are not visible to data sources. Make sure to add the variable under System variables instead of User variables.
Remark
In the case of using Ground Link in M-Files Cloud, and in order to establish a connection to an on-premises Oracle database, if you encounter a protocol adapter error, you may use the following workaround.
Basically, you would need to provide the Data Source value as one of the parameters in the advanced options field. So, within the EOT connector's UI configuration, you can leave the Data Source field empty and instead provide the value as one of the parameters in the Advanced Options field (the value will be added to the connection string).
Afterwards the configuration from advanced tab (JSON) should look like the following:
"connectionString": { "provider": "OraOLEDB.Oracle", "advanced": "Data Source=datasource_name" }
