Test data example

View All Public Scripts Login to Run Script

Script Name Test data example
Visibility Unlisted - anyone with the share link can access
Description Creates 3 test case data sets
Contributor kerry.j.norman@gmail.com
Created Wednesday June 24, 2020

Statement 1

create table reg3_test_case_1 
( reg3_test_nk        varchar2(30) 
, col_txt1            varchar2(100) 
, col_txt2            varchar2(100) 
, col_clob            clob 
, col_num             number 
, col_date            date 
, constraint reg3_test_case_1_u01 unique (reg3_test_nk) enable 
)  
parallel  
nologging

Table created.

Statement 2

insert into reg3_test_case_1  
( reg3_test_nk 
, col_txt1 
, col_txt2 
, col_clob 
, col_num 
, col_date 
) 
select  
  'NK'|| to_char(level,'0000000') as reg3_test_nk 
, 'Test 1 - Insert record '|| to_char(level,'0000000') 
, DBMS_RANDOM.STRING('A', DBMS_RANDOM.value( 1,100 )) 
, random_clob(32767,500000,100) 
, round(DBMS_RANDOM.value( 100,10000 ),0) 
, sysdate 
from dual  
connect by level <= 123

ORA-00904: "RANDOM_CLOB": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 3
```
commit
```
```
Statement processed.
```

Statement 4

create or replace function RANDOM_CLOB 
( min_length number 
, max_length number 
, muliplier number default 1 
) return clob 
is  
  l_clob clob := empty_clob(); 
begin   
  for i in 1 .. trunc(dbms_random.value(round(min_length/muliplier,0), round(max_length/muliplier,0)),0) 
  loop 
    l_clob := l_clob || dbms_random.string('A', muliplier); 
  end loop; 
  return l_clob; 
end;

Function created.

Statement 5

insert into reg3_test_case_1  
( reg3_test_nk 
, col_txt1 
, col_txt2 
, col_clob 
, col_num 
, col_date 
) 
select  
  'NK'|| to_char(level,'0000000') as reg3_test_nk 
, 'Test 1 - Insert record '|| to_char(level,'0000000') 
, DBMS_RANDOM.STRING('A', DBMS_RANDOM.value( 1,100 )) 
, random_clob(32767,500000,100) 
, round(DBMS_RANDOM.value( 100,10000 ),0) 
, sysdate 
from dual  
connect by level <= 123

ORA-01536: space quota exceeded for tablespace 'LIVESQL_USERS' ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-01536

Statement 6
```
commit
```
```
Statement processed.
```

Statement 7

insert into reg3_test_case_1  
( reg3_test_nk 
, col_txt1 
, col_txt2 
, col_clob 
, col_num 
, col_date 
) 
select  
  'NK'|| to_char(level,'0000000') as reg3_test_nk 
, 'Test 1 - Insert record '|| to_char(level,'0000000') 
, DBMS_RANDOM.STRING('A', DBMS_RANDOM.value( 1,100 )) 
, random_clob(10,100,1) 
, round(DBMS_RANDOM.value( 100,10000 ),0) 
, sysdate 
from dual  
connect by level <= 123

123 row(s) inserted.

Statement 8
```
commit
```
```
Statement processed.
```

Statement 9

create table reg3_test_case_2 parallel nologging 
as  
select *  
from reg3_test_case_1 
where reg3_test_nk in 
  ( select reg3_test_nk from (select reg3_test_nk from reg3_test_case_1 order by dbms_random.value) where rownum <= 95)

Table created.

Statement 10

exec ach.utl.run('drop table reg3_test_case_3')

ORA-06550: line 1, column 7:
PLS-00201: identifier 'ACH.UTL' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 11

create table reg3_test_case_3 parallel nologging 
as select * 
from reg3_test_case_2

Table created.

Statement 12

create table reg3_test_case_3 parallel nologging 
as select * 
from reg3_test_case_2

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 13

merge into reg3_test_case_3 t 
using  
( select  
    reg3_test_nk 
  , 'Update ' || to_char(rownum,'00') as COL_TXT1 
  , DBMS_RANDOM.STRING('P', DBMS_RANDOM.value( 1,100 )) as COL_TXT2 
  , random_clob(32767,100000,1000) as COL_CLOB 
  , rownum as COL_NUM 
  , sysdate as COL_DATE 
  from 
  ( select * 
    from reg3_test_case_3 t 
    order by dbms_random.value 
  ) where rownum <= 25 
) s 
on (s.reg3_test_nk = t.reg3_test_nk) 
when matched then 
update 
set t.COL_TXT1 = s.COL_TXT1 
  , t.COL_TXT2 = s.COL_TXT2 
  , t.COL_CLOB = s.COL_CLOB 
  , t.COL_NUM = s.COL_NUM 
  , t.COL_DATE = s.COL_DATE

ORA-01536: space quota exceeded for tablespace 'LIVESQL_USERS' ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-01536

Statement 14
```
commit
```
```
Statement processed.
```

Statement 15

merge into reg3_test_case_3 t 
using  
( select  
    reg3_test_nk 
  , 'Update ' || to_char(rownum,'00') as COL_TXT1 
  , DBMS_RANDOM.STRING('P', DBMS_RANDOM.value( 1,100 )) as COL_TXT2 
  , random_clob(10,100,1) as COL_CLOB 
  , rownum as COL_NUM 
  , sysdate as COL_DATE 
  from 
  ( select * 
    from reg3_test_case_3 t 
    order by dbms_random.value 
  ) where rownum <= 25 
) s 
on (s.reg3_test_nk = t.reg3_test_nk) 
when matched then 
update 
set t.COL_TXT1 = s.COL_TXT1 
  , t.COL_TXT2 = s.COL_TXT2 
  , t.COL_CLOB = s.COL_CLOB 
  , t.COL_NUM = s.COL_NUM 
  , t.COL_DATE = s.COL_DATE

Statement processed.

Statement 16
```
commit
```
```
Statement processed.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database