How to reverse value of a clob data type in oracle sql?

Question

I want to reverse clob data type value in oracle in same way as we do for string data type fields with the help of 'reverse' function.Is there any inbuilt method for that.Google was not much help.Being a newbie in sql don't know whether it is even possible? I initially thought that 'reverse' function can be used for clob data type fields also but its not working, here is the example I have tried-

 drop table test;
 create table test
 (
  name varchar2(4000),
   description clob
  )

insert into test values ('aadinath','I have to reverse a clob data type value')

select reverse(name) from test;

output= htanidaa

select reverse(name), reverse(description) from test;

output= ORA-00932: inconsistent datatypes: expected CHAR got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Answer 1

You need to convert clob to varchar2 first. Then perform the reverse.

Reference 1:

The Function to translate CLOB datatype into varchar() is DBMS_LOB. The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs. DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs.

Syntax:

DBMS_LOB.SUBSTR (lob_loc, amount, offset)
dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

Parameter Description:

• lob_loc: Locator for the LOB to be read i.e CLOB column name.
• amount: Number of bytes (for BLOBs) or characters (for CLOBs) to be read.
• offset: Offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB.

Example:

 CREATE OR REPLACE VIEW temp_view
    AS
    SELECT
    column1, -- datatype numeric
    column2, -- datatype varchar()
    DBMS_LOB.SUBSTR(column3, 2000,1) as column3, -- datatype CLOB
    column4 -- datatype numeric
    FROM temp_table;

Note: In this example I am reading first 2000 charactres.