hi
how can i use REVERSE function in my udf?
i have got an error
PLS-00201: identifier 'REVERSE' must be declared
my function is as below
CREATE OR REPLACE FUNCTION GEN_15_DIGIT_NO(BRANCH NUMBER,TYP NUMBER,AC VARCHAR2)
RETURN VARCHAR2 IS
VVAR VARCHAR2(20):=' ';
BEGIN
BEGIN
if REGEXP_INSTR (AC, '[-|/|*|@|#|$|%|]')>0 then
for x in
1..length(AC)
loop
if ascii(SUBSTR(REPLACE(AC,' '),x,1)) between 48 and 57 then
vvar:=vvar||SUBSTR(REPLACE(AC,' '),x,1);
end if;
end loop;
vvar:=lpad(replace(vvar,' '),8,'0');
elsIF
LENGTH(REPLACE(ac,' '))<15 THEN
vvar:=reverse(SUBSTR(reverse(ac), 1, 8));
elsif
LENGTH(REPLACE(ac,' '))>=15 THEN
vvar:=substr(ac,-8);
ELSE
VVAR:=ac;
end if;
VVAR:=LPAD(SUBSTR(BRANCH,-3),3,'0')||LPAD(TYP,3,'0')||'1'||LPAD(VVAR,8,'0');
RETURN VVAR;
EXCEPTION
when NO_DATA_FOUND THEN
VVAR:='SOMETHING_WRONG';
RETURN VVAR;
END;
END GEN_15_DIGIT_NO;
i am working on
BANNER
----------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
please help