How to Query a Clob Column in Oracle

How to query a CLOB column in Oracle

When getting the substring of a CLOB column and using a query tool that has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size. For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000.

Running the DBMS_LOB.substr command you can also specify the amount of characters you want to return and the offset from which. So using DBMS_LOB.substr(column, 3000) might restrict it to a small enough amount for the buffer.

See oracle documentation for more info on the substr command

    DBMS_LOB.SUBSTR (
       lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
       amount      IN    INTEGER := 32767,
       offset      IN    INTEGER := 1)
      RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

Querying oracle clob column

Yes, it's not allowed (this restriction does not affect CLOBs comparison in PL/SQL)
to use comparison operators like =, !=, <> and so on in SQL statements, when trying
to compare two CLOB columns or CLOB column and a character literal, like you do. To be
able to do such comparison in SQL statements, dbms_lob.compare() function can be used.

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, 'value') = 0

In the above query, the 'value' literal will be implicitly converted to the CLOB data type.
To avoid implicit conversion, the 'value' literal can be explicitly converted to the CLOB
data type using TO_CLOB() function and then pass in to the compare() function:

  select * 
    from aTable 
   where dbms_lob.compare(aClobColumn, to_clob('value')) = 0

Oracle SQL : Select Query : search if clob Contains a string with pattern matching

Generic string functions for parsing JSON inputs are dangerous - you will get false positives, for example, when something that looks like a JSON object is in fact embedded in a string value. (Illustrated by ID = 101 in my example below.)

The ideal scenario is that you are using Oracle 19 or higher; in that case you can use a simple call to json_exists as illustrated below. In the sample table I create, the first JSON string does not contain a member named apple. In the second row, the string does contain a member apple but the value is null. The first query I show (looking for all JSON with an apple member) will include this row in the output. The last query is what you need: it adds a filter so that a JSON string must include at least one apple member with non-null value (regardless of whether it also includes other members named apple, possibly with null value).

create table sample_data
( id      number primary key
, colname clob   check (colname is json)
);

insert into sample_data
    values (101, '{name:"Chen", age:83, values:["{apple:6}", "street"]}');

insert into sample_data
    values (102, '{data: {fruits: [{orange:33}, {apple:null}, {plum:44}]}}');
    
insert into sample_data
    values (103, '[{po:3, "prods":[{"apple":4}, {"banana":null}]},
                   {po:4, "prods":null}]');

Note that I intentionally mixed together quoted and unquoted member names, to verify that the queries below work correctly in all cases. (Remember also that member names in JSON are case sensitive, even in Oracle!)

select id
from   sample_data
where  json_exists(colname, '$..apple')
;


 ID
---
102
103

This is the query you need. Notice the .. in the path (meaning - find an object member named apple anywhere in the JSON) and the filter at the end.

select id
from   sample_data
where  json_exists(colname, '$..apple?(@ != null)')
;

 ID
---
103

Querying Oracle Clob datatype

Hope this examples illustrates clearly what i am trying to explain.

SET SQLBL ON;
SET DEFINE OFF;

CREATE TABLE CLOB_TEST
(
clob_in CLOB
);

INSERT
INTO CLOB_TEST VALUES
  (
    'I am working as a DBA and senior database resource in L&T Infotech in Pune India'
  );


SELECT DBMS_LOB.SUBSTR(CLOB_IN,3000) ot FROM CLOB_TEST;

-----------------------------OUTPUT------------------------------------------

OT
I am working as a DBA and senior database resource in L&T Infotech in Pune India

-----------------------------OUTPUT------------------------------------------

check if clob contains string oracle

You can use DBMS_LOB.INSTR( clob_value, pattern [, offset [, occurrence]] ):

SELECT *
FROM   your_table
WHERE  DBMS_LOB.INSTR( clob_column, 'string to match' ) > 0;

or

SELECT *
FROM   your_table
WHERE  clob_column LIKE '%string to match%';

Search for a particular string in Oracle clob column

Use dbms_lob.instr and dbms_lob.substr, just like regular InStr and SubstStr functions.

Look at simple example:

SQL> create table t_clob(
  2    id number,
  3    cl clob
  4  );

Tabela zosta│a utworzona.

SQL> insert into t_clob values ( 1, ' xxxx abcd xyz qwerty 354657 [] ' );

1 wiersz zosta│ utworzony.

SQL> declare
  2    i number;
  3  begin
  4    for i in 1..400 loop
  5        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';
  6    end loop;
  7    update t_clob set cl = cl || ' CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text ';
  8    for i in 1..400 loop
  9        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';
 10    end loop;
 11  end;
 12  /

Procedura PL/SQL zosta│a zako˝czona pomyťlnie.

SQL> commit;

Zatwierdzanie zosta│o uko˝czone.
SQL> select length( cl ) from t_clob;

LENGTH(CL)
----------
     25717

SQL> select dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) from t_clob;

DBMS_LOB.INSTR(CL,'NEW.PRODUCT_NO=[')
-------------------------------------
                                12849

SQL> select dbms_lob.substr( cl, 5,dbms_lob.instr( cl, 'NEW.PRODUCT_NO=[' ) + length( 'NEW.PRODUCT_NO=[') ) new_product
  2  from t_clob;

NEW_PRODUCT
--------------------------------------------------------------------------------
T9856

Extract TEXT from a CLOB field

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

---

Related Topics