This knowledgebase article describes how to establish a connection between M-Files to an Oracle database.
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 18.104.22.168.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.
- To use 64-bit version of the database driver, M-Files server installation must also in 64-bit. You can find 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, M-Files Admin must be run on the server itself, not with a remote vault connection from another workstation.
- If the driver is not found properly 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, make sure 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
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. These settings can be done, for instance, by using Oracle Net Developer. The TNS names are stored into a file named TNSNAMES.ORA. The file has the following structure:
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = Servername)
(Port = 1521)
(CONNECT_DATA = (SID = DEMO)
In the same folder with TNSNAME.ORA, there should be also 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 that this location is defined in Environment variables of the M-Files Server. To open Environment variables, select properties of the computer > Advanced settings > Environment variables. Below illustrated screenshots of these features in Windows 7.
Make sure that in the environment variables there is a variable TNSNAMES defined and its value point 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 UN path, since mapped network drives are not visible to data sources.