I have a column 'Test_desc' of CLOB datatype.
The data in the 'Test_desc' is of html format.
<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
<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,
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...
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
<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..
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:
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
3) offset SUBSTR tales parameters