Thanks Mike ,
We need to implement the logic by using pl/sql code ?

My requirement is to get the default values of all tables and need to do some validations based on the values.

Hi Mike ,

Thanks for your support. I had implemented one function which returns the varchar for the given long value.

But still getting some problem while using that function in sql query :

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(
    P_LONG LONG)
  RETURN VARCHAR2
IS
 -- l_long LONG := P_LONG;
  l_vc2 VARCHAR2(32767);
BEGIN
  
--  l_vc2 :=   CASE
--      WHEN l_long IS NULL
--      THEN 'NO_VALUE'
--      ELSE SUBSTR(l_long,1,32767)
--    END;
IF  P_LONG  IS NULL THEN  
l_vc2 := 'NO_VALUE';
ELSE  
l_vc2  :=   SUBSTR(P_LONG,1,32767);
END  IF;  
  RETURN l_vc2;
END;

then i am using that function in the following query :

 
  SELECT LONG_TO_CHAR(TEXT)   CH  FROM user_views where rownum = 1;

ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"
*Cause:    
*Action:
Error at Line: 10 Column: 21