OraFAQ Forum: SQL & PL/SQL » DBMS_CRYPTO hash a BLOB in a union over dblink

Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_CRYPTO hash a BLOB in a union over dblink

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

DBMS_CRYPTO hash a BLOB in a union over dblink [message #573376]

Thu, 27 December 2012 09:37

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

Report message to a moderator

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

Michel Cadot
Messages: 54687
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

Report message to a moderator

Previous Topic:	Problem with cursor
Next Topic:	Refcursor question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 02:51:41 CDT 2013

Total time taken to generate the page: 0.07374 seconds