Convert CLOB to VARCHAR, nvarchar, etc

I have users that need to query a table that has a CLOB column. They have to use BRIO to query. However, BRIO can NOT query CLOB datatypes.

What can I do to facilitate this?

I was thinking of creating a view. Or, I can duplicating the table into one that converts into one with a varchar datatype (since they just query for reporting).

In either case, I would have to know how to convert a CLOB into another datatype (preferrably, varchar).

One: could someone tell if I can convert it that datatype in creating the view?

Two: how would I go about converting the column (in any way possible)?

any takers?

What kind of data is stored in the CLOB columns ?

A lot of text. Basically, a description of something.

Will that be smaller than 4K ?
If so, I guess you can write a function or something to extract it in varchar format...

Oracle says that is it is under 4K, then it is stored inline (actual in the table).

Are you then saying that if it exceeds 4k (in which case it is stored outside the table), then I will not be able to use a function that converts/extracts. If so, then would there be no other (FEASIBLE) way of converting/extracting the column into an alternative data type, other than using a function?

I am assuming that they used CLOB because they either exceed that limitation (that LONG has), or they have multiple CLOBS in they table.

I've only been briefed of the task ahead. I haven't been given access yet to this db/table to see that actual table structure looks like. I am doing my priliminary research.

Appreaciate any other feedbacks, Thanks.

Well...looks like I solved my own problem here.
Turns out there's a DBMS package call DBMS_LOB. See below for description

The Function to translate CLOB datatype into varchar() is DBMS_LOB
The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs. DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs.

Here is the syntax you might want to use for your requirement:

Syntax:
DBMS_LOB.SUBSTR (lob_loc, amount, offset)

Parameter Description
lob_loc: Locator for the LOB to be read i.e CLOB column name.
amount: Number of bytes (for BLOBs) or characters (for CLOBs) to be read.
offset: Offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1).

Example:

CREATE OR REPLACE VIEW temp_view
AS
SELECT
column1, -- datatype numeric
column2, -- datatype varchar()
DBMS_LOB.SUBSTR(column3, 2000,1) as column3, -- datatype CLOB
column4 -- datatype numeric
FROM temp_table;

Note: In this example I am reading first 2000 charactres.


Well, there you go.
Thanks to Tom Chen and Rakesh Dudhia for this info.