Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_CRYPTO hash a BLOB in a union over dblink
DBMS_CRYPTO hash a BLOB in a union over dblink [message #573376] Thu, 27 December 2012 09:37 Go to next message
ocdeveloper
Messages: 9
Registered: November 2010
Junior Member
Hi guys,

With this table in local and remote database via a DBLink

CREATE TABLE PICTURES (
  ID NUMBER(12),
  PICNAME VARCHAR2(20),
  PICTURE BLOB
);


How come I can get the hash of the picture column fine like this:

SELECT dbms_crypto.hash@dbLink(PICTURE, 3)from SCHEMA1.PICTURES@dbLink;


but I can't do it in a union with the local database?
SELECT dbms_crypto.hash@dbLink(PICTURE, 3)from SCHEMA1.PICTURES@dbLink
UNION
SELECT dbms_crypto.hash(PICTURE, 3)FROM SCHEMA1.PICTURES;


gives "ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server"

Thanks,
OC
Re: DBMS_CRYPTO hash a BLOB in a union over dblink [message #573378 is a reply to message #573376] Thu, 27 December 2012 10:31 Go to previous message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either a use local hash or create your remote function that will return the hash value. There are many ways depending on the actual need.

If your first query really works, you can try using the "driving_site" hint:
SELECT /*+ qb_name(remot) driving_site(@remot PICTURES) materialize */
       dbms_crypto.hash@dbLink(PICTURE, 3)from SCHEMA1.PICTURES@dbLink 
UNION
SELECT dbms_crypto.hash(PICTURE, 3)FROM SCHEMA1.PICTURES;

Regards
Michel
Previous Topic: Problem with cursor
Next Topic: Refcursor question
Goto Forum:
  


Current Time: Fri Dec 19 07:59:17 CST 2014

Total time taken to generate the page: 0.16183 seconds