Recently, I need to do some performance test against oracle 10g, the case is that I need to prepare as many records as possible which has blob columns, and the blob column must at least contain 10kbs of data.
How do I automatically generate the test data? Say, 10,000 records?
If you do not care that the content of the LOB data is the same, you can use the following (creating one random 10000 bytes lob value):
declare
v_clob clob;
v_blob blob;
v_dest_offset integer := 1;
v_src_offset integer := 1;
v_warn integer;
v_ctx integer := dbms_lob.default_lang_ctx;
begin
for idx in 1..5
loop
v_clob := v_clob || dbms_random.string('x', 2000);
end loop;
dbms_lob.createtemporary( v_blob, false );
dbms_lob.converttoblob(v_blob,
v_clob,
dbms_lob.lobmaxsize,
v_dest_offset,
v_src_offset,
dbms_lob.default_csid,
v_ctx,
v_warn);
insert into blob_test (id, data)
select rownum, v_blob from dual
connect by level <= 10000;
end;
/
See here an example session:
SQL> create table blob_test (id number primary key, data blob);
Table created.
SQL> declare
2 v_clob clob;
3 v_blob blob;
4 v_dest_offset integer := 1;
5 v_src_offset integer := 1;
6 v_warn integer;
7 v_ctx integer := dbms_lob.default_lang_ctx;
8 begin
9 for idx in 1..5
10 loop
11 v_clob := v_clob || dbms_random.string('x', 2000);
12 end loop;
13 dbms_lob.createtemporary( v_blob, false );
14 dbms_lob.converttoblob(v_blob,
15 v_clob,
16 dbms_lob.lobmaxsize,
17 v_dest_offset,
18 v_src_offset,
19 dbms_lob.default_csid,
20 v_ctx,
21 v_warn);
22
23 insert into blob_test (id, data)
24 select rownum, v_blob
25 from dual
26 connect by level <= 10000;
27
28 end;
29 /
PL/SQL procedure successfully completed.
SQL> select count(*) , max(length(data)) from blob_test;
COUNT(*) MAX(LENGTH(DATA))
---------- -----------------
10000 10000
If you want to have different data you can generate and insert v_lob in a loop instead.
