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
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:
- What you can do with an Oracle REST API is only limited by your SQL and PL/SQL code.
- Know what keywords to ‘google for’ when you want help with a problem
- These patterns pop up, over and over again. Experience gives you the ability to recognize them.