Solved

Oracle LOB Fields

Posted on 2003-11-17
3
1,045 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

734 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