Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Unanswered: SQL Server reverse Function Equivalent in Oracle

    Hi

    Equivalent function for SQL Server REVERSE

    Thanx in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Server reverse Function Equivalent in Oracle

    Well, Oracle has a REVERSE function too. Is it not what you want?

    SQL> select reverse('abc') from dual;

    REV
    ---
    cba

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Re: SQL Server reverse Function Equivalent in Oracle

    Originally posted by andrewst
    Well, Oracle has a REVERSE function too. Is it not what you want?

    SQL> select reverse('abc') from dual;

    REV
    ---
    cba
    REVERSE is perfectly valid in a SQL Query, but when it is used in PL/SQL function doesn't seem to be working.

    plz. provide ur input.

    I am providing a sample PL/SQL Ref.

    CREATE OR REPLACE PROCEDURE TEMP
    AS
    BEGIN

    SELECT REVERSE('ABCD') FROM SYS.DUAL;
    END;
    /

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Server reverse Function Equivalent in Oracle

    You are quite right! And in fact, the REVERSE function isn't documented, probably for this reason. REVERSE is a reserved word in PL/SQL used in FOR loops, so perhaps that's why it isn't allowed.

    Quite by accident, I appear to have found a work-around. I was wondering whether you could write your own function called REVERSE. I didn't want to bother with implementing the algorithm, so I just did this:
    PHP Code:
    SQLcreate function reverse(string in varchar2) return varchar2 is begin return stringend;
      
    2  /

    Function 
    created
    Note that all it does is return the input - it does not reverse it. But now:
    PHP Code:
    SQL> declare
      
    2    x varchar2(3);
      
    3  begin
      4    select reverse
    ('abc'into x from dual;
      
    5    dbms_output.put_line(x);
      
    6  end;
      
    7  /
    cba

    PL
    /SQL procedure successfully completed
    See that? Now there is no PLS-00201 error, and the standard REVERSE function is called, not my dummy function!

    Of course, this looks more like a bug than a feature, and as I said before REVERSE is not a documented Oracle function - so whether you should rely on this in your code is questionable (may even have implications with Oracle support!)
    Last edited by andrewst; 03-01-04 at 13:52.

  5. #5
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Thumbs up Re: SQL Server reverse Function Equivalent in Oracle

    Originally posted by andrewst
    You are quite right! And in fact, the REVERSE function isn't documented, probably for this reason. REVERSE is a reserved word in PL/SQL used in FOR loops, so perhaps that's why it isn't allowed.

    Quite by accident, I appear to have found a work-around. I was wondering whether you could write your own function called REVERSE. I didn't want to bother with implementing the algorithm, so I just did this:
    PHP Code:
    SQLcreate function reverese(string in varchar2) return varchar2 is begin return stringend;
      
    2  /

    Function 
    created
    Note that all it does is return the input - it does not reverse it. But now:
    PHP Code:
    SQL> declare
      
    2    x varchar2(3);
      
    3  begin
      4    select reverse
    ('abc'into x from dual;
      
    5    dbms_output.put_line(x);
      
    6  end;
      
    7  /
    cba

    PL
    /SQL procedure successfully completed
    See that? Now there is no PLS-00201 error, and the standard REVERSE function is called, not my dummy function!

    Of course, this looks more like a bug than a feature, and as I said before REVERSE is not a documented Oracle function - so whether you should rely on this in your code is questionable (may even have implications with Oracle support!)

    Thanx andrew. now It works Thanx

  6. #6
    Join Date
    Aug 2001
    Posts
    66
    Tony,

    Are you sure about that? SELECT REVERSE () appears to work fine in PL/SQL without any need for functions named 'REVERESE' or whatever.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by padderz
    Tony,

    Are you sure about that? SELECT REVERSE () appears to work fine in PL/SQL without any need for functions named 'REVERESE' or whatever.
    That "REVERESE" was a typo! Should have been "REVERSE" of course. Well, in 8.1.7.3.0. that's what happens for me:

    PHP Code:
    SQLdrop function reverse;

    Function 
    dropped.

    SQL> declare
      
    2    x varchar2(3);
      
    3  begin
      4    select reverse
    ('abc'into x from dual;
      
    5    dbms_output.put_line(x);
      
    6  end;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-06550line 4column 10:
    PLS-00201identifier 'REVERSE' must be declared
    ORA
    -06550line 4column 3:
    PL/SQLSQL Statement ignored


    SQL
    create function reverse(string in varchar2) return varchar2 is begin return stringend;
      
    2  /

    Function 
    created.

    SQL> declare
      
    2    x varchar2(3);
      
    3  begin
      4    select reverse
    ('abc'into x from dual;
      
    5    dbms_output.put_line(x);
      
    6  end;
      
    7  /
    cba

    PL
    /SQL procedure successfully completed
    Maybe in later versions this has been fixed?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    By the way, I just checked the 10G SQL Reference, and the REVERSE function isn't documented there either.

  9. #9
    Join Date
    Aug 2001
    Posts
    66
    OK it looks like it fails in 8.1.7 and works in 9i and later.

    The fact that this works in later versions is probably a result of the integrated SQL parser in post 9i versions of Oracle.

    Suspect your example simply permits the PL/SQL to resolve the SQL and compile successfully but when the SQL is actually executed at run-time the alternate version of REVERSE is called.

    Apologies - with the typo and everything I was starting to wonder what was going on!
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  10. #10
    Join Date
    Apr 2009
    Posts
    1

    More on Oracle's REVERSE()


    ____I hit this snag myself in 10gR2 XE. The first line didn't work due to the parser rejecting it. The "Select ... From Dual;" did the trick. This was part of a test procedure that used records as input (pName_rec) and output (pEnam_rec). The record had two Varchar2(20) name fields - fErste (first) and fLetzte (last). My thanks to "andrewst" for the hint.

    Note - No user "REVERSE(...)" was defined for my version.

    Code:
    --    pEnam_rec.fErste := Reverse(pName_rec.fErste);
        Select Reverse(pName_rec.fErste)
            Into pEnam_rec.fErste
            From Dual;

  11. #11
    Join Date
    Dec 2003
    Posts
    1,074
    So SUBSTR with negative values in the third argument don't suffice as an alternative? I've never had the need for a REVERSE, so I can't think of an example to even try it out.

    What's a practical example for using REVERSE?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by chuck_forbes
    What's a practical example for using REVERSE?
    "Find the LAST instance of a space (or other character) in a string".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pootle flump
    "Find the LAST instance of a space (or other character) in a string".
    Which can easily be done using INSTR searching backwards

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by shammat
    Which can easily be done using INSTR searching backwards
    ... like this:
    Code:
    SQL> select instr('abacab','a',-1) from dual;
    
    INSTR('ABACAB','A',-1)
    ----------------------
                         5

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - my bad. I was scouting for SPAM and thought I was in a different forum.

    Ho hum.
    Continue as you were
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •