Centralized OIC Lookup for Dynamic Email Routing, Improving Integration Flexibility, Automation, and Maintainability Across Business Transactions.

In modern enterprise integration landscapes, operational efficiency depends heavily on how effectively systems communicate, respond to events, and handle exceptions. As organizations grow, integrations become more complex and the number of transactions flowing through middleware platforms such as Oracle Integration Cloud (OIC) continues to expand. One of the most overlooked but critical components of a scalable integration architecture is the management of dynamic values—especially those that frequently change, such as email recipients for notifications, error handlers, and support teams. Hard-coding these values inside individual integrations creates long-term maintenance challenges, slows down development cycles, and increases the risk of operational failures when updates are required.

To address this, Oracle Integration Cloud provides Lookups, a powerful, centralized mechanism that stores reusable mappings for use across multiple integrations. By using Lookups, organizations gain a flexible structure where business rules—such as routing emails based on transaction type—can be managed independently from integration logic. The result is a cleaner, more modular, and future-proof automation environment.

This tutorial provides a detailed, step-by-step walkthrough of how to create, configure, validate, and deploy a custom lookup within OIC. Using the example of routing Success, Error, and Fault notifications for transaction categories like AP and GL, we explore how Lookups can streamline communication flows and ensure consistency across integrations. Each step in this guide is written to reflect real-world best practices used by experienced OIC consultants, ensuring clarity not only in execution but also in understanding why each action matters.

Whether you are implementing your first lookup or refining your enterprise integration architecture, this comprehensive guide will help you build a scalable, maintainable email routing solution that enhances operational reliability and supports long-term digital transformation.

1. Oracle Integration Cloud Environment

You must have access to a valid OIC instance (Development, Test, or Sandbox). Ensure the environment is stable and accessible through a supported browser such as Google Chrome or Microsoft Edge.

2. User Roles and Permissions

A user account with one of the following roles is required:

1. Service Developer
2. Integration Developer
3. Service Administrator

These roles allow you to create Lookups, modify integrations, and run validation tests. Read-only roles will not be sufficient.

3. Business Mapping Requirements

Prepare the list of values you plan to store in the lookup, such as:

1. Transaction Codes (e.g., AP, GL, INV)
2. Success Email IDs
3. Error Handling Email IDs
4. Fault Notification Email IDs
5. This ensures you can populate the lookup accurately.

4. Integration Samples for Testing

Have at least one test integration ready to validate lookup behavior. It may include:

1. Mapper activities
2. Notification steps
3. Switch/Decision activities
4. LookupValue function calls

5. Connectivity and System Stability

A reliable internet connection is essential while working in OIC; unstable sessions may cause incomplete saves or UI issues.

6. Optional Tools

1. CSV/JSON files if you plan to bulk-import lookup data
2. A version control or documentation tool (Confluence, Notion, SharePoint) for capturing changes
3. Email testing tools to verify correct delivery of notification mappings

When you first sign into Oracle Integration Cloud (OIC) you land on the platform’s home/dashboard page. This screen is intentionally high-level: it gives you entry points to Design, Integrations, Insight, Observability, Visual Builder, and other consoles. For the task of creating a lookup (a reusable mapping table), you must move into the Design area. Click the left-side navigation icon (three bars) if the left panel is collapsed, then select Design. If your tenancy is customized, there might be multiple tiles — choose the “Design” tile or the “Design” menu entry.

Why this navigation matters: OIC separates runtime and design-time artifacts. Lookups are a design-time artifact used by integrations at runtime; therefore you must create them from the Design console. Inside Design you’ll find sub-sections like Integrations, Connections, Lookups, Libraries, Packages, Agents, and Adapters. Each sub-section maps to a type of artifact you create and manage. Think of Design as the development workspace, whereas Integrations is where runtime flows are built and executed.

Practical tips: ensure you’re using a browser supported by Oracle (Chrome/Edge/Firefox), and that your session hasn’t timed out — stale sessions sometimes display stale UI or unpredictable behavior. If you don’t see the Design link, verify your role: only users with Service Developer or higher typically have access to artifact creation. If your tenancy has strict admin controls you may need to raise a request to an administrator for the necessary privileges. Always take a screenshot (or note the URL) before making changes if you’re working in a shared environment.

Common gotchas: browser extensions that block scripts can prevent UI elements from rendering (e.g., create/save buttons). Disable ad-blockers for your OIC domain while working. Finally, use a stable network — UI operations sometimes fail mid-transaction on flaky connections; if that happens check the browser console for errors and retry.

From the Design console, locate and click Lookups in the left navigation bar. The Lookups page is the central catalog for all lookup artifacts in your project/tenant. Initially, you may see an empty list (as shown in your screenshots) or a table of existing lookups. The interface typically shows columns such as Name, Usage, Domains, Values Mapped, Last Updated, and Status. There’s also an action bar with options to Import, Create, refresh the list, toggle list/grid view, and apply filters.

Why this matters: opening the Lookups page is the first step to creating a centralized repository for any key-value or multi-column mapping you want to re-use. It’s a single source of truth — instead of hard-coding addresses or codes into many integrations, you store them in one lookup and reference them. From the Lookups list you can also import existing lookups (JSON), edit metadata, duplicate entries, and check where lookups are used across integrations.

Detailed UI behavior: if your tenancy has multiple projects, you may have a project filter at the top of the page; choose the right project for lifecycle management. The list supports search and advanced filters — e.g., filter by who last updated the lookup or by status (Draft/Configured). Use the “Updated by” filter to quickly locate items owned by you or a teammate. If you are in a large org, use keywords to narrow results.

Troubleshooting and checks: if the Lookups page returns “No data to display” but you know lookups exist, check the project filter or date-range filters. Also verify your user permissions; read-only users may see but not edit. To speed up navigation in a multi-tenancy setup, create a naming convention (prefix/suffix) so lookups are grouped and easily searchable.

Click the Create button on the Lookups page. A right-side panel slides out prompting for metadata: Name, Keywords, and Description. Name is the primary identifier for the lookup; follow a naming standard (e.g., XX_EMAIL_LKP or LKP_EMAIL_TEAM). The prefix XX_ or CUST_ signals custom artifacts and prevents collisions with Oracle-supplied items. Add meaningful Keywords to aid future searches (e.g., “notification, email, team”). Write a concise Description that explains the lookup’s purpose, e.g., “This lookup stores success, error, and fault email addresses per transaction code.”

Why this step matters: giving proper metadata is crucial for maintainability. Teams often inherit a long list of lookups; well-named and documented lookups reduce onboarding time and prevent accidental deletion. The Name must conform to OIC naming rules (starts with a letter, max length, no unusual special characters). The Keywords field is optional but extremely helpful in large environments.

After entering metadata, click Create. The lookup opens in Draft mode allowing you to add domains (columns) and rows. Draft mode means the artifact is not yet considered ready for runtime use—this prevents half-baked or incomplete data from being consumed by integrations. If you need to revert or abandon the draft, you can delete it; otherwise progress to domain configuration.

Validation and versioning: OIC will enforce certain validations (unique name within the project, acceptable characters). If the Create action fails, read the error message; typical causes include name conflicts or permission constraints. Log your change request or use comments if your organization requires tracking changes.

Once the lookup opens, you’ll see the default columns (Domain_Name_1, Domain_Name_2, etc.). Domains are effectively the columns of your lookup table; they define the data points stored per row. Typical domain names for email mapping are Transaction_Code, SuccessEmail, ErrorEmail, and FaultEmail. Click the dropdown on the domain header and select Edit domain name. Enter a meaningful name, ensure it starts with a letter, contains only permitted characters (letters, numbers, underscore), and is under the maximum length—OIC displays guidance in the edit dialog.

Why domain design matters: choosing appropriate domain names affects integration readability and mapping clarity. For example, Transaction_Code as a domain is intuitive: it clearly identifies the key used to look up the row. Email domains should indicate intent: SuccessEmail vs ErrorEmail makes it obvious when selecting columns in mappers or scripts. Domains are also referenced in lookup retrieval calls; a poorly named domain forces the integrator to remember cryptic names.

Column types & behavior: Lookups typically store text values. If you plan to store multiple email addresses, keep them comma-separated in a single domain (e.g., a@x.com, b@y.com) — this is a common pattern consumed by OIC connectors that send notifications. If you foresee structured data, consider a different approach (e.g., storing a JSON string), but remember that lookup UI is tabular and optimized for simple text columns.

Best practices: limit domain count to only what’s necessary — too many columns increase maintenance complexity. Keep the primary key column (the one you’ll query by) as the leftmost domain for discoverability. Document each domain’s purpose in the lookup description or an internal wiki so other integrators understand how to use it.

If your lookup needs more than the initial two domains, click the ( + ) add-column button (often on the right side of the table header). Each added domain will appear with a default header name; immediately rename it to something meaningful (e.g., ErrorEmail, FaultEmail). Think through data normalization — for example, if success/error emails share addresses, you can duplicate values or store a group ID that maps to another artifact; however, for simplicity and speed, storing emails directly in separate domains is a common practice.

Why adding domains is essential: additional domains let you capture multiple facets of the mapping in the same table row. In the email routing scenario, each domain corresponds to a different notification type: success, error, and fault. When designing integrations, you will query the lookup for a given Transaction_Code and fetch the appropriate domain depending on the outcome (mapper logic, decision shapes, or notification adapter).

Data entry considerations: while the UI allows free text entry, maintain consistent formatting. If storing multiple recipients, separate them with commas and avoid accidental spaces or trailing commas as some adapters may interpret spaces as part of an address. Validate addresses where possible, or maintain a separate registry to validate email addresses before adding them to production lookups. If your organization requires approval, add a staging lookup in a non-production project to validate entries before copying them to production.

Operational notes: adding domains in Draft mode avoids impacting running integrations. After you save and configure the lookup, changes will be visible for consumption — consider versioning or using projects to control when lookups become available to runtimes.

Now populate the lookup with the mapping rows. Each row should represent one key-based mapping: the primary key column (e.g., Transaction_Code) and every corresponding domain value for that key. In your screenshots the examples are AP and GL as transaction codes with corresponding email lists. Click into the first row’s Transaction_Code cell and type the code (e.g., AP). Move right to SuccessEmail and type the comma-separated recipients (aeronzafar@gmail.com, ap-team@gmail.com). Continue for ErrorEmail and FaultEmail.

Why this matters operationally: rows are the actual data your integrations will read. Use the exact values that your integration expects to query against; casing and whitespace matter in string comparisons. For multi-recipient fields, ensure recipients are validated and current. If you have multiple recipients, choose a consistent ordering or tagging (e.g., primary,secondary) if your emails are processed programmatically.

Large datasets and bulk entry: For many rows, manual entry via the UI is time-consuming. Use the Import feature if you have CSV/JSON data, or script a migration using OIC REST APIs. When importing, ensure columns map correctly and special characters (commas in display names, quotes) are handled. Before bulk import into production, import into a test project and run automated integration tests to confirm lookup retrieval behaves as expected.

Validation & testing: After adding rows, test lookup retrieval inside a sample integration or mapping function. Use simple lookup functions to fetch SuccessEmail for AP and verify expected output. If values don’t return, check for leading/trailing spaces and ensure your query key exactly matches the Transaction_Code. Keep a small test integration to validate lookups without affecting production flows.

After you’ve added all required domains and rows, click Save in the top-right. The lookup will transition from Draft to Configured (or some environments show a “Published” state). This action commits the lookup in the project/tenant and makes it available for use in integration mappings and runtime lookups. Saving also triggers backend validation: unique name check, domain naming rules, and any data length constraints. If OIC finds an issue (e.g., invalid characters, row content exceeding allowed length), it will display an error and prevent saving until fixed.

Why saving is critical: until saved and configured, the lookup is not discoverable by integrations. The configured state indicates the artifact is production-ready. For governance, tie saving to change-control processes — consider annotating the lookup description with who approved it and when, or use your organization’s deployment pipeline (export/import through projects) to move lookups from dev → test → prod.

After saving, perform runtime verification: open an integration where you intend to use the lookup and add a Lookup function or call in the mapper or a script step. Test end-to-end by triggering the integration with a sample payload that includes a Transaction_Code, and confirm the correct SuccessEmail or ErrorEmail is pulled and used in the notification step. Monitor logs and check observability metrics to ensure there are no runtime errors.

Post-deployment best practices: maintain a change log for each lookup and review entries periodically (quarterly or per release cycle) to purge stale email addresses. Use projects for lifecycle management and restrict edit privileges to a small number of maintainers to avoid accidental changes. If your organization requires audit trails, capture who edited the lookup and when — OIC’s UI shows last updated metadata, but you may also export artifact metadata into your CMDB for compliance.

After saving and configuring the lookup, the next critical step is validation. While the lookup now appears in “Configured” state, that does not guarantee that it behaves correctly inside integrations. Validation ensures that each domain, row, and value works exactly as intended when invoked by runtime flows.

Start by reviewing the lookup visually. Scroll through all added rows and confirm that no values contain unwanted spaces, line breaks, or typographical errors. For example, an extra space before an email address can cause delivery failure or incorrect mapping results. Validate that every Transaction_Code is unique; lookups operate on a key-based retrieval method, so duplicate keys can create ambiguity or unexpected behavior.

Next, open a simple integration (or create a temporary test integration). Add a Mapper or Switch activity, then use the built-in lookupValue() function. Enter the following test expression.

Additionally, validate the email routing in a controlled environment. Replace the SuccessEmail output in your integration with a dummy notification step and confirm the list of recipients appears correctly formatted. Ensure commas separate addresses and there are no trailing or leading commas.

Finally, perform peer validation. Ask another integrator or team member to review the lookup structure. Cross-verification helps avoid entry errors and ensures adherence to organizational naming standards. Thorough validation here prevents runtime failures and ensures smooth downstream automation.

Once the lookup is validated and functioning correctly, the next stage is deployment—making it part of your integration landscape in a controlled, traceable, and maintainable manner. Deployment involves using the lookup inside actual integrations, promoting it across environments, and aligning it with organizational governance standards.

Begin by embedding the lookup into the integrations that require dynamic routing. For example, in a file processing or ERP transaction flow, use the lookup inside decision branches or notification steps. Instead of hard-coding email IDs,

This dynamically routes notifications to the correct team based on transaction outcome. This approach ensures future updates require modifying only the lookup, not the integration logic itself.

Next, package the lookup into an OIC Project. Oracle strongly recommends using Projects for versioning, grouping related artifacts, and ensuring clean lifecycle management. Export the project and import it into Test or UAT environments. Follow a proper migration process: Dev → Test → Prod. This avoids inconsistencies and ensures that all environments align with the same mapping structure.

During deployment, enforce governance rules. Assign the lookup an owner—preferably a lead integrator or application engineer—responsible for future updates. Use ticketing systems (Jira, ServiceNow, etc.) to control who can modify lookup values. Since lookups influence email delivery and stakeholder communication, unauthorized changes can create major disruptions.

Before go-live, conduct integrated testing. Trigger transactions of all covered types (AP, GL, etc.) and verify correct routing. Check logs for unexpected null values or error patterns. After deployment, enable monitoring so any invalid or outdated email address can be updated quickly.

Finally, document the lookup. Capture domain definitions, row meanings, and ownership details in your integration repository or wiki. Clear documentation ensures long-term maintainability and reduces the risk of misconfiguration as teams evolve.