Extract TEXT from a CLOB field

Question

I have a CLOB field in my Oracle Database that store TEXT data in the following format:

__99__RU_LOCKED=N;;__99__RU_SUSPENDED=Y;;__17__USER_TYPE=A;;__17__USER_TYPE_610=A;;__17__GUIFLAG=0;;__17__DEFAULT_LANG_610=E;;__17__OUTPUT_DEVICE_46=LOCL;;__17__PRINT_IMMED=G;;__17__DELETE_AFTER_PRINT=D;;__17__CATT=*BLANK;;__17__CATT_46=*;;__17__DEC_FORMAT=*BLANK;;__17__DEC_FORMAT_46=X;;__17__DATE_FORMAT=2;;__17__PARAMETERS=OM_OBJM_NO_DISPLAYX;;__17__MEAS_EASLPFL=0;;__17__USER_GROUP=S1BR22;;__17__VALID_FROM=20080222;;__17__VALID_UNTIL=99991231;;__17__ACCOUNT=37004968;;

I'm using TOAD and while I am creating the query I can read the CLOB field with the following:

--- To read the CLOB field.
select DBMS_LOB.substr(ADD_INFO_MASTER) from USER

This select return me the CLOB field HUMAN READABLE.

My question is: Is there any way to extract the one single value like ACCOUNT value from the line above? Keep in mind that this CLOB field can variate and the __17__ACCOUNT= will not be in the same place every time. I need a way to extract to locate the ;;__17__ACCOUNT= (this will be a pattern) and extract the the value 37004968.

It is possible to achieve this while performing a query in TOAD?

Answer 1

If you want to deal with CLOB values larger than 4000 symbols length (Oracle 11g) or 32K length (Oracle 12c) then you must use DBMS_LOB package.

This package contains instr() and substr() functions which operates on LOBs.

In your case query looks like that:

with prm as (
  select '__17__ACCOUNT' as fld_start from dual
)
select 
  dbms_lob.substr(
    text,         
    -- length of substring             
    (  
      -- position of delimiter found after start of desired field 
      dbms_lob.instr(text, ';;', dbms_lob.instr(text, prm.fld_start)) 

      -

      -- position of the field description plus it's length
      ( dbms_lob.instr(text, prm.fld_start) + length(fld_start) + 1 )
    ),

    -- start position of substring
    dbms_lob.instr(text,prm.fld_start) + length(fld_start) + 1
  )  
from 
  text_table,
  prm

Query above uses this setup:

create table text_table(text clob);

insert into text_table(text) values (
  '__99__RU_LOCKED=N;;__99__RU_SUSPENDED=Y;;__17__USER_TYPE=A;;__17__USER_TYPE_610=A;;__17__GUIFLAG=0;;__17__DEFAULT_LANG_610=E;;__17__OUTPUT_DEVICE_46=LOCL;;__17__PRINT_IMMED=G;;__17__DELETE_AFTER_PRINT=D;;__17__CATT=*BLANK;;__17__CATT_46=*;;__17__DEC_FORMAT=*BLANK;;__17__DEC_FORMAT_46=X;;__17__DATE_FORMAT=2;;__17__PARAMETERS=OM_OBJM_NO_DISPLAYX;;__17__MEAS_EASLPFL=0;;__17__USER_GROUP=S1BR22;;__17__VALID_FROM=20080222;;__17__VALID_UNTIL=99991231;;__17__ACCOUNT=37004968;;'
);  

For everyday use with development tools it may be useful to define a function which returns value of field with desired name and use it instead of writing complicated expressions each time.
E.g. :

create or replace function get_field_from_text(
  pi_text       in clob,
  pi_field_name in varchar2
) return varchar2 deterministic parallel_enable
is
  v_start_pos   binary_integer;
  v_field_start varchar2(4000);
  v_field_value varchar2(32767);
begin

  if( (pi_text is null) or (pi_field_name is null) ) then
    return null;
  end if;

  v_field_start := pi_field_name || '=';
  v_start_pos := dbms_lob.instr(pi_text, v_field_start);

  if(v_start_pos is null) then
    return null;
  end if;

  v_start_pos := v_start_pos + length(v_field_start);

  v_field_value := dbms_lob.substr(
                     pi_text,
                     (dbms_lob.instr(pi_text, ';;', v_start_pos) - v_start_pos),
                     v_start_pos
                   );

  return v_field_value;
end;

Usage:

select get_field_from_text(text,'__17__OUTPUT_DEVICE_46') from text_table