The strange case of the REVERSE function

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Goal: reverse the order of the characters in a string:

select reverse('hello') s from dual;

exec dbms_output.put_line(reverse('hello'));

S    
-----
olleh


Error starting at line : 11 in command -
BEGIN dbms_output.put_line(reverse('hello')); END;
Error report -
ORA-06550: line 1, column 28:
PLS-00201: identifier 'REVERSE' must be declared
ORA-06550: line 1, column 7:
 

The REVERSE function exists only in the context of a SQL statement, therefore in order to execute it in a PL/SQL block you need to put it inside a SELECT ... FROM.

Interestingly, REVERSE is nowhere to be found in the SQL Reference but the function  seems to be available since at least Oracle 11g.

Another possibility is to write your own PL/SQL REVERSE function (but with a different name).

See message translations for PLS-00201 and search additional resources.