?
Solved

Oracle LOB Fields

Posted on 2003-11-17
3
Medium Priority
?
1,047 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 1000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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