CODEWITHSUNDEEP
oracle-apex
Mohammad Taleshi
Mohammad Taleshi

Reputation: 41

Oracle Apex 5.1 use NVL in PL/SQL

I write a PL/SQL in my Apex app.

I want to set NVL with Dynamic Parameter. q is a string query, :P53_INS_FIELD is a Parameter. When I write NVL, it makes an error (Failed to parse SQL query).

My code is:

q:= q || ' and nvl(' || :P53_INS_FIELD || ' , ifl.id) = ifl.id';

This is wrong in Apex. The error is "Failed to parse SQL query"

What can I do?

Upvotes: 0

Views: 446

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

When you write like this, Oracle puts a value of the parameter into the string. So, if the parameter has value 123, your query in q variable will be:

... and nvl(123, ifl.id) = ifl.id

If the value will be NULL, the query will be:

... and nvl(, ifl.id) = ifl.id

I think it is what really happened. I suppose you need to write as follows:

q:= q || ' and nvl(:P53_INS_FIELD, ifl.id) = ifl.id';

Upvotes: 1

Related Questions