Oracle DBMS_CRYPTO Encryption

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:
    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:


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.

Author: dan


  1. That was exactly my problem. In my case I changed it to an in out no copy from an out nocopy since I had initialized it in the calling routine. It was not smart enough with an an out nocopy to see it was already set up.

Leave a Reply

Your email address will not be published. Required fields are marked *