This content has been marked as final. Show 5 replies
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.
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?
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
SUBSTR tales parameters