Connecting to Oracle, Db2 and Other Databases

Audience: System/Security Administrators, Low-code Engineers

Skill Prerequisites: System Administration and Maintenance, SQL

Plant an App runs on Microsoft SQL Server. Its own database is always SQL Server, and SQL Server databases can be used everywhere in the platform.

Plant an App can also read and write data in other database engines, such as Oracle and IBM Db2. It connects through standard .NET data providers (ADO.NET) that you install on the web server:

• The database vendor's own provider, for example Oracle Data Provider for .NET (ODP.NET) or the IBM Data Server Provider for .NET
• ODBC, with the vendor's ODBC driver

There are two ways to set up a connection:

1. A Database connector (recommended). You create it in the Connectors area, without editing files, and select it in actions. Its connection string is stored encrypted.
2. A connection string in web.config (advanced setup). This is where you choose the provider for Oracle, Db2 or ODBC.

For SQL Server, a connector is all you need. For Oracle, Db2 or ODBC, you set up the web.config entry once, and the connector points to it by name.

Typical Use Cases

• Read reference data from an Oracle or Db2 system, for example employees, cost centers or forest districts, and show it in a form or listing
• Write a record to another system's database when a workflow runs
• Load a list of rows from Oracle or Db2 and loop over it in a workflow
• Export the result of a query on another database to Excel

How It Works

Every feature that accepts a connection string or a Database connector picks the connection like this:

1. If the value is the name of a connection string in web.config, Plant an App creates the connection with that entry's providerName. This is how you reach Oracle, Db2 or any other engine.
2. Any other value is treated as a SQL Server connection string.

A Database connector follows the same rule: its Connection String is either a SQL Server connection string or the name of a web.config entry.

note

Plant an App doesn't ship Oracle, Db2 or ODBC drivers. You install and register the provider on each web server. With several web servers, install it on every server.

Option 1: Database Connector

1. In the Connectors area, click NEW and choose Database Connection.
2. In Connection String, enter:
   • For SQL Server: the full connection string, for example Server=sql-host;Database=HR;User Id=pa_reader;Password=********;Encrypt=True;
   • For Oracle, Db2 or ODBC: the name of the connection string in web.config, for example OracleHR. Set it up first, as in Option 2.
3. Save, then click Test to check that the connection opens.
4. In the action, select the connector in Database, for example in Run SQL Query. Leave Override Connection String empty: when it's filled in, it's used instead of the connector.

warning

Don't type an Oracle, Db2 or ODBC connection string straight into the connector. The connector would open it as a SQL Server connection, and it fails. Put the connection string in web.config and enter its name in the connector.

With a connector, the people who build applications select the connection by name and never see the password. You can also create and update connectors from a workflow with Add Connector and Update Connector.

Option 2: Connection String in web.config (Advanced Setup)

You add a named connection string to web.config, with the provider in providerName. Use its name in a Database connector (Option 1), or directly in Override Connection String. The setup for each database follows in Set Up an Oracle Connection and Set Up a Db2 Connection.

Edit web.config on every web server. Saving the file restarts the site, so plan the change for a quiet moment.

Where You Can Use It

Feature	Use with Oracle / Db2	Notes
Run SQL Query	Yes	Set Override Connection String to the connection string name, or select a Database connector. @File<path> and the :ISO tokens of datetimeoffset columns are SQL Server only.
Create List from SQL	Yes	Loads every row as a list, to loop over or show.
Create Excel from SQL Query	Yes
Listing data source SQL Advanced Query	Yes, read, update and delete	Filtering, sorting and paging are done by Plant an App in memory, so keep the result set small. After an insert, the new row's ID isn't returned.
Database custom tokens	Yes	Use the connection string name.
Database connector Test	Yes	Checks that the connection opens.
Entities, Import List into Database, Listing data sources SQL Select and Database Table, Load Users from SQL, SQL validation in forms, Automation database triggers	No	These work only with SQL Server or with the application database.

Set Up an Oracle Connection

Oracle Data Provider for .NET (recommended)

1. Download ODP.NET, Managed Driver (Oracle.ManagedDataAccess) for .NET Framework from Oracle. It needs no Oracle Client installation.

2. Copy Oracle.ManagedDataAccess.dll into the bin folder of the website.

3. Register the provider in web.config, inside <configuration>. Use the version and public key token of the DLL you installed (see its file properties or Oracle's documentation):

   <system.data>
     <DbProviderFactories>
       <remove invariant="Oracle.ManagedDataAccess.Client" />
       <add name="ODP.NET, Managed Driver"
            invariant="Oracle.ManagedDataAccess.Client"
            description="Oracle Data Provider for .NET, Managed Driver"
            type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.23.1, Culture=neutral, PublicKeyToken=89b483f429c47342" />
     </DbProviderFactories>
   </system.data>

4. Add the connection string in <connectionStrings>:

   <add name="OracleHR"
        connectionString="Data Source=oracle-host:1521/HRPDB;User Id=pa_reader;Password=********;"
        providerName="Oracle.ManagedDataAccess.Client" />

5. Use OracleHR as the connection in the features listed above.

Oracle ODBC Driver

1. Install the 64-bit Oracle ODBC driver on the web server. The IIS application pool is 64-bit, so a 32-bit driver isn't visible to it.

2. Add the connection string. The ODBC provider (System.Data.Odbc) is already registered in .NET Framework:

   <add name="OracleHR"
        connectionString="Driver={Oracle in OraClient19Home1};Dbq=oracle-host:1521/HRPDB;Uid=pa_reader;Pwd=********;"
        providerName="System.Data.Odbc" />

   The driver name must match the name shown in ODBC Data Sources (64-bit), on the Drivers tab. You can also create a system DSN and use DSN=OracleHR;Uid=...;Pwd=...;.

Set Up a Db2 Connection

IBM Data Server Provider for .NET (recommended)

1. Install the IBM Data Server Driver Package (64-bit) on the web server. It includes the .NET provider IBM.Data.DB2.

2. Check that the provider is registered. It must appear in the DbProviderFactories section of machine.config (64-bit, .NET Framework 4). If it doesn't, register it in web.config as shown in IBM's documentation.

3. Add the connection string:

   <add name="Db2Finance"
        connectionString="Server=db2-host:50000;Database=FINANCE;UID=pa_reader;PWD=********;"
        providerName="IBM.Data.DB2" />

Db2 ODBC Driver

1. Install the 64-bit IBM Db2 ODBC driver (included in the IBM Data Server Driver Package).

2. Add the connection string:

   <add name="Db2Finance"
        connectionString="Driver={IBM DB2 ODBC DRIVER};Database=FINANCE;Hostname=db2-host;Port=50000;Protocol=TCPIP;Uid=pa_reader;Pwd=********;"
        providerName="System.Data.Odbc" />

Writing Queries

• Use the target database's SQL dialect. The query is sent as you write it. For example, use FETCH FIRST 10 ROWS ONLY instead of TOP 10, and "Name" or no quotes instead of [Name].
• Tokens in the query are replaced before it runs, and single quotes in their values are doubled. Put text values in quotes yourself: WHERE EMAIL = '[Email]'.
• Bind Tokens are sent as real query parameters, named @Name. How parameters are matched depends on the provider: ODBC matches ? placeholders by position, and ODP.NET matches by position unless the command binds by name. Test your query with your provider. If parameters don't bind as expected, use quoted tokens instead.
• {databaseOwner} and {objectQualifier} are replaced with the values of the application database. Don't use them in queries on other databases.
• Transactions. Run SQL Query runs the script in a transaction by default. If the driver doesn't support transactions, turn Use Transactions off.
• Timeouts. Query Timeout works the same way for every provider.

Security

• Store connection strings with passwords in web.config only on servers you control, and restrict access to the file. Consider encrypting the connectionStrings section with aspnet_regiis.
• Use a database account with only the rights the application needs, for example read-only for reference data.
• Encrypt the network connection to the database with the options of your driver, for example Oracle Native Network Encryption or TLS, or Db2 SSL/TLS.
• See also Data Security.

Troubleshooting

Symptom	Cause	Fix
A SQL Server error, such as A network-related or instance-specific error, when you use the Oracle or Db2 connection	The value isn't the name of a web.config connection string, or the provider in providerName isn't registered. Plant an App then opens the connection as SQL Server.	Check the connection string name. Check that the provider's invariant matches providerName exactly and that the DLL is in bin or installed.
Data source name not found and no default driver specified	The ODBC driver name is wrong, or only the 32-bit driver is installed.	Copy the exact driver name from ODBC Data Sources (64-bit).
Syntax errors in a query that works in SQL Server	The query uses SQL Server syntax.	Rewrite it in the target database's dialect.
A parameter isn't filled in	The provider matches parameters differently.	See Bind Tokens in Writing Queries.

Revised 11/12/2026