5 Replies Latest reply: Sep 12, 2007 3:07 PM by JustinCave RSS

    split clob data

      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

      <b>Test Procedure:</b>
      <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

      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
          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
            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
              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...

              • 4. Re: split clob data
                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

                <b>Test Procedure:</b>
                <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

                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:
                • 5. Re: split clob data
                  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