DBMS_CRYPTO.RANDOMBYTES returns null

Question

This statement returns null instead of 64 random bytes:

select DBMS_CRYPTO.RANDOMBYTES(64) from dual;

What is causing it to return null? Database is Oracle 10.1.0.2.0 running on Windows Server 2003 and I want to use the random bytes as salt for password hashing.

Edit: Here's the output from sqlplus:

Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production

SQL> select DBMS_CRYPTO.RANDOMBYTES(64) from dual;

DBMS_CRYPTO.RANDOMBYTES(64)
--------------------------------------------------------------------------------



SQL>

Edit2: When switching to Oracle 11 the query works. I have no idea why it doesn't work on Oracle 10 but have decided to not use that old database.

Can u try once from sys user and see if you are facing the same issue? — anudeepks, Apr 21 '15 at 09:25
No sorry, I don't have access to the sys user. I only have access to a normal user that has been granted the dbms_crypto package. — johanrex, Apr 21 '15 at 09:28
@anudeepks You should not use `SYS` for such tasks, sys is special. — Lalit Kumar B, Apr 21 '15 at 09:28
@lalit kumar Yes, I know , i just wanted to check if the same issue was repeating — anudeepks, Apr 21 '15 at 09:30
You appear to be getting output that is wrapped onto a second line, suggesting you're getting blanks rather than a null value. Just for fun, can you try `select RAWTOHEX(DBMS_CRYPTO.RANDOMBYTES(64)) from dual` and see if that displays any differently? Do you have another client you can try it with? — Alex Poole, Apr 21 '15 at 10:17
@AlexPoole Perhaps, an alternate way to check is to execute the statement in SQL Developer and check the query output. — Lalit Kumar B, Apr 21 '15 at 10:21
@LalitKumarB - yes, I'm wondering if that specific version of SQL\*Plus isn't displaying the RAW output properly, though I can't see any bugs related to that either, and I can't think of any settings that would affect it. Although, the output doesn't show which version of SQL\*Plus it actually is, so maybe it's a different version/platform to the DB and there's a compatibility issue. — Alex Poole, Apr 21 '15 at 10:26
@AlexPoole, The output of "select RAWTOHEX(DBMS_CRYPTO.RANDOMBYTES(64)) from dual" looks exactly the same as without RAWTOHEX. Both variants returns (null) as output in SQL Developer. — johanrex, Apr 21 '15 at 10:50
@LalitKumarB. Yes, the sqlplus version I use is for 11.2. So it's later than the database. When running the query from SQL Developer it gives the output (null). — johanrex, Apr 21 '15 at 10:52

Lalit Kumar B · Answer 1 · 2015-04-21T10:00:16.760

It works perfectly. I have tested it on Oracle 12c.

Update

I have checked My Oracle Support(Metalink), however, did not find anything related to this function returning NULL. Certainly, not a bug.

Anyway, looking deeper into the function and it's usage in the documentation, I came across SQLNET.CRYPTO_SEED

SQLNET.CRYPTO_SEED

Purpose

Use the parameter SQLNET.CRYPTO_SEED to specify the characters used when generating cryptographic keys. The more random the characters are, the stronger the keys are. The string should be 10-70 random characters. This optional parameter is required for when encryption or checksumming are turned on. Encryption is turned on if the SQLNET.ENCRYPTION_CLIENT parameter is specified for the client and the SQLNET.ENCRYPTION_SERVER parameter is specified for the database server; checksumming is turned on if the SQLNET.CRYPTO_CHECKSUM_CLIENT parameter is specified for the client and the SQLNET.CRYPTO_CHECKSUM_SERVER parameter is specified for the database server.

It might be possible that the parameter is not properly set in your environment. I don't have 10g version(obsolete though) to test.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing option

SQL> select DBMS_CRYPTO.RANDOMBYTES(64) from dual;

DBMS_CRYPTO.RANDOMBYTES(64)
--------------------------------------------------------------------------------
427AECD44D27CDF6CDC8F290D9F8079109BAB8AECD7E687E7ADF8E15825BBDF3D746FBC181059443
965B1425B224CC46E8CD07CC2D02B2E023238E8883520A19


SQL>

Yes, that is the result I was expecting. But instead I get null as a result. I don't know what is causing the null. — johanrex, Apr 21 '15 at 09:27
@user1073476 I guess Lalit wants to tell you that you need to upgrade either your DBMS, your OS or both. — Artjom B., Apr 21 '15 at 09:28
Well, `DBMS_CRYPTO.RANDOMBYTES` is available from 10g and up. — Lalit Kumar B, Apr 21 '15 at 09:29
IIRC, I used it in `10.2` in production. Not sure, if it was made backward compatible for `10.1` or not. — Lalit Kumar B, Apr 21 '15 at 09:32
Yes, it's possible that the sqlnet settings might be the cause. It's unfortunate that the documentation doesn't mention anything about returning null. I'll have to check with my dba since I don't have access to look up those settings myself. Thanks. — johanrex, Apr 21 '15 at 10:16

DBMS_CRYPTO.RANDOMBYTES returns null

1 Answers1