LONG_TO_CHAR

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(IN_TABLE_NAME  VARCHAR,
                                        IN_COLUMN      VARCHAR2,
                                        IN_COLUMN_NAME VARCHAR2,
                                        IN_TAB_NAME    VARCHAR2) RETURN VARCHAR AS
  TEXT_C1 VARCHAR2(32767);
  SQL_CUR VARCHAR2(2000);
BEGIN
  SQL_CUR := 'SELECT ' || IN_COLUMN || ' FROM
           ' || IN_TABLE_NAME || ' WHERE COLUMN_NAME = ' || CHR(39) ||
             IN_COLUMN_NAME || CHR(39) || ' AND TABLE_NAME=' || CHR(39) || IN_TAB_NAME ||
             CHR(39); --1 AND ROWNUM = 1';
  DBMS_OUTPUT.PUT_LINE(SQL_CUR);
  EXECUTE IMMEDIATE SQL_CUR
    INTO TEXT_C1;
  TEXT_C1 := SUBSTR(TEXT_C1, 1, 4000);
  RETURN TEXT_C1;
END;
/*
SELECT TABLE_NAME,
       COLUMN_NAME,
       LONG_TO_CHAR('USER_TAB_COLUMNS', 'DATA_DEFAULT', COLUMN_NAME, TABLE_NAME)
  FROM USER_TAB_COLUMNS
*/