SQL Language (MOSC)

MOSC Banner

CLOB to VARCHAR2 conversion - extracting first <n> characters?

edited Apr 16, 2014 3:28AM in SQL Language (MOSC) 4 commentsAnswered ✓

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center