Solved

Oracle LOB Fields

Posted on 2003-11-17
3
1,046 Views
Last Modified: 2012-06-27
How to compare two clob fields stored in oracle table
0
Comment
Question by:vishalgoyal123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

696 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