Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Support DECODE in PL/SQL

User_1871Jul 4 2022 — edited Jul 4 2022

It would be helpful if the DECODE function could be supported in PL/SQL.
Currently, if we use DECODE directly in PL/SQL, we get an error:

return decode(num1, num2, 'SAME', 'DIFFERENT');

ORA-06553: PLS-204: function or pseudo-column 'DECODE' may be used inside a SQL statement only

DECODE is useful because when comparing two different columns, it considers two NULL values to be equivalent.

I'm aware that there are workarounds, such as SELECT INTO:

  select decode(num1, num2, 'SAME', 'DIFFERENT')
  into  v_decode
  from  dual;

But that's not as clean as using DECODE directly.
Could DECODE be added to PL/SQL?

Feel free to let me know if I've misunderstood something.

Comments
Post Details
Added on Jul 4 2022
2 comments
169 views