I have table with subnetworks like
cidr | ip
And I want to select subnetworks by list of belonging ips. In postgres I can do it in this way:
select ips.ip, net.uid, net.cidr from TBL_SID_SUBNETWORK net, (select unnest(ARRAY[1,2,3]) ip) ips where cast (((2^net.cidr) - 1) as bigint)<<(32 - net.cidr) & ips.ip = net.ipaddress
In postgres I can pass array and use it as a table.
select ips.ip from (select unnest(ARRAY[1,2,3]) ip) ips |ip| 1 2 3
Is it possible to do something like that in Oracle? In one query? I don't want to create, populate and drop additional table, because I use DB indirectly, and transaction is managed by application configuration.
I know about Oracle's
TABLE(collection) function which does merely the same what I want. But I can't pass collection into this query, 'cause I should declare and populate collection before, and in this way it's the same as creating temp table.