CLOB to VARCHAR2 conversion - extracting first <n> characters?
I have a materialized view populated from an external database with a number of CLOB columns. The CLOBs are maintained by a third party application. The reporting tool (OBIEE) doesn't support CLOBs, and I've agreed with my customer that I will extract only the first 4k characters from the CLOB columns for display. The table may contain upwards of a million rows (hard to tell just how big it will be as the application use is being ramped up).
I had created the following view:
CREATE OR REPLACE VIEW clob_free AS SELECT id, DBMS_LOB.SUBSTR (clob_column, 4000, 1) as vc_column FROM clob_table;