Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

REVERSE function

hemuMar 5 2016 — edited Mar 6 2016

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

This post has been answered by Paulzip on Mar 5 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 3 2016
Added on Mar 5 2016
31 comments
9,787 views