How do I write an SQL function that takes an unknown amount of numbers as parameters?

Question

I am trying to write an Oracle SQL function that takes a list of numbers as arguments and return a pipelined list of table rows. My main problem is the quantity of numbers that can be passed is never certain with no real upper limit. I'll try and demonstrate what I mean:

Say I have a table defined as so:

create table items (
  id number primary key,
  class number,
  data string
);

I want to return all rows that match one of a list of class numbers that I submit. The function I'm shooting at looks a little like this:

function get_list_items_from_class([unknown number of parameters] 
  in items.class%type) 
  return tbl_list_item pipelined; -- I have types defined to handle the return values

I've been looking at ways to handle defining a function that can take an undefined amount of integers and so far the most promising search has taken me to this page which explains about using collections and records. I don't think a VARRAY is what I'm looking for as the size has to be predefined. As Associative Array may be what I'm looking for, but before I spend a lot of time trying things out, I want to make sure the tool is fit for the job. I'm pretty inexperienced with Oracle SQL right now and I'm working on a time sensitive project.

Any help that you could offer would be appreciated. I realise that there are simpler ways to achieve what I'm trying to do in this example (simply multiple calls to a function that takes one parameter is one) but this example is simplified. Other parts of the project I'm working on require me to seek a solution using this multiple parameter method.

EDIT: That being said, I would welcome other design suggestions if I'm way off base with what I'm trying to attempt. It would be a learning experience if nothing else.

Many thanks in advance for your time.

EDIT: I will be accessing the database from proprietary client software written in Java.

cf http://stackoverflow.com/questions/10217922/how-to-use-array-table-parameter-to-oracle-odp-net-10g-via-ado-net-c — Hogan, Apr 29 '14 at 14:46
While I would seriously question such a design decision/requirement to begin with, compared to make a 'statement builder' class - I would properly myself look towards xml datatype and use that. — Allan S. Hansen, Apr 29 '14 at 14:46
what client software are you using -- this influences you choice. — Hogan, Apr 29 '14 at 15:08
The client software I'm using is written in Java. I've added this to the question. — Jack, Apr 29 '14 at 15:13

score 1 · Accepted Answer · answered Apr 29 '14 at 15:51

You could use a table parameter as I linked in the comments or you could pass in a comma separated list of values parse it to a table and join to that.

something like this (with input_lst as a string):

select * 
from tbl_list_item 
where tbl_list_item.class in
(
  select regexp_substr(input_lst,'[^,]+', 1, level) from dual
  connect by regexp_substr(input_lst, '[^,]+', 1, level) is not null
);

adapted from https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Which choice is better depends on your expected number of entries and what is easier for your client side. I think with a small number (2-20) the comma separated is a fine choice. With a large number you probably want to pass a table.

I will try an implementation and report back. – Jack Apr 29 '14 at 16:10 — Jack, Apr 29 '14 at 16:10

Jack · Answer 2 · 2014-05-08T10:59:45.750

A colleague actually suggested another way to achieve this and I think it is worth sharing. Basically, define a table type that can contain the arguments, then pass an array from the Java program that can be read from this table.

In my example, firstly define a table type of number:

create or replace type tbl_number as table of number;

Then, in the SQL package, define the function as:

function get_list_items_from_class(i_numbers in tbl_number)
  return tbl_list_item pipelined;

The function in the package body has one major change (apart from the definition obviously). Use the following select statement:

select * 
from tbl_list_item 
where tbl_list_item.class in
(
  select * from table(i_numbers)
);

This will select all the relevant items that match one of the integers that were passed to the "i_numbers" table. I like this way as it means less string parsing, both in the Java application and the SQL pacakage.

Here's how I passed the number arguments from the Java application using an ARRAY object.

ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("NUMBERS", con); //con is the database connection
ARRAY array = new ARRAY(arrayDesc, con, numberList.toArray()); // numberList is an ArrayList of integers which holds the arguments

array is then passed to the SQL function.

How do I write an SQL function that takes an unknown amount of numbers as parameters?

2 Answers2