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

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.

Create a Test Database User
Enable ORDS
Read HTTP Headers (OWA_UTIL)
Read HTTP Headers (ORDS.DEFINE_PARAMETER)
Write HTTP Headers (OWA_UTIL)
Write HTTP Headers (ORDS.DEFINE_PARAMETER)
ORDS-Specific Bind Variables (Implicit Parameters)

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

--DROP USER testuser1 CASCADE;

CREATE USER testuser1 IDENTIFIED BY testuser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO testuser1;

Enable ORDS

We are going to assume ORDS is installed and is available from the following base URL.

http://localhost:8080/ords/

We have to enable Oracle REST data services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name. In this example we used "hr".

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'hr',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Web services from the schema can now be referenced using the following base URL.

http://localhost:8080/ords/hr/

We are now ready to start.

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.

Used directly in the ORDS service definition.
Used as parameter values passed into a PL/SQL stored procedure or function.
Used inside a PL/SQL stored procedure or function. These values will only be set if the code is called from a gateway (ORDS, mod_plsql, EPG).

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.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule1',
    p_base_path      => 'testmodule1/',
    p_pattern        => 'cgi-headers1/',
    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'));
                            END;]',
    p_items_per_page => 0);

  COMMIT;
END;
/

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

$ curl http://localhost:8080/ords/hr/testmodule1/cgi-headers1/
Individual Calls:
=======================================================
SERVER_SOFTWARE=Mod-Apex
SERVER_NAME=localhost
GATEWAY_INTERFACE=CGI/1.1
SERVER_PROTOCOL=HTTP/1.1
SERVER_PORT=8080
REQUEST_METHOD=GET
PATH_INFO=/
PATH_TRANSLATED=
SCRIPT_NAME=/ords/hr/testmodule1/ords-headers1
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=
$

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

We could call the OWA_UTIL.PRINT_CGI_ENV procedure to list all the available HTTP header values in HTML format.

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule2',
    p_base_path      => 'testmodule2/',
    p_pattern        => 'cgi-headers2/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             OWA_UTIL.mime_header('text/plain');
                             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://localhost:8080/ords/hr/testmodule2/cgi-headers2/
List from PRINT_CGI_ENV including <br /> terminator:
=======================================================
REMOTE_IDENT = <br />
REMOTE_USER = TESTUSER1<br />
user-agent = curl/7.29.0<br />
host = localhost:8080<br />
accept = */*<br />
APEX_LISTENER_VERSION = 19.2.0.r1991647<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 = localhost: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 = 0:0:0:0:0:0:0:1<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/hr/testmodule2/cgi-headers2<br />
SCRIPT_PREFIX = <br />
SERVER_NAME = localhost<br />
SERVER_PORT = 8080<br />
SERVER_PROTOCOL = HTTP/1.1<br />
SERVER_SOFTWARE = Mod-Apex<br />
WEB_AUTHENT_PREFIX =  <br />
X-APEX-METHOD = GET<br />
X-APEX-BASE = http://localhost:8080/ords/hr/<br />
X-APEX-PATH = testmodule2/cgi-headers2/<br />
X-APEX-REMOTE-ADDRESS = 0:0:0:0:0:0:0:1<br />
X-APEX-CHARSET = UTF-8<br />
HTTP_COOKIE = <br />
$

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    => 'testmodule3',
    p_base_path      => 'testmodule3/',
    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=' || :tims_header);
                          END;]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'testmodule3',
    p_pattern            => 'ords-headers/',
    p_method             => 'GET',
    p_name               => 'Tims-Header',
    p_bind_variable_name => 'tims_header',
    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://localhost:8080/ords/hr/testmodule3/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.

Open the header using the MIME_HEADER procedure, making sure the bclose_header parameter is set to false so you can continue to write to the header.
Write additional header information.
Close the header with the HTTP_HEADER_CLOSE procedure.
Write any required output from the service.

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    => 'testmodule4',
    p_base_path      => 'testmodule4/',
    p_pattern        => 'write-headers1/',
    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://localhost:8080/ords/hr/testmodule4/write-headers1/
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    => 'testmodule5',
    p_base_path      => 'testmodule5/',
    p_pattern        => 'write-headers2/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[BEGIN
                             :fruit := 'banana';
                             :tims_header := 'Some Value';
                         END;]',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'testmodule5',
    p_pattern            => 'write-headers2/',
    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        => 'testmodule5',
    p_pattern            => 'write-headers2/',
    p_method             => 'GET',
    p_name               => 'Tims-Header',
    p_bind_variable_name => 'tims_header',
    p_source_type        => 'HEADER',
    p_access_method      => 'OUT'
  );

  COMMIT;
END;
/

$ curl -i http://localhost:8080/ords/hr/testmodule5/write-headers2/
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.

X-APEX-STATUS-CODE : The HTTP status code returned by ORDS (pre-18.3).
X-APEX-FORWARD : The location of the updated resource (pre-18.3).
X-ORDS-STATUS-CODE : The HTTP status code returned by ORDS (18.3 onward).
X-ORDS-FORWARD : The location of the updated resource (18.3 onward).

ORDS-Specific Bind Variables (Implicit Parameters)

ORDS provides a number of bind variable values, or implicit parameters, 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.

Used directly in the service definition.
Used as parameter values passed into a PL/SQL stored procedure or function from the service definition.

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

:body : The request payload presented as BLOB. Note this value MUST only be dereferenced once, if it needs to be dereferenced more than once in a handler, assign it's value to a local variable and dereference the local variable instead. This is because the stream backing the BLOB can only be read once.
:body_text : The request payload presented as CLOB. Introduced in 18.3, this is the text equivalent of ":body".
:content_type : The Media Type of the request body. For example "application/json". Header Content-Type.
:current_user : The identity of authenticated user. Blank if no authentication is presented.
:fetch_offset : The page number, indexed from zero.
:fetch_size : The maximum number of rows to include in the page. Note this value will be +1 of the actual desired page size. If the page size is configured to be 25, this value will be 26. ORDS always requests one extra row so it can detect if there is a subsequent page or not (If 25 or less rows are returned then we are on the last page). This replaces the deprecated :page_size parameter.
:row_offset : The offset of the first row in this page, indexed from one. This replaces the deprecated :page_offset parameter.
:row_count : The offset of the last row in this page, indexed from one (in other words :row_count = :row_offset + :page_size)
:forward_location : The location ORDS will forward a GET to, to get a response. Header X-ORDS-FORWARD.
:status_code : Sets the HTTP status code for the request. Header X-ORDS-STATUS-CODE.

From ORDS 18.3 onward, these are included in the ORDS documentation here.

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    => 'testmodule6',
    p_base_path      => 'testmodule6/',
    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 \
       --data-binary '{"fruit":"banana"}' \
       -H "Content-Type: application/json" \
       -u "TESTUSER1:testuser1" \
        http://localhost:8080/ords/hr/testmodule6/ords-binds/
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.