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
    l_plist apex_t_varchar2;
    l_map   apex_application_global.vc_map;

    -- 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

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

            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);
                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 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.

    l_map apex_application_global.vc_map;
    l_key varchar2(255);

    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 :=;
    end loop;

Some musings on camping


  1. Hello Jeff, your function is to extract parameters from the query_string, but for APEX applications that are not with the “friendly_url” we still need to extract with the “:”

    In your opinion, is it possible to find via an ORDS function, procedure or other thing the address of the APEX page in a non-“friendly url” format even if the APEX application is configured to “friendly_url”?

    Good Job for your fonction 🙂

    • Hi Michel, I assume you’re referring to the “p” parameter that legacy APEX urls used in the past. This parameter is parsed directly by APEX itself and is not relevant to ORDS services.

      However, if for some reason you need to parse a “p”-style parameter, you might use the function APEX_STRING.split(l_p_value,’:’,3) for this. The 2nd parameter is expected to be the page number (or alias).

Leave a Reply

Your email address will not be published / Required fields are marked *