How to insert same random value into two columns within one SQL Statement (Oracle)?

Question

My example doesn't work:

INSERT INTO test_table 
 (column_1, column_2)
 VALUES (DBMS_CRYPTO.RANDOMBYTES(16), column_1); 

I'm not interested in PL/SQL solutions.

Answer 1

One option is to use scalar subquery caching:

INSERT INTO test_table (column_1, column_2)
SELECT random, random FROM
    (SELECT (SELECT dbms_crypto.randombytes(16) FROM dual) random FROM dual);

Or using PL/SQL:

DECLARE
    random RAW(16) := dbms_crypto.randombytes(16);
BEGIN
    INSERT INTO test_table (column_1, column_2) VALUES (random, random);
END;

Answer 2

Use a subquery:

INSERT INTO test_table(column_1, column_2)
  SELECT val, val
  FROM (SELECT DBMS_CRYPTO.RANDOMBYTES(16) as val FROM dual) x;

Or, this can be written as:

INSERT INTO test_table(column_1, column_2)
  WITH x AS (SELECT DBMS_CRYPTO.RANDOMBYTES(16) as val FROM dual)
  SELECT val, val
  FROM x;