8

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.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
dkiselev
  • 890
  • 8
  • 20
  • 2
    Is the problem with `TABLE` just that you don't want to define a collection? If so, there are pre-defined, documented collections you can use. For example: `select column_value ip from table(sys.odcinumberlist(1,2,3));` – Jon Heller Feb 04 '15 at 20:41

1 Answers1

9

Oracle has pre-defined, documented collections you can use. For example:

select column_value ip from table(sys.odcinumberlist(1,2,3));

There's no one place that contains a list of all documented collections. This page of the Database Data Cartridge Developer's Guide lists some of the more popular ones, such as ODCIVarchar2List, ODCINumberList, and ODCIDateList. Also, this answer by Lukas Eder uses a query to find system collections, although not all of them are documented.

It would be nice if Oracle just a few standard collections for everyone to use and gave them nice names. It's safe to use sys.odcinumberlist but it looks like an ugly hack.

Community
  • 1
  • 1
Jon Heller
  • 34,999
  • 6
  • 74
  • 132