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.

Read HTTP Headers (ORDS.DEFINE_PARAMETER)

We can use ORDS parameters to read from the response header using the ORDS.DEFINE_PARAMETER procedure. The following example uses a bind variable in the service definition, which is then defined as a parameter read from the request header.

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('Tims-Header=' || :some_value);
                          END;]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'testmodule1',
    p_pattern            => 'ords-headers/',
    p_method             => 'GET',
    p_name               => 'Tims-Header',
    p_bind_variable_name => 'some_value',
    p_source_type        => 'HEADER',
    p_param_type         => 'STRING',
    p_access_method      => 'IN');

  COMMIT;
END;
/

We can see the output from the web service using the following curl call. The "-H" parameter allows us to write to the request header, which the service uses to write out the value.

$ curl -H "Tims-Header: Some Value" \
    "http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule1/ords-headers/"
Tims-Header=Some Value
$

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"}
$

Write HTTP Headers (ORDS.DEFINE_PARAMETER)

We can use ORDS parameters to write to the response header using the ORDS.DEFINE_PARAMETER procedure. The following example uses two bind variables in the service definition, which are then defined as parameters, one written to the response and one written to the response header.

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule4',
    p_base_path      => 'testmodule4/',
    p_pattern        => 'fruit/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             :fruit := 'banana';
                             :some_value := 'Some Value';
                         END;]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'testmodule4',
    p_pattern            => 'fruit/',
    p_method             => 'GET',
    p_name               => 'fruit',
    p_bind_variable_name => 'fruit',
    p_source_type        => 'RESPONSE',
    p_access_method      => 'OUT'
  );

  ORDS.define_parameter(
    p_module_name        => 'testmodule4',
    p_pattern            => 'fruit/',
    p_method             => 'GET',
    p_name               => 'Tims-Header',
    p_bind_variable_name => 'some_value',
    p_source_type        => 'HEADER',
    p_access_method      => 'OUT'
  );

  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/testmodule4/fruit/"
HTTP/1.1 200
Tims-Header: Some Value
ETag: "wm1dAv0xxxdEhVcCfIcLnBaAUm/C7ZyoVm2vIpbPkJZtcHrOL+BfVcIlvhU1bYsYY/4Kwfe3XuMO7HqDV2AJxQ=="
Content-Type: application/json
Transfer-Encoding: chunked
Date: Thu, 12 Oct 2017 22:28:49 GMT

{"fruit":"banana"}
$

There are some ORDS-specific headers you might find useful including the following.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.