Invalid Numeric Value in Oracle APEX? The Hidden Comma Problem in URL Parameters Explained

A Real-World Lesson on Passing Page Items Safely in Oracle APEX

🔍 The Issue Faced in Production

Recently, I have faced a critical production error while navigating from a report page (Page 29) to an Edit page (Page 31).

Users were clicking the Edit icon, and suddenly this error appeared:

Invalid numeric value King for column EMPNO

ORA-06502: PL/SQL numeric or value error: character to number conversion error

It was working perfectly in most cases…

But failing for one specific employee:

KING,King

And that comma was the root cause.

🧠 What Was Happening?

On Page 29, we had a report with an Edit link passing values to Page 31 using Set Items.

Link Builder Configuration

• P31_ENAME → #ENAME#
• P31_EMPNO → #EMPNO#

Everything looked correct.

But when ENAME contained a comma:

KING,King

APEX interpreted the comma as a separator between multiple URL parameters.

So instead of:

P31_ENAME = KING,King

It interpreted as:

P31_ENAME = KING
P31_EMPNO = King

Now here is the problem:

• P31_EMPNO expects NUMBER
• It received King (VARCHAR2)

🔥 Boom → ORA-06502 numeric conversion error.

📸 Error Screenshot

APEX Error Code:

WWV_FLOW_DML.DML_INVALID_NUMBER_ERR

🔎 Root Cause Analysis (RCA)

In APEX URL structure, commas , are used as separators when passing multiple item values in a link.

When free-form text contains:

• Comma ,
• Special characters
• Rich text

APEX may split values incorrectly if not handled properly.

That’s exactly what happened.

✅ The Fix

To escape special characters properly while passing page items in Link Builder, we must enclose page item values with backslashes:

\P31_NAME\

Correct Link Setup

Instead of:

#ENAME#

Use:

\#ENAME#\

This ensures APEX treats the entire value as one string — even if it contains commas.

🛠 Why This Works

Backslashes instruct APEX:

“Treat everything inside as a single parameter value.”

So now:

KING,King

Remains:

P31_ENAME = KING,King

And does NOT spill into the next parameter.

📘 Lesson Learned

Whenever passing:

✔ Free-form text

✔ Rich text

✔ Values that may contain commas

✔ User-entered content

👉 Always escape the page item using backslashes.

💡 Best Practices Moving Forward

1. Never assume text will be “clean”
2. Escape page items when passing via URL
3. Test with edge-case data (commas, quotes, special characters)
4. Understand how APEX URL parameter parsing works
5. Use apex_util.prepare_url when possible

🎯 Final Thought

This issue was small in appearance…

But critical in production.

It reminded me that:

In Oracle APEX, URL parameter handling is powerful — but must be handled carefully.

One comma cost us production downtime.

One backslash fixed it.

🚀 If you’re working in Oracle APEX…

Test with:

• Commas
• Quotes
• Special characters
• Rich text values

Because production data will always surprise you.