I need to debug some flow and to do that, I need to generate at least some 100k rows, using say:

insert into a.b(DATA, ID, ORDER_ID) 
SELECT UTL_RAW.CAST_TO_RAW('too short!'), 
       raw_to_guid(sys_guid()),
       level 
FROM dual CONNECT BY level <= 5;

but that data are too small! I don't care what data is it, it can be all ones. I can generate random bytes base64 encoded, but I have no idea how to pass it into oracle, but it would be even better to fix somehow the code above and generate it on db side. Can someone advice?

CodePudding user response：

You can define a function to generate a BLOB from random data; and since Oracle 12c that can be an on-the-fly function defined in a with clause (read more):

insert /*  WITH_PLSQL */ into b (data, id, order_id)
with
  function generate_blob return blob as
    l_blob blob;
  begin
    dbms_lob.createtemporary(l_blob, false);
    for i in 1..50 loop
      dbms_lob.append(
        l_blob,
        to_blob(utl_raw.cast_to_raw(dbms_random.string('p', 2000)))
      );
    end loop;
    return l_blob;
  end generate_blob;
select generate_blob,
       raw_to_guid(sys_guid()),
       level
from dual connect by level <= 5;

which inserts five rows with a random 100-byte BLOB value; this query shows the length and the first 20 bytes to show they are random:

select order_id, id, lengthb(data), rawtohex(dbms_lob.substr(data, 20, 1)) from b

fiddle

You can make the BLOBs much bigger if you want, just by change the loop's upper bound from 50 to something larger.