-- Oracle Function to convert LONG datatype to VARCHAR2(4000)
-- The general recommendation is to avoid LONG datatype in 
-- database design.  If objects have LONG datatype, the 
-- function below converts LONG datatype to VARCHAR2(4000)
-- in such objects.

CREATE OR REPLACE  TYPE typ_str IS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE
FUNCTION tab_function_vc4k(i_view  IN user_views.view_name%TYPE) 
RETURN typ_str  PIPELINED
 AS
  v_str   VARCHAR2(32000);
BEGIN
 FOR v_rec IN (SELECT text FROM all_views 
               WHERE view_name = UPPER(i_view)) LOOP
  v_str := v_rec.text;
  v_str := SUBSTR(v_str,1,4000);
  PIPE ROW(v_str);
 END LOOP;
 RETURN;
END  tab_function_vc4k;
/



-- Query Using Table Function Syntax

SELECT t.* FROM TABLE(tab_function_vc4k('wwv_flow_years')) t
;



-- Query The Function Using Dual

SELECT tab_function_vc4k('wwv_flow_years') view_text  FROM DUAL;