DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Reverse() function in oracle

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Lightbulb

    hi,
    just go thru the example below.

    y the function REVERSE doesn't work in a PL/SQL block?

    -----------------------------------------------------------------------
    SQL> select reverse('jegan') from dual;

    REVER
    -----
    nagej

    -----------------------------------------------------------------------
    declare
    a varchar2(100);
    begin
    select reverse('jegan') into a from dual;
    dbms_output.put_line(a);
    end;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-06550: line 4, column 8:
    PLS-00201: identifier 'REVERSE' must be declared
    ORA-06550: line 4, column 1:
    PL/SQL: SQL Statement ignored
    Cheers!
    OraKid.

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    The function REVERSE is part of Oracle supplied PL/SQL package UTL_RAW.
    It can be used in SQL statements but not in PL/SQL blocks or procedures.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Raminder
    The function REVERSE is part of Oracle supplied PL/SQL package UTL_RAW.
    It can be used in SQL statements but not in PL/SQL blocks or procedures.
    Correction: UTL_RAW.REVERSE has its purity level set to "WNDS, RNDS, WNPS, RNPS", so it can be used in SQL statements without problems. The only inconveniance is that it operates on RAW datatypes, so you must use UTL_RAW.CAT_TO_RAW and UTL_RAW.CAST_TO_VARCHAR2 to properly handle strings to be reversed.

    Example:

    SQL> select utl_raw.cast_to_varchar2(UTL_RAW.REVERSE(utl_raw.cast_to_raw('Reverse this')))
    2 from dual;

    UTL_RAW.CAST_TO_VARCHAR2( UTL_RAW.REVERSE( UTL_RAW.CAST_TO_RAW( 'REVERSETHIS')))
    --------------------------------------------------------------------------------
    siht esreveR
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width