0

How to find particular sub part of the clob data column ( actually characters are stored) using oracle sql command?.

There is a column in oracle DB table, of type clob, which stores characters, many number of lines.

I need to extract part of the clob data, not entire data

For example column data is as follows

com.mywebsite.info  information message:  java.io.Filenotfound exception
trace

I need to extract only java.io.filenotfound string. I tried using Regular expressions in oracle sql, but unable to find options to get substring.

Raj
  • 3
  • 3
  • What is the algorithm that you want to implement? Having only one example of the data makes the question unanswerable. Do all the strings you want come between "message: " and " exception trace" for example? – Justin Cave Nov 04 '15 at 17:54
  • I am trying to extract only root cause of the problem that is filenotfound instead of entire stack trace, which will be multiple lines.the sub string i am looking for will come between message and java trace string – Raj Nov 04 '15 at 17:58
  • State in words the algorithm that finds the "root cause". We don't know your data. We don't know what assumptions we can make about the format of that data. We don't even have access to more than the one example string you posted. I could speculate that "com.mywebsite.info information message: " is a hard-coded value and you want everything after that up to the first space. I have no idea whether that speculation is correct. – Justin Cave Nov 04 '15 at 18:07
  • [This post](http://stackoverflow.com/questions/17649011/search-for-a-particular-string-in-oracle-clob-column) might help – tbone Nov 04 '15 at 20:38
  • So you tried `regexp_substr()`? What exactly did you try? –  Nov 04 '15 at 20:48

1 Answers1

0

you can use DBMS_LOB.SUBSTR and DBMS_LOB.INSTR functions in your query you can select only subpart of CLOB as a text and then do what you need with the text as with simple text field

for example:

--create test table from user_tables DDL text
create table test as
select table_name, dbms_metadata.get_ddl('TABLE', table_name) as clob_field
from user_tables

--sample select
 select 
 dbms_lob.substr(clob_field, 17) as sample_first_17_chars
,dbms_lob.substr(clob_field, 6, instr(clob_field,'NUMBER')) as sample_number_text      
  from test
where dbms_lob.instr(clob_field, 'NUMBER') > 0

result

SAMPLE_FIRST_17_CHARS, SAMPLE_NUMBER_TEXT
CREATE TABLE ",NUMBER
CREATE TABLE ",NUMBER
CREATE TABLE ",NUMBER
CREATE TABLE ",NUMBER
are
  • 2,535
  • 2
  • 22
  • 27
  • It worked. if a particular sub part i am searching is there in multiple lines in clob type, do i need to add another argument to specify to search for a substring in each line of the clob (characters) that is dbms_lob.instr(fil, pattern, 100, 3);, here sql checks only for 3rd occurence, but i need to get multiple occurences of a substring and display the string next to it. do i need to write for loop or need to go for procedural function in sql. For example below error com.mywebsite.info information message: java.io.Filenotfound exception – Raj Nov 05 '15 at 20:16
  • above file not found exception appears multiple times in trace logs, i need to pull the string next to the file not found exception, multiple times – Raj Nov 05 '15 at 20:17
  • it looks as a complex logic, you need see this post how to do the CLOB reading http://stackoverflow.com/questions/11647041/reading-clob-line-by-line-with-pl-sql – are Nov 05 '15 at 20:50