REST API: Query Parameter values to a SQL WHERE IN (:list)

Question:

select * from tab where product_id in (100,200,300,400,500,600)

Yes, it’s “just SQL,” of course

This is a fairly common question, how do we take a list of numbers and pass that as a :bind variable to a WHERE IN clause.

A blogger back in 2011 wrote how to do this with a FUNCTION using a user defined type, used to build a table of numbers. I’ve done it myself before with a regular express/connect by list, and Steve reminds me you could do this with the APEX_SPLIT() function.

Let’s look at the first two options, as they doesn’t require APEX to be installed.

Using PL/SQL TABLE of NUMBER and a FUNCTION

The code:

CREATE TYPE num_table AS TABLE OF NUMBER;
/
CREATE OR REPLACE FUNCTION in_number_list (p_in_list  IN  VARCHAR2)
RETURN num_table
AS
l_tab   num_table := num_table();
l_text  VARCHAR2(32767) := p_in_list || ',';
l_idx   NUMBER;
BEGIN
LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_tab.extend;
l_tab(l_tab.last) := to_number(TRIM(SUBSTR(l_text, 1, l_idx - 1)));
l_text := SUBSTR(l_text, l_idx + 1);
END LOOP;
RETURN l_tab;
END;
/

Building the REST API

The SQL for my GET handler is this –

select *
from employees where employee_id IN 
(SELECT * FROM TABLE( CAST ( in_number_list(:stuff) as num_table)))

But where does :stuff get declared?

Creating a REST Parameter to pull info off the request URI

On the REST Handler page, we can define a parameter, this allows us to grab information from the request and make it available to the block of code being ran by the database.

And this is what it looks like when we put it all together –

Or, no custom PL/SQL or TYPEs required

Or we could just do this –

select * from employees
where employee_id  IN (
    SELECT regexp_substr(:stuff,'[^,]+',1,level)
      FROM dual CONNECT BY
        regexp_substr(:stuff,'[^,]+',1,level) IS NOT NULL
)

Note this solution still requires the ORDS PARAMETER to be declared, that’s how we get the info off the request URI into our bind variable.

And the moral of our story is what, exactly?

I have three takeaways here:

1. What you can do with an Oracle REST API is only limited by your SQL and PL/SQL code.
2. Know what keywords to ‘google for’ when you want help with a problem
3. These patterns pop up, over and over again. Experience gives you the ability to recognize them.