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.
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:
DBMS_LOB.SUBSTR (lob_loc, amount, offset)
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).
CREATE OR REPLACE VIEW temp_view
column1, -- datatype numeric
column2, -- datatype varchar()
DBMS_LOB.SUBSTR(column3, 2000,1) as column3, -- datatype CLOB
column4 -- datatype numeric
Note: In this example I am reading first 2000 charactres.
Well, there you go.
Thanks to Tom Chen and Rakesh Dudhia for this info.