• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1051
  • Last Modified:

Oracle LOB Fields

How to compare two clob fields stored in oracle table
1 Solution
Helena Markováprogrammer-analystCommented:
You have to use DBMS_LOB Package.

Subprograms in the DBMS_LOB Package
The more commonly used procedures and functions contained in the DBMS_LOB package can be broadly categorized as follows:  a)  The routines that can modify BLOB, CLOB, and NCLOB values are:
     APPEND()   - append the contents of the source LOB to the destination LOB
     COPY()     - copy all or part of the source LOB to the destination LOB
     ERASE()    - erase all or part of a LOB
     LOADFROMFILE() - load BFILE data into an internal LOB
     TRIM()     - trim the LOB value to the specified shorter length
     WRITE()    - write data to the LOB from a specified offset

b)  The routines that read or examine LOB values are:
      COMPARE()   - comapre two entire or part of two lobs
      GETLENGTH() - get the length of the LOB value
      INSTR()     - return the matching position of the nth occurrence of the pattern in the LOB
      READ()      - read data from the LOB starting at the specified offset
      SUBSTR()    - return part of the LOB value starting at the specified offset

c)  The read-only routines specific to BFILEs are:
      FILECLOSE()   - close the file
      FILECLOSEALL()- close all previously opened files
      FILEEXISTS()  - check if the file exists on the server
      FILEGETNAME() - get the directory alias and file name
      FILEISOPEN()  - check if the file was opened using the input BFILE locators
      FILEOPEN()    - open a file

Here is a procedure published in Note:61737.1 on Metalink:

   dblob BLOB;
   sblob BLOB;
   dclob CLOB;
   sclob CLOB;
   amt   NUMBER;
   dpos  NUMBER;
   spos  NUMBER;
   ret   INTEGER;
   dbms_output.put_line('--------------- COMPARE Begin ---------------');
   dpos := 3;
   spos := 1;
   amt := 5;
    /* test COMPARE for BLOBs */
   SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2;
   SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4;
   ret := -1;
   ret := dbms_lob.compare(dblob, sblob, amt, dpos, spos);
   dbms_output.put_line('Return value for BLOB: ' || ret);
    /* testing the validity of COMPARE for CLOBs */
   SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
   SELECT document INTO sclob FROM lob_store WHERE lob_id = 4;
   ret := -1;
   ret := dbms_lob.compare(dclob, sclob, amt, dpos, spos);
   dbms_output.put_line('Return value for CLOB: ' || ret);
    dbms_output.put_line('--------------- COMPARE End   ---------------');
   dbms_output.put_line(' ');
 END tst_compare;

I hope this will help you.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now