Long to Varchar2

CREATE OR REPLACE FUNCTION .f_text_all_views(p_owner varchar2, p_view varchar2)
RETURN varchar AS 
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--
begin
  sql_cur := 'select text from all_views where owner = '||chr(39)||p_owner||chr(39)||' and view_name = '||chr(39)||p_view||chr(39);
  execute immediate sql_cur into text_c1;

  text_c1 := substr(text_c1, 1, 4000);
  RETURN TEXT_C1;  
END;

CREATE OR REPLACE FORCE VIEW ._ALL_VIEWS
(OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TYPE_TEXT_LENGTH, 
 TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, 
 SUPERVIEW_NAME)
AS 
select OWNER
     , VIEW_NAME
     , TEXT_LENGTH
     , .f_text_all_views(owner, view_name) text
     , TYPE_TEXT_LENGTH
     , TYPE_TEXT
     , OID_TEXT_LENGTH
     , OID_TEXT
     , VIEW_TYPE_OWNER
     , VIEW_TYPE
     , SUPERVIEW_NAME
  from all_views
 where owner = user;


CREATE OR REPLACE FORCE VIEW ._ALL_VIEWS_DBLINK
(VIEW_NAME, TEXT)
AS 
select view_name, substr(text,instr(text,'@'),50) text from _all_views where text like '%@%';