This discussion is archived
5 Replies Latest reply: Sep 12, 2007 1:07 PM by Justin Cave RSS

split clob data

vpolasa Newbie
Currently Being Moderated
I have a column 'Test_desc' of CLOB datatype.
The data in the 'Test_desc' is of html format.

for example:


<table cellpadding="0" cellspacing="0"hspace="0" border="0"><tr><td valign="top"><b>Goal:</b> </td><td>Verify this variable can control the rate for the console
</td></tr></table>

<b>Test Procedure:</b>
<ol>
<li>Bring up the boot loader command-line interface

<li>Use set command to verify the default value is 9600

<li>Use set BAUD command to change the value

<li>Use set command to verify the change

<li>Verify the rate by running XMODEM

<li>For LOTR, repeat the above steps on slave switches
</ol>


I want to seperate the data starting with 'Test Procedure' and store it in another column. What would be the best way to split the CLOB data. The code should be able to split any size of data.(as the CLOB datatype can hold upto 4gb of data).

Thank you and regards,
  • 1. Re: split clob data
    damorgan Oracle ACE Director
    Currently Being Moderated
    Look at the DBMS_LOB package. The package contains SUBSTring and INSTRing functions for LOBs.

    Demos in Morgan's Library at www.psoug.org and docs at http://tahiti.oracle.com.
  • 2. Re: split clob data
    vpolasa Newbie
    Currently Being Moderated
    I tried with dbms_lob.substr and dbms_lob.instr
    but it also worked with substr and instr (without using the dbms_lob package)

    so what is the differece between the substr and dbms_lob package?
  • 3. Re: split clob data
    Justin Cave Oracle ACE
    Currently Being Moderated
    It depends on the Oracle version, there were a number where the various SQL functions like SUBSTR and INSTR weren't overloaded to accommodate LOBs. There are also a number of cases where the order of parameters to the SQL version and DBMS_LOB version of the functions are different, which is a really fun way to introduce bugs...

    Justin
  • 4. Re: split clob data
    vpolasa Newbie
    Currently Being Moderated
    thank you damorgan and justin


    I've this column 'Description' with clob datatype. I tried to split the 'Description' into clob_pro and goal columns.


    The data in 'Description' is in the html format, as i mentioned above.. sample data is:


    <table cellpadding="0" cellspacing="0"hspace="0" border="0"><tr><td valign="top"><b>Goal:</b> </td><td>Verify that the variables can be configured from Web console
    </td></tr></table>

    <b>Test Procedure:</b>
    <ol>
    <li>Rerun 4.1.1 -- 4.1.9, configure the variable from web console instead of system console

    <li>Verify from both system console and web console
    </ol>



    The code is working fine with the below syntax. But when I tried to execute the code with dbms_lob package ie with dbms_lob.substr and dbms_lob.instr, the code isn't working.


    Would anyone let me know, what is the reason behind and is this the right way to split the CLOB data..


    update testcases
    set clob_pro = substr(description, instr(description, '<b>Test Procedure:</b>') , length (description)) ,
    goal = substr (description, 1, instr (description, '<b>Test Procedure:</b>', 1, 1)-1) ;


    Message was edited by:
    user575124
  • 5. Re: split clob data
    Justin Cave Oracle ACE
    Currently Being Moderated
    As I mentioned, you need to be careful about the order of parameters since there are differences between the DBMS_LOB version of functions and the SQL version of the functions. For example

    DBMS_LOB.SUBSTR takes parameters
    1) string
    2) amount
    3) offset

    SUBSTR tales parameters
    1) string
    2) offset
    3) amount

    Justin