10 Implicit Parameters
This chapter describes the implicit parameters used in REST service handlers that are not explicitly declared. Oracle REST Data Services (ORDS) adds these parameters automatically to the resource handlers.
List of Implicit Parameters
The following table lists the implicit parameters:
Note:
Parameter names are case sensitive. For example, :CURRENT_USER is not a valid implicit parameter.Table 10-1 List of Implicit Parameters
Name | Type | Access Mode | HTTP Header | Description | Introduced |
---|---|---|---|---|---|
|
BLOB |
IN |
N/A |
Specifies the body of the request as a temporary BLOB. |
2.0 |
|
CLOB |
IN |
N/A |
Specifies the body of the request as a temporary CLOB. |
18.3 |
|
VARCHAR |
IN |
|
Specifies the MIME type of the request body, as indicated by the Content-Type request header. |
2.0 |
|
VARCHAR |
IN |
N/A |
Specifies the authenticated user for the request. If no user is authenticated, then the value is set to null. |
2.0 |
|
VARCHAR |
OUT |
|
Specifies the location where Oracle REST Data Services must forward a GET request to produce the response for this request. |
18.3 |
|
NUMBER |
IN |
N/A |
Specifies the zero-based offset of the first row to be displayed on a page. |
18.3 |
|
NUMBER |
IN |
N/A |
Specifies the maximum number of rows to be retrieved on a page. |
18.3 |
|
NUMBER |
IN |
N/A |
Specifies the zero based page offset in a paginated request. Note:
|
2.0 |
|
NUMBER |
IN |
N/A |
Specifies the maximum number of rows to be retrieved on a page. The |
2.0 |
|
NUMBER |
IN |
N/A |
Specifies the one-based index of the first row to be displayed in a paginated request. |
3.0 |
|
NUMBER |
IN |
N/A |
Specifies the one-based index of the last row to be displayed in a paginated request. |
3.0 |
|
NUMBER |
OUT |
|
Specifies the HTTP status code for the request. |
18.3 |
About the :body parameter
:body
implicit parameter is used in the resource handlers to receive the contents of the request body as a temporary BLOB.
Note:
Only POST or PUT requests can have a request body. The HTTP specification does not permit request bodies on GET or DELETE requests.Example 10-1 Example
begin
insert into tab (content) values (:body);
end;
Note:
The :body
implicit parameter must be dereferenced exactly once in a PL/SQL block. If it is dereferenced more than once, then the second and subsequent dereferences will appear to be empty. This is because the client sends the request body only once.
The following example will not work as intended because it dereferences the :body
parameter twice:
begin
insert into tab1(content) values (:body); -- request body will be inserted
insert into tab2(content) values (:body); -- an empty blob will be inserted
end;
:body
parameter value must be assigned to a local PL/SQL variable before it is used. This enables the local variable to be dereferenced more than once:declare
l_content blob := :body;
begin
insert into tabl(content) values(l_content);
insert into tab2(content) values(l_content);
end;
About the :body_text Parameter
The :body_text
implicit parameter is used in the resource handlers to receive the contents of the request body as a temporary CLOB. Typically, the content of the request body is textual (for example JSON or HTML content) and so, receiving the request body as a CLOB saves the resource handler author from the effort of converting the :body
BLOB parameter to a CLOB instance. Similar to the :body
parameter, the :body_text
parameter must be dereferenced only once in a PL/SQL block.
It is recommended to assign the :body_text
value to a local PL/SQL variable, and the PL/SQL variable is used throughout the PL/SQL block.
About the :content_type Parameter
The :content_type
implicit parameter provides the value of the Content-Type request header supplied with the request. If no Content-Type header is present in the request, then a null value is returned.
About the :current_user Parameter
:current_user
implicit parameter provides the identity of the user authenticated for the request.
Note:
In a scenario, where the user is not authenticated, the value is set to null. For example, if the request is for a public resource, then the value will be set to null.About the :status_code Parameter
The :status_code
implicit parameter enables a resource handler to
indicate the HTTP status code value to include in a response. The value must be one of
the numeric values defined in the HTTP
Specification document.
About the :forward_location Parameter
The :forward_location
implicit parameter provides a mechanism for PL/SQL based resource handlers to produce a response for a request.
Consider a POST request that results in the creation of a new resource. Typically, the response of a POST request for REST APIs contains the location of the newly created resource (in the Location response header) along with the representation of the new resource. The presence of the Location header in the response indicates that there must be a GET resource handler that can produce a response for the specified location.
Instead of applying logic to the POST resource handler to render the representation of the new resource in the response, the resource handler can delegate that task to the existing GET Resource Handler.
The following resource handler defines a POST handler that delegates the generation of the response to a GET resource handler:
ords.define_handler(
p_module_name => 'tickets.collection',
p_pattern => '.',
p_method => 'POST',
p_mimes_allowed => 'application/json',
p_source_type => ords.source_type_plsql,
p_source => '
declare
l_owner varchar2(255);
l_payload clob;
l_id number;
begin
l_payload := :body_text;
l_owner := :current_user;
l_id := ticket_api.create_ticket(
p_json_entity => l_payload,
p_author => l_owner
);
:forward_location := ''./'' || l_id;
:status_code := 201;
end;
'
);
-
The
ords.define_handler
API is used to add a POST handler to an existing resource module namedtickets.collection
. -
The
p_pattern
with value '.' indicates that the POST handler should be bound to the root resource of the resource module. If the base path of thetickets.collection
' is/tickets/
, then the POST handler is bound to the/tickets/
URL path. -
The
p_mimes_allowed
value indicates that the POST request must have a Content-Type header value ofapplication/json
'. -
The
p_source_type
value indicates that the source of the POST handler is a PL/SQL block. -
The
p_source
value contains the source of the PL/SQL block:Where:
Note:
The:body_text
implicit parameter is assigned to a local variable, so that it can be dereferenced more than once.-
The identity of the user, making the POST request, is determined from the
:current_user
implicit parameter. -
The PL/SQL block, delegates the task of storing the request payload to a PL/SQL package level function. The PL/SQL block should only contain logic to bridge from the HTTP request to the PL/SQL package invocation.
Note:
When all the data modification operations are wrapped in a PL/SQL API, the PL/SQL block can be independently unit tested. Long and complicated PL/SQL blocks are an anti-pattern indicative of code that is difficult to test and maintain. -
The PL/SQL package level function returns the ID of the newly created resource.
-
The
:forward_location
implicit parameter is assigned the value of'./' || l_id
. For example, if the value ofl_id
is 4256, then the value of:forward_location
is/tickets/4256
.When ORDS evaluates the preceding PL/SQL block and checks the value assigned to the
:forward_location
implicit parameter, it initiates a GET request against the specified location (for example,/tickets/4256
) and return the response generated by the GET request as the response of the POST request. In addition, ORDS includes a location response header with the fully resolved URL of the:forward_location
value. -
The
:status_code
implicit parameter is assigned the HTTP response status code value. The 201 (Created) status code indicates that a new resource is created. This value will override the status code generated by the GET request.
-
About the Pagination Implicit Parameters
The following table lists the pagination implicit parameters:
Note:
Oracle REST Data Services reserves the use of the query parameters,page
, offset,
and limit
. It is not permitted to define REST services that use
named bind parameters with any of the preceding query parameter names.
Alternatively, REST services must use the appropriate pagination implicit parameters
defined in the following table:
Table 10-2 Pagination Implicit Parameters
Name | Description | Status |
---|---|---|
|
Specifies the zero based page offset in a pagination request. |
Deprecated |
|
Specifies the maximum number of rows to be retrieved on a page. |
Deprecated |
|
Specifies the index of the first row to be displayed in a pagination request. |
Not Recommended |
|
Specifies the index of the last row to displayed in a pagination request. |
Not Recommended |
|
Specifies the zero based index of the first row to be displayed on a page. |
Recommended |
|
Specifies the maximum number of rows to be retrieved on a page. |
Recommended |
About the :page_offset Parameter
:page_offset
implicit parameter is provided for backward compatibility, so it is used only with source_type_query
source type resource handlers.
Note:
-
The
source_type_query
source type is deprecated, instead use thesource_type_collection
feed parameter. -
The
:page_offset
implicit parameter is deprecated, instead use the:row_offset
implicit parameter.
About the :page_size Parameter
The :page_size
implicit parameter is used to indicate the maximum number of rows to be retrieved on a page. :page_size
parameter is provided for backward compatibility. This parameter is deprecated, instead use :fetch_size
implicit parameter.
About the :row_offset Parameter
The :row_offset
implicit parameter indicates the number of the first row to be displayed on a page. The :row_offset
implicit parameter is used when you are using both a wrapper pagination query and row_number()
(used in Oracle 11g and earlier releases). Starting Oracle 12c or later releases, Oracle recommends using the :fetch_offset
implicit parameter and a row limiting clause instead of the :row_offset
parameter.
About the :row_count Parameter
The :row_count
implicit parameter is used to indicate the number of rows to be displayed on a page. The :row_count
value is the value of the sum of :row_offset
and the pagination size. The :row_count
implicit parameter is useful when implementing pagination using a wrapper pagination query and row_number()
method that was used in Oracle database 11g and earlier releases. Starting Oracle Database release 12c or later, Oracle recommends that you use :fetch_size
parameter and a row limiting clause instead.
About the :fetch_offset Parameter
The :fetch_offset
implicit parameter is used to indicate the zero based offset of the first row to display in a given page. The :fetch_offset
implicit parameter is used when you implement pagination using a row limiting clause, which is recommended for use with Oracle 12c and later releases.
About the :fetch_size Parameter
:fetch_size
implicit parameter is used to indicate the maximum number of rows to retrieve on a page. ORDS always sets the value of :fetch_size
to the pagination size plus one. The presence or absence of the extra row helps ORDS in determining if there is a subsequent page in the results or not.
Note:
The extra row that is queried is never displayed on the page.About Automatic Pagination
This section describes the automatic pagination process.
source_type_collection_feed
or source_type_query
has a non zero pagination size (p_items_per_page
) and the source of the GET resource handler does not dereference any of the implicit pagination parameters discussed in the preceding sections, then ORDS automatically wraps the query in a pagination clause to constrain the query results to include only the values from the requested page. With automatic pagination, the resource handler author needs to specify only the pagination size, and ORDS automatically handles the remaining effort in paginating the resource.
Note:
All resource modules have a default pagination size (p_items_per_page
) of 25. So, by default automatic pagination is enabled.
About Manual Pagination
This section describes the manual pagination process.
Note:
The GET resource handler must specify the desired pagination size so that ORDS can correctly calculate the required values for the implicit pagination parameters.Examples
Manual pagination example using row limiting clause
The following example defines a REST service that uses a row limiting clause to paginate the query result set. This is the recommended way to implement manual pagination:
begin
ords.define_service(
p_module_name => 'example.paging',
p_base_path => '/example/',
p_pattern => '/paged',
p_items_per_page => 7,
p_source => 'select * from emp e order by empno desc offset :fetch_offset rows fetch next :fetch_size rows only'
);
commit;
end;
Manual pagination example using row_number() method
The following example defines a REST service that uses a wrapper query and row_number()
method. This approach is not recommended.
begin
ords.define_service(
p_module_name => 'example.paging',
p_base_path => '/example/',
p_pattern => '/paged',
p_items_per_page => 7,
p_source => 'select * from (select q_.* , row_number() over (order by 1) rn__ from (select * from emp e order by empno desc) q_ )where rn__ between :row_offset and :row_count'
);
commit;
end;