Comparing BLOB fields in two different databases

[Glowball]

Hi all, I have a BLOB field storing the binary for a PDF file. The database is being copied to another space and I'm not sure the BLOB field is being copied correctly. How would I compare the two fields to make sure they're exactly the same?

I've used LENGTH() but I think that's really only supposed to work for strings. When I try the example shown on

http://www.dba-oracle.com/t_finding_lob_blob_clob_length.htm

I get the error ORA-00904: invalid identifier

What should I use to find the length of a BLOB field? Thanks!

 

[lecorr]

Try dbms_lob.getglentgh to get the length of the lob. If you use TOAD, it contains a tool to look at the content of lobs (I just used it in a project).

Depending on the oracle version, you won't get the same functions and capacities for lob.

The above function should work. Try connected as SYS or SYSTEM to check. If you connect as a normal user, ask your dba for a grant execute on dbms_lob (and a synonym? you have to check this, I am using a very provileged user).

Hope it helps.

Christian

 

[Glowball]

I tried dbms_lob functions and they're either not installed or I don't have the right permissions. I'm not allowed to use TOAD and my user is pretty limited. I think I'll pass this along to the DBA. Thanks!

 

[Thargy]

I believe that DBMS_LOB has a compare function built-in - check the documentation.

Regards

T