I spent a few days battling the oracle DBMS_CRYPTO package. This package allows the encryption of data types, including clobs and blobs (in oracle 10g). The battles included:

1. My user not having visibility to the package in the stored procedure call. This was solved by a simple call to the DBA.
2. In my system, I was trying to encrypt a CLOB. According to the oracle docs the function signature is:

   dbms_crypto.encrypt(
   
   dst IN OUT NOCOPY BLOB,
   
   src IN     CLOB CHARACTER SET ANY_CS,
   
   typ IN     PLS_INTEGER, key IN RAW,
   
   iv  IN     RAW DEFAULT NULL);

   To which I wrote:

   outputBlob := dbms_crypto.encrypt
   (outputBlob, myClob, myRawKey, myCryptoFormat, null)

   To which oracle told me:

   Error(13,15): PLS-00306: wrong number
    or types of arguments in call to 'ENCRYPT'".

   Now what is mischievous about this error is that it’s not actually the arguments of the function that are wrong, it’s the fact that I’m trying to set the output of the call back into outputBlob.

   dbms_crypto.encrypt(outputBlob, myClob,
   myRawKey, myCryptoType, null)

   Is the proper call. I wish that the compiler was better as telling me that. I’m assuming that because a parameter is an IN OUT I shouldn’t be trying to set the output value the way I was.

3. When I tried to first invoke the stored procedure, I got:

   ORA-01405:	fetched column value is NULL

   Which basically said that the output BLOB needs to be initialized. I initialized the value to EMPTY_BLOB.

4. When I then to invoke the stored procedure, I got the following error:

   ORA-22275:invalid LOB locator specified"

   Apparently EMPTY_BLOB is no good. I google’d around for a while and found:

   DBMS_LOB.CREATETEMPORARY(encrypt,true);

And finally it worked and returned me an encrypted BLOB! Props to the power of the internet for providing the chain of solutions that got my stored procedure up and running.