CODEWITHSUNDEEP
plsqloracle10g
Pratyush Priyam Kuanr
Pratyush Priyam Kuanr

Reputation: 33

getting Warning: Function created with compilation errors

I am getting error while executing the following function.I have been banging my head for quite sometime now.I am new to oracle so I am not able to correct it.Can someone please help?

create or replace function rever(x int)
return number
is
y varchar2(30);
c varchar2(30);
v int;
begin
y:=to_char(x);
c:=reverse(y);
v:=to_number(c);
return v;
end rever;
/

the following is showing as error message 

LINE/COL ERROR
-------- ----------------------------------------------------------------- 
9/1      PL/SQL: Statement ignored 
9/4      PLS-00201: identifier 'REVERSE' must be declared

Upvotes: 1

Views: 165

Answers (3)

APC
APC

Reputation: 146229

There is no PL/SQL reverse() function in Oracle. So this does not work:

declare
  v varchar2(20);
begin
  v:= reverse('krow not does ti');
  dbms_output.put_line(v);
end;  
/

It throws the PLS-00201 error you got.

However, there is an undocumented SQL function which we can use in PL/SQL, but only by invoking the SQL engine:

declare
  v varchar2(20);
begin
  select reverse('skrow ti') into v from dual;
  dbms_output.put_line(v);
end;  
/

Of course, because reverse() is undocumented we're not supposed to use it, at least in production code. Not sure why it's undocumented. I think Oracle uses it for reverse indexes, so maybe there's some limit on reversible string size.

Here is a db<>fiddle demo.

The performace is a bit worse

I think that is the cost of moving from the PL/SQL engine to the SQL engine and back again. So it comes down to use case. If we're writing a function which will only be used in pure PL/SQL then I think your approach is the better one. But if we're writing a function to be used in SQL then I would consider using the Oracle built-in instead, even though it's not supported.

Although to be honest I can't remember the last time I used a reverse() function - Oracle's or hand-rolled - in real life (as opposed to answering questions in forums or similar Code Golf questions :) ).

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142715

Just for amusement, without PL/SQL, using a little bit of regular expressions with hierarchical query:

SQL> with test (col) as
  2    (select 'Littlefoot' from dual)
  3  select listagg(one, '') within group (order by lvl desc) reversed
  4  from (select level lvl, regexp_substr(col, '.', 1, level) one
  5        from test
  6        connect by level <= length(col)
  7       );

REVERSED
--------------------------------------------------------------------------
toofelttiL

SQL>

Or, rewritten as a function:

SQL> create or replace function f_reverse (par_col in varchar2)
  2    return varchar2
  3  is
  4    retval varchar2(1000);
  5  begin
  6    select listagg(one, '') within group (order by lvl desc)
  7      into retval
  8      from (select level lvl, regexp_substr(par_col, '.', 1, level) one
  9            from dual
 10            connect by level <= length(par_col)
 11           );
 12    return retval;
 13  end;
 14  /

Function created.

SQL> select empno, f_reverse(empno) rev_empno,
  2         ename, f_reverse(ename) rev_ename
  3  from emp
  4  where rownum <= 3;

     EMPNO REV_EMPNO  ENAME      REV_ENAME
---------- ---------- ---------- ----------
      7369 9637       SMITH      HTIMS
      7499 9947       ALLEN      NELLA
      7521 1257       WARD       DRAW

SQL>

Upvotes: 1

hotfix
hotfix

Reputation: 3396

Oracle does not provide a function in plsql to do a string reverse.

     create or replace function rever(x int)
     return number
     is
       y varchar2(30);
       c varchar2(30);
       v int;
     begin
       y:=to_char(x);

       -- Loop other each char in a string from the last to the first element  
       for i in reverse 1.. length(y)
       loop 
         c:= c|| substr(y,i,1); 
       end loop; 
       v:=to_number(c);
       return v;
     end rever;
     /

Test:

     begin
       dbms_output.put_line(rever(1));
       dbms_output.put_line(rever(12));
       dbms_output.put_line(rever(21));
       dbms_output.put_line(rever(123));
     end;
     /

Result:

dbms_output
    1
    21
    12
    321

db<>fiddle here

Upvotes: 2

Related Questions