|
|
| Re: How to compare 2 values of Long Datatype [message #38926 is a reply to message #38922] |
Tue, 28 May 2002 06:03   |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
*******************************************************
hi!.
Make use of dbms_lob.compare.
The COMPARE() function allows comparison between two entire LOBs or parts of the LOBs. COMPARE() returns a zero if the data exactly matches over the specified range. Otherwise, it returns a non-zero value.
*******************************************************
SQL> CREATE TABLE lob_store OF lob_type;
Table created.
/* load some data */
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 loop_count INTEGER;
3 BEGIN
4 loop_count := 1;
5 WHILE loop_count <= 10 LOOP
6 INSERT INTO lob_store (lob_id) VALUES (loop_count);
7 loop_count := loop_count + 1;
8 END LOOP;
9 UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('0123456789'),
10 document = 'abcdefgh' where lob_id=2;
11 UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('7777777'),
12 document = 'ijklmn' where lob_id=3;
13 UPDATE lob_store SET video_clip=empty_blob(),
14 document = empty_clob() where lob_id=4;
15* END;
16 /
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PROCEDURE tst_compare IS
2 dblob BLOB;
3 sblob BLOB;
4 dclob CLOB;
5 sclob CLOB;
6 amt NUMBER;
7 dpos NUMBER;
8 spos NUMBER;
9 ret INTEGER;
10 BEGIN
11 dbms_output.put_line('--------------- COMPARE Begin ---------------');
12 dpos := 3; spos := 1;
13 amt := 5;
14
15 /* test COMPARE for BLOBs */
16 SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2;
17 SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4;
18 ret := -1;
19 ret := dbms_lob.compare(dblob, sblob, amt, dpos, spos);
20 dbms_output.put_line('Return value for BLOB: ' || ret);
21
22 /* testing the validity of COMPARE for CLOBs */
23 SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
24 SELECT document INTO sclob FROM lob_store WHERE lob_id = 4;
25 ret := -1;
26 ret := dbms_lob.compare(dclob, sclob, amt, dpos, spos);
27 dbms_output.put_line('Return value for CLOB: ' || ret);
28
29 dbms_output.put_line('--------------- COMPARE End ---------------');
30 dbms_output.put_line(' ');
31 END tst_compare;
32
33 /
Procedure created.
SQL> exec tst_compare;
--------------- COMPARE Begin ---------------
Return value for BLOB: 1
Return value for CLOB: 1
--------------- COMPARE End ---------------
PL/SQL procedure successfully completed.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
