8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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)
Related articles.
- Oracle REST Data Services (ORDS) : HTTP Headers and ORDS-Specific Bind Variables
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads
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 thebclose_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; /
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/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". HeaderContent-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. HeaderX-ORDS-FORWARD
.:status_code
: Sets the HTTP status code for the request. HeaderX-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:
- OWA_UTIL
- ORDS : Implicit Parameters
- Oracle REST Data Services (ORDS) : HTTP Headers and ORDS-Specific Bind Variables
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads
Hope this helps. Regards Tim...