Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table it220213a (
  txt1 varchar2(4)
, val1 int
);
insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

insert into it220213a values
('test', 123);
1 rows affected

with t1(cnt, i, j) as (
select 1 
     , 1 
     , 1 
 from dual
union all
select cnt + 1
     , case
         when mod(i, 9) = 0 then 1
         else i + 1
       end
     , case
         when mod(i, 9) = 0 then j + 1
         else j
       end
  from t1
 where cnt < (select count(*) from it220213a)
), t2 as (
select row_number() over(order by txt1) as rn
     , txt1
     , val1
  from it220213a
)
select to_char(sysdate, 'YYMMDD') || lpad(t1.j, 4, '0') as serno
     , t1.i as xrn
     , t2.txt1
     , t2.val1
  from t1
     , t2
 where t1.cnt = t2.rn;


SERNO
XRN
TXT1
VAL1


2306210001
1
test
123

2306210001
2
test
123

2306210001
3
test
123

2306210001
4
test
123

2306210001
5
test
123

2306210001
6
test
123

2306210001
7
test
123

2306210001
8
test
123

2306210001
9
test
123

2306210002
1
test
123

2306210002
2
test
123

2306210002
3
test
123

2306210002
4
test
123

SERNO	XRN	TXT1	VAL1
2306210001	1	test	123
2306210001	2	test	123
2306210001	3	test	123
2306210001	4	test	123
2306210001	5	test	123
2306210001	6	test	123
2306210001	7	test	123
2306210001	8	test	123
2306210001	9	test	123
2306210002	1	test	123
2306210002	2	test	123
2306210002	3	test	123
2306210002	4	test	123