OIC File Handling – Removing .pgp and Preserving .csv (Dynamic File Name Logic) | use of ast-index-within-string() function

📌 Problem Statement

In Oracle Integration Cloud (OIC), while working with Stage File, we often receive source files like:

a.b.c.csv.pgp 🔐 (encrypted file)

a.b.c.csv 📄 (plain file)

🎯 Requirement

If file = a.b.c.csv.pgp → Target should be a.b.c.csv

If file = a.b.c.csv → Keep as a.b.c.csv

⚠️ Challenge

Handling multiple dots (.) in file names:

Simple logic may break

Functions like last-index-of() (or OIC equivalent) may not always behave as expected in mapper

✅ Approach 1: Nested substring-before/after (Working & Stable).

💻 Code Snippet

XML

fn:concat(

  fn:substring-before($FileName, '.'),

  '.',

  fn:substring-before(fn:substring-after($FileName, '.'), '.'),

  '.',

  fn:substring-before(fn:substring-after(fn:substring-after($FileName, '.'), '.'), '.'),

  '.csv'

)

✔️ Behavior

Extracts each part between dots

Reconstructs filename ending with .csv

👍 Advantages

Works consistently in OIC mapper

No dependency on special functions

👎 Disadvantages

Not dynamic (hardcoded for 3 segments like a.b.c)

Breaks if file structure changes (e.g., a.b.c.d.csv.pgp)

⚡ Approach 2: Using oraext:last-index-within-string (Dynamic but Risky)

💻 Code Snippet

XML

substring(

  $filename,

  1,

  oraext:last-index-within-string($filename, '.')

)

✔️ Behavior

Dynamically trims extension

Works for any filename length

👍 Advantages

Fully dynamic

Handles multiple dots easily

👎 Disadvantages

❌ In OIC mapper, sometimes does not evaluate correctly

Can fail depending on context (Stage File / namespaces)

Less predictable compared to substring chaining

🧠 Recommended Hybrid Logic

👉 Best practical approach:

Use substring logic when:

File pattern is fixed (like a.b.c.csv.pgp)

Use last-index logic when:

File pattern is dynamic

Tested properly in your integration

🚀 Pro Tip

If your only goal is to remove .pgp, a simpler and safer approach:

XML

fn:replace($filename, '.pgp', '')

✔️ Works for both cases:

a.b.c.csv.pgp → a.b.c.csv

a.b.c.csv → unchanged

🧾 Conclusion

OIC file handling becomes tricky with multiple dots

substring-before → stable but rigid

last-index-within-string → flexible but unreliable in some cases

🎯 Best solution depends on your file pattern stability