Forum Stats

  • 3,874,885 Users
  • 2,266,782 Discussions
  • 7,911,993 Comments

Discussions

Support DECODE in PL/SQL

User_1871
User_1871 Member Posts: 247 Red Ribbon

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.

User_1871Mike Kutz
3 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    Decode is conditional logic. It is very easy to do conditional logic in a procedural language like plsql.

    The typical way to implement decode would be a IF THEN ELSE or a CASE expression. But you are correct that proper null handling is a bit verbose.

    return case when num1=num2 then 'SAME' when num1 is null and num2 is null then 'SAME' else 'DIFFERENT' end; end;
    


    User_1871
  • Mike Kutz
    Mike Kutz Member Posts: 6,254 Gold Crown

    @Sven W.

    IMO - this idea is to simplify code and is a good idea because

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

    is a lot simpler than

    return case when num1=num2 then 'SAME' when num1 is null and num2 is null then 'SAME' else 'DIFFERENT' end; end;
    

    Additionally, it brings some SQL functionality into PL/SQL (keeps logic identical)