Question:

Is it possible to submit a http request using a list of values to filter data in a table?

For example with GET: endpoint/ords/multisearch?product_id=100,200,300,400,500,600

Which would get translated to

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.

Stuff is the query parameter name AND the name of the :bind variable in our SQL code.

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

We build the IN list with a SELECT to our Function.

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.

Simpler code, that’s probably why I used it before.

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. select * from employees where ‘,’|| :stuff || ‘,’ like ‘%,’ || employee_id || ‘,%’

    • Hi Jeff, I just meant this could be a simpler SQL than the IN sub-query with regexp_substr.
      BTW, you mentioned “still requires the ORDS PARAMETER to be declared”. But actually it works without the declaration of URI parameters right? Any URI parameter in the http request is implicitly understood by ORDS and passed down to the SQL … and I have used this feature quite a lot.

    • You only need to declare a parameter if you want a different bind variable name, but it doesn’t hurt and it’s more evident of what’s going on.

Write A Comment