8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : HTTP Headers (OWA_UTIL) and ORDS-Specific Bind Variables

This article describes how to get information from the HTTP header of a request to ORDS using the OWA_UTIL package, as well as how to reference ORDS-specific bind variables.

Most of this should be familiar if you have done any programming using the PL/SQL Web Toolkit available since Oracle 8i, or if you are an APEX developer.

Related articles.

Read HTTP Headers (OWA_UTIL)

The OWA_UTIL.GET_CGI_ENV function is used to return Common Gateway Interface (CGI) variables, as well as any custom headers sent in the request. These OWA_UTIL.GET_CGI_ENV function calls can be used in a number of ways when coding RESTful web services in PL/SQL.

The service below returns some common CGI variable values as a plain text document. In this case the calls to the OWA_UTIL package are directly in service definition, with the PL/SQL source quoted using the q'[ ... ]' syntax. It also calls the OWA_UTIL.PRINT_CGI_ENV procedure to list all the available HTTP header values in HTML format.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule1',
    p_base_path      => 'testmodule1/',
    p_pattern        => 'ords-headers/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             OWA_UTIL.mime_header('text/plain');
                             HTP.p('Individual Calls:');
                             HTP.p('=======================================================');
                             HTP.p('SERVER_SOFTWARE='      || OWA_UTIL.get_cgi_env('SERVER_SOFTWARE'));
                             HTP.p('SERVER_NAME='          || OWA_UTIL.get_cgi_env('SERVER_NAME'));
                             HTP.p('GATEWAY_INTERFACE='    || OWA_UTIL.get_cgi_env('GATEWAY_INTERFACE'));
                             HTP.p('SERVER_PROTOCOL='      || OWA_UTIL.get_cgi_env('SERVER_PROTOCOL'));
                             HTP.p('SERVER_PORT='          || OWA_UTIL.get_cgi_env('SERVER_PORT'));
                             HTP.p('REQUEST_METHOD='       || OWA_UTIL.get_cgi_env('REQUEST_METHOD'));
                             HTP.p('PATH_INFO='            || OWA_UTIL.get_cgi_env('PATH_INFO'));
                             HTP.p('PATH_TRANSLATED='      || OWA_UTIL.get_cgi_env('PATH_TRANSLATED'));
                             HTP.p('SCRIPT_NAME='          || OWA_UTIL.get_cgi_env('SCRIPT_NAME'));
                             HTP.p('QUERY_STRING='         || OWA_UTIL.get_cgi_env('QUERY_STRING'));
                             HTP.p('REMOTE_HOST='          || OWA_UTIL.get_cgi_env('REMOTE_HOST'));
                             HTP.p('REMOTE_ADDR='          || OWA_UTIL.get_cgi_env('REMOTE_ADDR'));
                             HTP.p('AUTH_TYPE='            || OWA_UTIL.get_cgi_env('AUTH_TYPE'));
                             HTP.p('REMOTE_USER='          || OWA_UTIL.get_cgi_env('REMOTE_USER'));
                             HTP.p('REMOTE_IDENT='         || OWA_UTIL.get_cgi_env('REMOTE_IDENT'));
                             HTP.p('CONTENT_TYPE='         || OWA_UTIL.get_cgi_env('CONTENT_TYPE'));
                             HTP.p('CONTENT_LENGTH='       || OWA_UTIL.get_cgi_env('CONTENT_LENGTH'));
                             HTP.p('HTTP_ACCEPT='          || OWA_UTIL.get_cgi_env('HTTP_ACCEPT'));
                             HTP.p('HTTP_ACCEPT_LANGUAGE=' || OWA_UTIL.get_cgi_env('HTTP_ACCEPT_LANGUAGE'));
                             HTP.p('HTTP_USER_AGENT='      || OWA_UTIL.get_cgi_env('HTTP_USER_AGENT'));
                             HTP.p('HTTP_COOKIE='          || OWA_UTIL.get_cgi_env('HTTP_COOKIE'));
                            
                             HTP.p(' ');
                             HTP.p('List from PRINT_CGI_ENV including <br /> terminator:');
                             HTP.p('=======================================================');
                             OWA_UTIL.print_cgi_env;
                           END;]',
    p_items_per_page => 0);

  COMMIT;
END;
/

We can see the output from the web service using the following curl call.

$ curl "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/ords-headers/"
Individual Calls:
=======================================================
SERVER_SOFTWARE=Mod-Apex
SERVER_NAME=ol7-122.localdomain
GATEWAY_INTERFACE=CGI/1.1
SERVER_PROTOCOL=HTTP/1.1
SERVER_PORT=8080
REQUEST_METHOD=GET
PATH_INFO=/
PATH_TRANSLATED=
SCRIPT_NAME=/ords/pdb1/testuser1/testmodule1/ords-headers
QUERY_STRING=
REMOTE_HOST=
REMOTE_ADDR=192.168.56.107
AUTH_TYPE=
REMOTE_USER=TESTUSER1
REMOTE_IDENT=
CONTENT_TYPE=
CONTENT_LENGTH=
HTTP_ACCEPT=*/*
HTTP_ACCEPT_LANGUAGE=
HTTP_USER_AGENT=curl/7.29.0
HTTP_COOKIE=

List from PRINT_CGI_ENV including <br /> terminator:
=======================================================
user-agent = curl/7.29.0<br />
host = ol7-122.localdomain:8080<br />
accept = */*<br />
APEX_LISTENER_VERSION = 17.3.0.248.08.45<br />
DAD_NAME = <br />
DOC_ACCESS_PATH = <br />
DOCUMENT_TABLE = <br />
GATEWAY_IVERSION = 3<br />
GATEWAY_INTERFACE = CGI/1.1<br />
HTTP_ACCEPT = */*<br />
HTTP_ACCEPT_ENCODING = <br />
HTTP_ACCEPT_LANGUAGE = <br />
HTTP_ACCEPT_CHARSET = <br />
HTTP_IF_MODIFIED_SINCE = <br />
HTTP_IF_NONE_MATCH = <br />
HTTP_HOST = ol7-122.localdomain:8080<br />
HTTP_ORACLE_ECID = <br />
HTTP_PORT = 8080<br />
HTTP_REFERER = <br />
HTTP_USER_AGENT = curl/7.29.0<br />
PATH_ALIAS =  <br />
PATH_INFO = /<br />
PLSQL_GATEWAY = WebDb<br />
QUERY_STRING = <br />
REMOTE_ADDR = 192.168.56.107<br />
REMOTE_USER = TESTUSER1<br />
REQUEST_CHARSET = AL32UTF8<br />
REQUEST_IANA_CHARSET = UTF-8<br />
REQUEST_METHOD = GET<br />
REQUEST_PROTOCOL = http<br />
REQUEST_SCHEME = http<br />
SCRIPT_NAME = /ords/pdb1/testuser1/testmodule1/ords-headers<br />
SCRIPT_PREFIX = <br />
SERVER_NAME = ol7-122.localdomain<br />
SERVER_PORT = 8080<br />
SERVER_PROTOCOL = HTTP/1.1<br />
SERVER_SOFTWARE = Mod-Apex<br />
WEB_AUTHENT_PREFIX =  <br />
HTTP_COOKIE = <br />
$

This header information provides additional context about the call to the web service.

ORDS-Specific Bind Variables

ORDS provides a number of bind variable values accessible only from your service definition, which means they can only be used in the following ways when coding RESTful web services in PL/SQL.

The available bind variables are as follows. Thanks to Colm Divilly for giving me the correct definitions for these.

The service below returns the ORDS-specific bind variable values as a plain text document. Notice the :body value has to be cast as the HTP.P procedure doesn't work directly with BLOB types.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule2',
    p_base_path      => 'testmodule2/',
    p_pattern        => 'ords-binds/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             OWA_UTIL.mime_header('text/plain');
                             HTP.p('content_type=' || :content_type);
                             HTP.p('body='         || UTL_RAW.cast_to_varchar2(:body));
                             HTP.p('current_user=' || :current_user);
                             HTP.p('page_size='    || :page_size);
                             HTP.p('page_offset='  || :page_offset);
                             HTP.p('row_offset='   || :row_offset);
                             HTP.p('row_count='    || :row_count);
                          END;]',
    p_items_per_page => 0);

  COMMIT;
END;
/

We can see the output from the web service using the following curl call.

$ curl -X POST "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule2/ords-binds/" \
       --data-binary {"fruit":"banana"} \
       -H "Content-Type: application/json" \
       -u TESTUSER1:testuser1
content_type=application/json
body={fruit:banana}
current_user=TESTUSER1
page_size=
page_offset=
row_offset=
row_count=
$

If you need to process complex JSON documents you can pass them as a BLOB type parameter to a stored procedure, as described here.

Write HTTP Headers (OWA_UTIL)

When you are manually creating a response from a web service you can alter the contents of the header using the OWA_UTIL package in the following way.

The following example creates a service that sends back some information in the header, and a basic JSON response.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule3',
    p_base_path      => 'testmodule3/',
    p_pattern        => 'fruit/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[DECLARE
                             l_response  VARCHAR2(32767);
                           BEGIN
                             -- Build response.
                             l_response := '{"fruit":"banana"}';

                             -- Amend header.
                             OWA_UTIL.mime_header('application/json', FALSE);
                             HTP.p('Tims-Header: Some Value');
                             OWA_UTIL.http_header_close;

                             -- Output response text.
                             HTP.p(l_response);
                         END;]',
    p_items_per_page => 0);

  COMMIT;
END;
/

We can see the output from the web service using the following curl call. The "-i" parameter displays the header information. Notice the custom header value and the response text are returned as expected.

$ curl -i "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule3/fruit/"
HTTP/1.1 200
Tims-Header: Some Value
ETag: "tA9OFS4d1mfWRgz/4LEcQJWNuuIAjq1b3932TC0gla0LdXUr9H1FJH8VP5v9S0/M+WnaV5h15pYlESCxk8wrKg=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Thu, 12 Oct 2017 22:28:49 GMT

{"fruit":"banana"}
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.