oracle execute immediate using reference to multiple variables

Question

I'm using Oracle Rest Data Services to build an app.

I can easily read & write with something like this GET http://example.com/foo/bar that runs query SELECT * FROM bar or

POST http://example.com/foo/bar { "first": "a'b", "second": "c,d" }

that runs query INSERT INTO bar (first, second) VALUES (:first, :second)

Where query parameters are bound from request body.

Now, I'd like to build a route that run a dynamic query.

I can do that with one binding param, eg.:

POST http://example.com/foo/query { "query": "DELETE FROM bar WHERE first = :param", "param": "a'b" }

that runs query

BEGIN EXECUTE IMMEDIATE :query USING :param; END;

But I don't know how to do it with multiple params. For eg.

POST http://example.com/foo/query { "query": "DELETE FROM bar WHERE first = :first AND second = :second", "bindings": "first,second", "first": "a'b", "second": "c,d" }

The query should be something like

DECLARE params ...? -- (params variable should set USING list from :bindings request param) BEGIN EXECUTE IMMEDIATE :query USING params; END;

Any idea?

Answer 1

It's only a small change from your previous example. See this example from the docs for more info.

BEGIN EXECUTE IMMEDIATE :query USING :first, :second; END;

As a warning, the bind variable names in the query don't need to match the names in the EXECUTE IMMEDIATE block - they're evaluated in the order they occur, not by name. So in this example:

:query := 'DELETE FROM bar WHERE first = :a and second = :b';
execute immediate :query using :my_var1, :my_var2;

The value of pl/sql variable my_var1 is assigned to sql bind variable a since they're both the first ones. It can get more complicated if you want to repeat variable names... but I think that's enough to answer your question.