Convert CLOB to VARCHAR, nvarchar, etc
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Convert CLOB to VARCHAR, nvarchar, etc

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    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?

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    What kind of data is stored in the CLOB columns ?
    svk

  3. #3
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    A lot of text. Basically, a description of something.

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    Will that be smaller than 4K ?
    If so, I guess you can write a function or something to extract it in varchar format...
    svk

  5. #5
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    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.

  6. #6
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    Problem resolved

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width