Implicit Parameters

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
`:body`	BLOB	IN	N/A	Specifies the body of the request as a temporary BLOB.	2.0
`:body_text`	CLOB	IN	N/A	Specifies the body of the request as a temporary CLOB.	18.3
`:content_type`	VARCHAR	IN	`Content-Type`	Specifies the MIME type of the request body, as indicated by the Content-Type request header.	2.0
`:current_user`	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
`:forward_location`	VARCHAR	OUT	`X-ORDS-FORWARD-LOCATION`	Specifies the location where Oracle REST Data Services must forward a GET request to produce the response for this request.	18.3
`:fetch_offset`	NUMBER	IN	N/A	Specifies the zero-based offset of the first row to be displayed on a page.	18.3
`:fetch_size`	NUMBER	IN	N/A	Specifies the maximum number of rows to be retrieved on a page.	18.3
`:page_offset`	NUMBER	IN	N/A	Specifies the zero based page offset in a paginated request. Note: `The :page_offset` parameter is deprecated. Use `:row_offset` parameter instead.	2.0
`:page_size`	NUMBER	IN	N/A	Specifies the maximum number of rows to be retrieved on a page. The `:page_size` parameter is deprecated. Use `:fetch_size parameter` instead.	2.0
`:row_offset`	NUMBER	IN	N/A	Specifies the one-based index of the first row to be displayed in a paginated request.	3.0
`:row_count`	NUMBER	IN	N/A	Specifies the one-based index of the last row to be displayed in a paginated request.	3.0
`:status_code`	NUMBER	OUT	`X-ORDS-STATUS-CODE`	Specifies the HTTP status code for the request.	18.3

About the :body parameter

The :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

The following example illustrates a PL/SQL block that stores the request body in a database table:

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;

To avoid this limitation, the :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

The :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;
  '
);

Where:

The ords.define_handler API is used to add a POST handler to an existing resource module named tickets.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 the tickets.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 of application/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 of l_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
`:page_offset`	Specifies the zero based page offset in a pagination request.	Deprecated
`:page_size`	Specifies the maximum number of rows to be retrieved on a page.	Deprecated
`:row_offset`	Specifies the index of the first row to be displayed in a pagination request.	Not Recommended
`:row_count`	Specifies the index of the last row to displayed in a pagination request.	Not Recommended
`:fetch_offset`	Specifies the zero based index of the first row to be displayed on a page.	Recommended
`:fetch_size`	Specifies the maximum number of rows to be retrieved on a page.	Recommended

About the :page_offset Parameter

The :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 the source_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

The :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.

If a GET resource handler source type, 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.

In some scenarios, a GET resource handler needs to perform pagination on its own rather than delegating the pagination process to ORDS. In such cases, the source of the GET resource handler will dereference one or more implicit pagination parameters discussed in the preceding sections.

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;