Get user-defined query parameters from ORDS

This would be a very rare requirement, as most of the time when you are designing an ORDS REST service you should know what query parameters your service supports. However, in the case where your users are allowed to supply an arbitrary list of additional parameters to your service, you won’t know what the keys will be for these parameters.

Since you can’t define the user-defined query parameters in your ORDS endpoint, they won’t be supplied via bind variables. Instead, in your PL/SQL handler you need to get the original query string using owa_util.get_cgi_env('QUERY_STRING'), then parse it to find the query parameters.

Here’s what I’ve used:

function query_string_map
    return apex_application_global.vc_map
is
    l_plist apex_t_varchar2;
    l_map   apex_application_global.vc_map;
begin

    -- query string may be like:
    --     param1=abc&param2=def&param3=ghi
    -- or blanks may be included like:
    --     param1=abc&param2=&param3=ghi
    -- or the = symbol may be omitted:
    --     param1=abc&param2&param3=ghi

    l_plist := apex_string.split(owa_util.get_cgi_env('QUERY_STRING'), '&');

    for i in 1..l_plist.count loop

        declare
            l_offset pls_integer;
            l_key    varchar2(255);
            l_value  varchar2(32767);
        begin

            l_offset := instr(l_plist(i), '=');

            if l_offset > 0 then
                l_key   := substr(l_plist(i), 1, l_offset - 1);
                l_value := substr(l_plist(i), l_offset + 1);
            else
                l_key := l_plist(i);
                -- the value is null
            end if;

            -- ORDS may encode %20 as '+', but this is not detected by utl_url
            l_key := replace(l_key, '+', ' ');
            l_key := sys.utl_url.unescape(l_key, 'UTF-8');
            
            if l_value is not null then
                l_value := replace(l_value, '+', ' ')
                l_value := sys.utl_url.unescape(l_value, 'UTF-8');
            end if;

            -- add the key/value to the map
            l_map(l_key) := l_value;

        end;

    end loop;

    return l_map;
end query_string_map;

This takes the query string and splits it on each occurrence of the & symbol. Each parsed part is expected to take the form key=value, key= or just key (with no = symbol). It converts any escaped URL characters and builds a map of key/value pairs and returns it.

The calling process can then use the map to process each key/value in turn, e.g.

declare
    l_map apex_application_global.vc_map;
    l_key varchar2(255);
begin

    l_map := query_string_map;

    l_key := l_map.first;
    while l_key is not null loop

        -- do something with the key/value
        dbms_output.put_line(l_key || ' : ' || l_map(l_key));

        l_key := l_map.next(l_key);
    end loop;

end;

Jeffrey Kemp 18 April 2023

ORDS / tips-&-tricks /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.