extract a substring from clob in oracle
clob, database, oracle, substring
Solution
Tom Kyte say how get varchar2 from clob in SQL or PL/SQL code
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:367980988799
And when you have varchar2 you can use SUBSTR or REGEXP_SUBSTR function for extract the line.
http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions147.htm#i87066
http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm
If you want to use SQL code, you can create this request
select col1, col2, func1(dbms_lob.substr( t.col_clob, 4000, 1 )) from table1 t
And in PL/SQL function "func1" you can do what you want with input string using SUBSTR or any other functions
Problem
I have a clob with data ``` <?xml version='1.0' encoding='UTF-8'?><root available-locales="en_US" default-locale="en_US"><static-content language-id="en_US"><![CDATA[<script type="text/javascript"> function change_case() { alert("here..."); document.form1.type.value=document.form1.type.value.toLowerCase(); } </script> <form name=form1 method=post action=''''> <input type=text name=type value=''Enter USER ID'' onBlur="change_case();"> <input type=submit value=Submit> </form> </form>]]></static-content></root> ``` I want to extract the line with the onblur attribute, in this case: ``` <input type=text name=type value=''Enter USER ID'' onblur="change_case();"> ```