Resolve CLOB field Issues in Oracle comparison

Clob and BLOB fields in Oracle have been developed to meet the requirements of storing oversized content, but in some simple uses it often brings trouble. A pointer is stored in the CLOB and cannot be taken directly to the actual value. and the Text field in SQL Server is very convenient, you can directly take with the need to match the string, like what equals ah less than ah kind ah. But to Oracle on the trouble to die, to open up a cache, the content of a paragraph read out after the conversion, is writing a where condition is so complex? After a multi-party search for information, finally found a convenient and simple method: The use of Dbms_lob package method (rest assured, built-in) InStr and substr. The specific description is as follows:

InStr function and substr function

The InStr function is used to find the nth string that matches a pattern from a large object, starting at the specified position.
The InStr function syntax for finding strings in internal large objects is as follows:

1:  dbms_lob.instr (

2:  Lob_loc in Clob character set Any_cs,

3:  pattern in varchar2 character set Lob_loc%charset,

4:  offset in integer:=1,

5:  nth in integer: = 1)

6:  

7:  

Lob_loc as a locator for internal large objects
Pattern is the mode to match
Offset is the starting position to search for matching files
Nth is the nth match to be made

substr function
The SUBSTR function is used to extract the specified digit bytes from a large object. This function is commonly used when we need only a portion of a large object.
The SUBSTR function syntax for manipulating internal large objects is as follows:

The meanings of each of these parameters are as follows:
Lob_loc is a large object locator to be manipulated by the SUBSTR function
Amount is the number of bytes to extract from the large object
Offset is where you start extracting data from a large object.

If the data is successfully extracted from a large object, the function returns a raw value. If there is a situation, NULL is returned:
1 trailing null for any input parameters
2 Amount < 1
3 Amount > 32767
4 Offset < 1
5 Offset > Lobmaxsize

1:  dbms_lob.instr (

2:  

3:  

4:  offset in integer: = 1;

5:  nth in integer: = 1)

6:  

7:  Dbms_lob.substr (

8:    

9:    amount in integer: = 32767,

:    offset in integer: = 1)

11:  

:  Dbms_lob.substr (

13:    

+:    amount in integer: = 32767,

:    offset in integer: = 1)

16:  

As the following example, it is easy to read:

1:  

2:     Source_lob clob;

3:     pattern VARCHAR2 (6): = ' Oracle ';

4:     start_location integer: = 1;

5:     nth_occurrence integer: = 1;

6:     position integer;

7:     buffer varchar2 (100);

8:  begin

9:     Select Clob_locator to Source_lob from mylobs where lob_index = 4;

Ten:     Position: = Dbms_lob.instr (Source_lob, pattern, start_location, nth_occurrence);

One:     dbms_output.put_line (' The first occurrence starts at position: ' | | position);

:  

     nth_occurrence: = 2;

:     select Clob_locator into Source_lob from mylobs where lob_index = 4;

     Position: = Dbms_lob.instr (Source_lob, pattern, start_location, nth_occurrence);

:     Dbms_output.put_line (' The first occurrence starts at position: ' | | position);

:  

:     select Clob_locator into Source_lob from mylobs where lob_index = 5;

:     Buffer: = Dbms_lob.substr (Source_lob, 9, start_location);

:     dbms_output.put_line (' The substring extracted is: ' | | buffer);

21st:  

The output is:
The first occurrence starts at Position:8
The first occurrence starts at position:24
The substring extracted is:oracle 9i

Haha, easy to fix clob field matching filter, not a lot of stored procedures and functions, good luck to everyone!

Resolve CLOB field Issues in Oracle comparison