SQL & PL/SQL

I am trying to write code equivalent to below in Oracle 23

The keypoints are aggregation of array ( window functions ) and collation according to datatype.

Code below runs in Postgres 17 and DuckDB 1.1.x

with v01 as ( select x.partkey, x.val1::integer as val1, x.val2 
from ( values ('partkey1', 7, 'g'), 
('partkey1', 10, 'j'), 
('partkey2', 7, 'g'), 
('partkey2', 2, 'b')) 
as x(partkey, val1, val2) ) , 
v02 as ( select v.* 
, array_agg(v.val1) over (win_v02) as integer_array 
, string_agg(v.val1::varchar, ',') over (win_v02) as varchar_array -- for collation comparison & visualisation 
from v01 v 
window win_v02 as (partition by v.partkey) ), 
v03 as ( select v.* 
, rank() over (order by v.integer_array, v.val2) as integer_array_rank -- orders by val1::integer 
, rank() over (order by v.varchar_array, v.val2) as varchar_array_rank -- orders by val1::varchar 
from v02 v ) 
select v.val1, v.val2, v.integer_array, v.integer_array_rank, v.varchar_array, v.varchar_array_rank from v03 v 
order by v.integer_array_rank ;