Solved

Oracle LOB Fields

Posted on 2003-11-17
3
1,040 Views
Last Modified: 2012-06-27
How to compare two clob fields stored in oracle table
0
Comment
Question by:vishalgoyal123
3 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 250 total points
ID: 9769471
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:

CREATE OR REPLACE PROCEDURE tst_compare IS
   dblob BLOB;
   sblob BLOB;
   dclob CLOB;
   sclob CLOB;
   amt   NUMBER;
   dpos  NUMBER;
   spos  NUMBER;
   ret   INTEGER;
 BEGIN
   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.

Henka
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now