1. Developer's Guide
  2. ORDS PL/SQL Package Reference

4 ORDS PL/SQL Package Reference

The ORDS PL/SQL package contains subprograms (procedures and functions) for developing RESTful services using Oracle REST Data Services.

Related Topics

4.1 ORDS.CREATE_ROLE

Format

ORDS.CREATE_ROLE(
   p_role_name IN sec_roles.name%type);

Description

CREATE_ROLE creates an Oracle REST Data Services role with the specified name.

Parameters

p_role_name

Name of the role.

Usage Notes

After the role is created, it can be associated with any Oracle REST Data Services privilege.

Examples

The following example creates a role.

EXECUTE ORDS.CREATE_ROLE(p_role_name=>'Tickets User');

4.2 ORDS.CREATE_SERVICE

Note:

ORDS.CREATE_SERVICE is deprecated. Use ORDS.DEFINE_SERVICE instead.

Format

ORDS.CREATE_SERVICE(
   p_module_name        IN ords_modules.name%type,
   p_base_path          IN ords_modules.uri_prefix%type,
   p_pattern            IN ords_templates.uri_template%type,
   p_method             IN ords_handlers.method%type DEFAULT 'GET',
   p_source_type        IN ords_handlers.source_type%type 
                              DEFAULT ords.source_type_collection_feed,
   p_source             IN ords_handlers.source%type,
   p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
   p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
   p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
   p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
   p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
   p_module_comments    IN ords_modules.comments%type DEFAULT NULL,
   p_template_comments  IN ords_modules.comments%type DEFAULT NULL,
   p_handler_comments   IN ords_modules.comments%type DEFAULT NULL);

Description

Creates a new RESTful service.

Parameters

p_module_name

The name of the RESTful service module. Case sensitive. Must be unique.

p_base_path

The base of the URI that is used to access this RESTful service. Example: hr/ means that all URIs starting with hr/ will be serviced by this resource module.

p_pattern

A matching pattern for the resource template. For example, a pattern of /objects/:object/:id? will match /objects/emp/101 (matches a request for the item in the emp resource with id of 101) and will also match /objects/emp/ (matches a request for the emp resource, because the :id parameter is annotated with the ? or question mark modifier, which indicates that the id parameter is optional).

p_method

The HTTP method to which this handler will respond. Valid values: GET (retrieves a representation of a resource), POST (creates a new resource or adds a resource to a collection), PUT (updates an existing resource), DELETE (deletes an existing resource).

p_source_type

The HTTP request method for this handler. Valid values:

  • source_type_collection_feed. Executes a SQL query and transforms the result set into an Oracle REST Data Services Standard JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_collection_item. Executes a SQL query returning one row of data into a Oracle REST Data Services Standard JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_media. Executes a SQL query conforming to a specific format and turns the result set into a binary representation with an accompanying HTTP Content-Type header identifying the Internet media type of the representation. Result Format: Binary

  • source_type_plsql. Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. Available only when the HTTP method is DELETE, PUT, or POST. Result Format: JSON

  • source_type_query || source_type_csv_query. Executes a SQL query and transforms the result set into either an Oracle REST Data Services legacy JavaScript Object Notation (JSON) or CSV representation, depending on the format selected. Available when the HTTP method is GET. Result Format: JSON or CSV

  • source_type_query_one_row. Executes a SQL query returning one row of data into an Oracle REST Data Services legacy JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_feed. Executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. The first column in each row in the result set must be a unique identifier for the row and is used to form a hyperlink of the form: path/to/feed/{id}, with the value of the first column being used as the value for {id}. The other columns in the row are assumed to summarize the resource and are included in the feed. A separate resource template for the full representation of the resource should also be defined. Result Format: JSON

p_source

The source implementation for the selected HTTP method.

p_items_per_page

The default pagination for a resource handler HTTP operation GET method, that is, the number of rows to return on each page of a JSON format result set based on a database query. Default: NULL (defers to the resource module setting).

p_status

The publication status. Valid values: 'PUBLISHED' (default) or 'NOT_PUBLISHED'.

p_etag_type

A type of entity tag to be used by the resource template. An entity tag is an HTTP Header that acts as a version identifier for a resource. Use entity tag headers to avoid retrieving previously retrieved resources and to perform optimistic locking when updating resources. Valid values: 'HASH' or 'QUERY' or 'NONE'.

  • HASH - Known as Secure HASH: The contents of the returned resource representation are hashed using a secure digest function to provide a unique fingerprint for a given resource version.

  • QUERY - Manually define a query that uniquely identifies a resource version. A manually defined query can often generate an entity tag more efficiently than hashing the entire resource representation.

  • NONE - Do not generate an entity tag.

p_etag_query

A query that is used to generate the entity tag.

p_mimes_allowed

A comma-separated list of MIME types that the handler will accept. Applies to PUT and POST only.

p_module_comments

Comment text.

p_template_comments

Comment text.

p_handler_comments

Comment text.

Usage Notes

Creates a resource module, template, and handler in one call.

This procedure is deprecated. Use ORDS.DEFINE_SERVICE instead.

Examples

The following example creates a simple service.

BEGIN
  ORDS.CREATE_SERVICE(
    p_module_name => 'my.tickets',
    p_base_path => '/my/tickets/',
    p_pattern => '.',
    p_source => 'select t.id "$.id", t.id, t.title from tickets t' || 
                ' where t.owner = :current_user order by t.updated_on desc'
  );
END;
/

4.3 ORDS.DEFINE_HANDLER

Format

ORDS.DEFINE_HANDLER(
p_module_name      IN ords_modules.name%type,
p_pattern          IN ords_templates.uri_template%type,
p_method           IN ords_handlers.method%type DEFAULT 'GET',
p_source_type      IN ords_handlers.source_type%type
DEFAULT ords.source_type_collection_feed,
p_source           IN ords_handlers.source%type,
p_items_per_page   IN ords_handlers.items_per_page%type DEFAULT NULL,
p_mimes_allowed    IN ords_handlers.mimes_allowed%type DEFAULT NULL,
p_comments         IN ords_handlers.comments%type DEFAULT NULL);

Description

DEFINE_HANDLER defines a module handler. If the handler already exists, then the handler and any existing handlers will be replaced by this definition; otherwise, a new handler is created.

Parameters

p_module_name

Name of the owning RESTful service module. Case sensitive.

p_pattern

Matching pattern for the owning resource template.

p_method

The HTTP method to which this handler will respond. Valid values: GET (retrieves a representation of a resource), POST (creates a new resource or adds a resource to a collection), PUT (updates an existing resource), DELETE (deletes an existing resource).

p_source_type

The HTTP request method for this handler. Valid values:

  • source_type_collection_feed. Executes a SQL query and transforms the result set into an Oracle REST Data Services Standard JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_collection_item. Executes a SQL query returning one row of data into a Oracle REST Data Services Standard JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_media. Executes a SQL query conforming to a specific format and turns the result set into a binary representation with an accompanying HTTP Content-Type header identifying the Internet media type of the representation. Result Format: Binary

  • source_type_plsql. Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. Available only when the HTTP method is DELETE, PUT, or POST. Result Format: JSON

  • source_type_query || source_type_csv_query. Executes a SQL query and transforms the result set into either an Oracle REST Data Services legacy JavaScript Object Notation (JSON) or CSV representation, depending on the format selected. Available when the HTTP method is GET. Result Format: JSON or CSV

  • source_type_query_one_row. Executes a SQL query returning one row of data into an Oracle REST Data Services legacy JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_feed. Executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. The first column in each row in the result set must be a unique identifier for the row and is used to form a hyperlink of the form: path/to/feed/{id}, with the value of the first column being used as the value for {id}. The other columns in the row are assumed to summarize the resource and are included in the feed. A separate resource template for the full representation of the resource should also be defined. Result Format: JSON

p_source

The source implementation for the selected HTTP method.

p_items_per_page

The default pagination for a resource handler HTTP operation GET method, that is, the number of rows to return on each page of a JSON format result set based on a database query. Default: NULL (defers to the resource module setting).

p_mimes_allowed

Comma-separated list of MIME types that the handler will accept. Applies to PUT and POST only.

p_comments

Comment text.

Usage Notes

Only one handler for each HTTP method (source type) is permitted.

Examples

The following example defines a POST handler to the /my/tickets/ resource to accept new tickets. 

BEGIN
  ORDS.DEFINE_HANDLER(
    p_module_name => 'my.tickets',
    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 blob;
        l_id number;
      begin
        l_payload := :body;
        l_owner := :owner;
        if ( l_owner is null ) then
          l_owner := :current_user;
        end if;
        l_id := ticket_api.create_ticket(
          p_json_entity => l_payload,
          p_author => l_owner
        );
        :location := ''./'' || l_id;
        :status := 201;
      end;
      '
  );
END;
/

4.4 ORDS.DEFINE_MODULE

Format

ORDS.DEFINE_MODULE(
   p_module_name     IN ords_modules.name%type,
   p_base_path       IN ords_modules.uri_prefix%type,
   p_items_per_page  IN ords_modules.items_per_page%type DEFAULT 25,
   p_status          IN ords_modules.status%type DEFAULT 'PUBLISHED',
   p_comments        IN ords_modules.comments%type DEFAULT NULL);

Description

DEFINE_MODULE defines a resource module. If the module already exists, then the module and any existing templates will be replaced by this definition; otherwise, a new module is created.

Parameters

p_module_name

Name of the owning RESTful service module. Case sensitive.

p_base_path

The base of the URI that is used to access this RESTful service. Example: hr/ means that all URIs starting with hr/ will be serviced by this resource module.

p_items_per_page

The default pagination for a resource handler HTTP operation GET method, that is, the number of rows to return on each page of a JSON format result set based on a database query. Default: 25.

p_status

Publication status. Valid values: PUBLISHED (default) or NOT_PUBLISHED.

p_comments

Comment text.

Usage Notes

(None.)

Examples

The following example creates a simple module.

BEGIN
  ORDS.DEFINE_MODULE(
    p_module_name => 'my.tickets',
    p_base_path => '/my/tickets/'
  );
END;
/

4.5 ORDS.DEFINE_PARAMETER

Format

ORDS.DEFINE_PARAMETER(
   p_module_name        IN ords_modules.name%type,
   p_pattern            IN ords_templates.uri_template%type,
   p_method             IN ords_handlers.method%type,
   p_name               IN ords_parameters.name%type ,
   p_bind_variable_name IN ords_parameters.bind_variable_name%type 
                              DEFAULT NULL,
   p_source_type        IN ords_parameters.source_type%type DEFAULT 'HEADER',
   p_param_type         IN ords_parameters.param_type%type DEFAULT 'STRING',
   p_access_method      IN ords_parameters.access_method%type DEFAULT 'IN',
   p_comments           IN ords_parameters.comments%type DEFAULT NULL);

Description

DEFINE_PARAMETER defines a module handler parameter. If the parameter already exists, then the parameter will be replaced by this definition; otherwise, a new parameter is created.

Parameters

p_module_name

Name of the owning RESTful service module. Case sensitive.

p_pattern

Matching pattern for the owning resource template.

p_method

The owning handler HTTP Method. Valid values: GET (retrieves a representation of a resource), POST (creates a new resource or adds a resource to a collection), PUT (updates an existing resource), DELETE (deletes an existing resource).

p_name

The name of the parameter, as it is named in the URI Template or HTTP Header. Used to map names that are not valid SQL parameter names.

p_bind_variable_name

The name of the parameter, as it will be referred to in the SQL. If NULL is specified, then the parameter is unbound.

p_source_type

The type that is identified if the parameter originates in the URI Template or a HTTP Header. Valid values: HEADER, RESPONSE, URI.

p_param_type

The native type of the parameter. Valid values: STRING, INT, DOUBLE, BOOLEAN, LONG, TIMESTAMP, RESULTSET.

p_access_method

The parameter access method. Indicates if the parameter is an input value, output value, or both. Valid values: IN, OUT, INOUT.

p_comments

Comment text.

Usage Notes

All parameters must have unique names and variable names for the same handler.

Examples

The following example defines an outbound parameter on the POST handler to store the location of the created ticket.

BEGIN
  ORDS.DEFINE_PARAMETER(
    p_module_name => 'my.tickets',
    p_pattern => '.',
    p_method => 'POST',
    p_name => 'X-APEX-FORWARD',
    p_bind_variable_name => 'location',
    p_source_type => 'HEADER',
    p_access_method => 'OUT'
  );
END;
/

The following example defines an outbound parameter on the POST handler to store the HTTP status of the operation.

BEGIN
  ORDS.DEFINE_PARAMETER(
    p_module_name => 'my.tickets',
    p_pattern => '.',
    p_method => 'POST',
    p_name => 'X-APEX-STATUS-CODE',
    p_bind_variable_name => 'status',
    p_source_type => 'HEADER',
    p_access_method => 'OUT'
  );
END;
/

4.6 ORDS.DEFINE_PRIVILEGE

Format

ORDS.DEFINE_PRIVILEGE(
   p_privilege_name    IN sec_privileges.name%type,
   p_roles             IN owa.vc_arr,
   p_patterns          IN owa.vc_arr,
   p_modules           IN owa.vc_arr,
   p_label             IN sec_privileges.label%type DEFAULT NULL,
   p_description       IN sec_privileges.description%type DEFAULT NULL,
   p_comments          IN sec_privileges.comments%type DEFAULT NULL);
or
ORDS.DEFINE_PRIVILEGE(
   p_privilege_name    IN sec_privileges.name%type,
   p_roles             IN owa.vc_arr,
   p_patterns          IN owa.vc_arr,
   p_label             IN sec_privileges.label%type DEFAULT NULL,
   p_description       IN sec_privileges.description%type DEFAULT NULL,
   p_comments          IN sec_privileges.comments%type DEFAULT NULL);
or
ORDS.DEFINE_PRIVILEGE(
   p_privilege_name    IN sec_privileges.name%type,
   p_roles             IN owa.vc_arr,
   p_label             IN sec_privileges.label%type DEFAULT NULL,
   p_description       IN sec_privileges.description%type DEFAULT NULL,
   p_comments          IN sec_privileges.comments%type DEFAULT NULL);

Description

DEFINE_PRIVILEGE defines an Oracle REST Data Services privilege. If the privilege already exists, then the privilege and any existing patterns and any associations with modules and roles will be replaced by this definition; otherwise, a new privilege is created.

Parameters

p_privilege_name

Name of the privilege. No spaces allowed.

p_roles

The names of the roles, at least one of which the privilege requires. May be empty, in which case the user must be authenticated but does not require any specific role; however, must not be null. Unauthenticated users will be denied access.

p_patterns

A list of patterns.

p_modules

A list of module names referencing modules created for the current schema.

p_label

Name of this security constraint as displayed to an end user. May be null.

p_description

A brief description of the purpose of the resources protected by this constraint.

p_comments

Comment text.

Usage Notes

p_roles, p_patterns, and p_modules do not accept null values. If no value is to be passed, then either choose the appropriate procedure specification or pass an empty owa.vc_arr value.

Examples

The following example creates a privilege connected to roles, patterns, and modules:

DECLARE
  l_priv_roles owa.vc_arr;
  l_priv_patterns owa.vc_arr;
  l_priv_modules owa.vc_arr;
BEGIN
  l_priv_roles(1) := 'Tickets User';
  l_priv_patterns(1) := '/my/*';
  l_priv_patterns(2) := '/comments/*';
  l_priv_patterns(3) := '/tickets_feed/*';
  l_priv_patterns(4) := '/tickets/*';
  l_priv_patterns(5) := '/categories/*';
  l_priv_patterns(6) := '/stats/*';

  l_priv_modules(1) := 'my.tickets';

  ords.create_role('Tickets User');

  ords.define_privilege(
    p_privilege_name     => 'tickets.privilege',
    p_roles              => l_priv_roles,
    p_patterns           => l_priv_patterns,
    P_modules            => l_priv_modules,
    p_label              => 'Task Ticketing Access',
    p_description        => 'Provides the ability to create, ' || 
                            'update and delete tickets ' || 
                            'and post comments on tickets'
  );
END;
/

The following example creates a privilege connected to roles and patterns:

DECLARE
  l_priv_roles owa.vc_arr;
  l_priv_patterns owa.vc_arr;
BEGIN
  l_priv_roles(1) := 'Tickets User';
  l_priv_patterns(1) := '/my/*';
  l_priv_patterns(2) := '/comments/*';
  l_priv_patterns(3) := '/tickets_feed/*';
  l_priv_patterns(4) := '/tickets/*';
  l_priv_patterns(5) := '/categories/*';
  l_priv_patterns(6) := '/stats/*';

  ords.create_role('Tickets User');

  ords.define_privilege(
    p_privilege_name     => 'tickets.privilege',
    p_roles              => l_priv_roles,
    p_patterns           => l_priv_patterns,
    p_label              => 'Task Ticketing Access',
    p_description        => 'Provides the ability to create, ' || 
                            'update and delete tickets ' || 
                            'and post comments on tickets'
  );
END;
/

The following example creates a privilege connected to roles:

DECLARE
  l_priv_roles owa.vc_arr;
BEGIN
  l_priv_roles(1) := 'Tickets User';

  ords.create_role('Tickets User');

  ords.define_privilege(
    p_privilege_name     => 'tickets.privilege',
    p_roles              => l_priv_roles,
    p_label              => 'Task Ticketing Access',
    p_description        => 'Provides the ability to create, ' || 
                            'update and delete tickets ' || 
                            'and post comments on tickets'
  );
END;
/

4.7 ORDS.DEFINE_SERVICE

Format

ORDS.DEFINE_SERVICE(
   p_module_name        IN ords_modules.name%type,
   p_base_path          IN ords_modules.uri_prefix%type,
   p_pattern            IN ords_templates.uri_template%type,
   p_method             IN ords_handlers.method%type DEFAULT 'GET',
   p_source_type        IN ords_handlers.source_type%type 
                             DEFAULT ords.source_type_collection_feed,
   p_source             IN ords_handlers.source%type,
   p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
   p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
   p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
   p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
   p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
   p_module_comments    IN ords_modules.comments%type DEFAULT NULL,
   p_template_comments  IN ords_modules.comments%type DEFAULT NULL,
   p_handler_comments   IN ords_modules.comments%type DEFAULT NULL);

Description

DEFINE_SERVICE defines a resource module, template, and handler in one call. If the module already exists, then the module and any existing templates will be replaced by this definition; otherwise, a new module is created.

Parameters

p_module_name

Name of the RESTful service module. Case sensitive. Must be unique.

p_base_path

The base of the URI that is used to access this RESTful service. Example: hr/ means that all URIs starting with hr/ will be serviced by this resource module.

p_pattern

A matching pattern for the resource template. For example, a pattern of /objects/:object/:id? will match /objects/emp/101 (matches a request for the item in the emp resource with id of 101) and will also match /objects/emp/. (Matches a request for the emp resource, because the :id parameter is annotated with the ? modifier, which indicates that the id parameter is optional.)

p_method

The HTTP Method to which this handler will respond. Valid values: GET (retrieves a representation of a resource), POST (creates a new resource or adds a resource to a collection), PUT (updates an existing resource), DELETE (deletes an existing resource).

p_source_type

The HTTP request method for this handler. Valid values:

  • source_type_collection_feed. Executes a SQL query and transforms the result set into an Oracle REST Data Services Standard JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_collection_item. Executes a SQL query returning one row of data into a Oracle REST Data Services Standard JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_media. Executes a SQL query conforming to a specific format and turns the result set into a binary representation with an accompanying HTTP Content-Type header identifying the Internet media type of the representation. Result Format: Binary

  • source_type_plsql. Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. Available only when the HTTP method is DELETE, PUT, or POST. Result Format: JSON

  • source_type_query || source_type_csv_query. Executes a SQL query and transforms the result set into either an Oracle REST Data Services legacy JavaScript Object Notation (JSON) or CSV representation, depending on the format selected. Available when the HTTP method is GET. Result Format: JSON or CSV

  • source_type_query_one_row. Executes a SQL query returning one row of data into an Oracle REST Data Services legacy JSON representation. Available when the HTTP method is GET. Result Format: JSON

  • source_type_feed. Executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. The first column in each row in the result set must be a unique identifier for the row and is used to form a hyperlink of the form: path/to/feed/{id}, with the value of the first column being used as the value for {id}. The other columns in the row are assumed to summarize the resource and are included in the feed. A separate resource template for the full representation of the resource should also be defined. Result Format: JSON

p_source

The source implementation for the selected HTTP method.

p_items_per_page

The default pagination for a resource handler HTTP operation GET method, that is, the number of rows to return on each page of a JSON format result set based on a database query. Default: NULL (defers to the resource module setting).

p_status

Publication status. Valid values: PUBLISHED (default) or NOT_PUBLISHED.

p_etag_type

A type of entity tag to be used by the resource template. An entity tag is an HTTP Header that acts as a version identifier for a resource. Use entity tag headers to avoid retrieving previously retrieved resources and to perform optimistic locking when updating resources. Valid values are HASH, QUERY, NONE:

  • HASH (known as Secure HASH): The contents of the returned resource representation are hashed using a secure digest function to provide a unique fingerprint for a given resource version.

  • QUERY: Manually define a query that uniquely identifies a resource version. A manually defined query can often generate an entity tag more efficiently than hashing the entire resource representation.

  • NONE: Do not generate an entity tag.

p_etag_query

Query that is used to generate the entity tag.

p_mimes_allowed

Comma-separated list of MIME types that the handler will accept. Applies to PUT and POST only.

p_module_comments

Comment text.

p_template_comments

Comment text.

p_handler_comments

Comment text.

Usage Notes

Creates a resource module, template, and handler in one call.

Use this procedure instead of the deprecated ORDS.CREATE_SERVICE procedure.

Examples

The following example defines a REST service that retrieves the current user's tickets.

BEGIN
  ORDS.DEFINE_SERVICE(
    p_module_name => 'my.tickets',
    p_base_path => '/my/tickets/',
    p_pattern => '.',
    p_source => 'select t.id "$.id", t.id, t.title from tickets t' || 
                ' where t.owner = :current_user order by t.updated_on desc'
  );
END;
/

The following example defines a REST service that retrieves tickets filtered by category.

BEGIN
  ORDS.DEFINE_SERVICE(
    p_module_name => 'by.category',
    p_base_path => '/by/category/',
    p_pattern => ':category_id',
    p_source => 'select ''../../my/tickets/'' || 
                  t.id "$.id", t.id, t.title' || 
                ' from tickets t, categories c, ticket_categories tc' || 
                ' where c.id = :category_id and c.id = tc.category_id and' || 
                ' tc.ticket_id = t.id order by t.updated_on desc'
  );
END;
/

4.8 ORDS.DEFINE_TEMPLATE

Format

ORDS.DEFINE_TEMPLATE(
   p_module_name  IN ords_modules.name%type,
   p_pattern      IN ords_templates.uri_template%type,
   p_priority     IN ords_templates.priority%type DEFAULT 0,
   p_etag_type    IN ords_templates.etag_type%type DEFAULT 'HASH',
   p_etag_query   IN ords_templates.etag_query%type DEFAULT NULL,
   p_comments     IN ords_templates.comments%type DEFAULT NULL);

Description

DEFINE_TEMPLATE defines a resource template. If the template already exists, then the template and any existing handlers will be replaced by this definition; otherwise, a new template is created.

Parameters

p_module_name

Name of the owning RESTful service module. Case sensitive.

p_pattern

A matching pattern for the resource template. For example, a pattern of /objects/:object/:id? will match /objects/emp/101 (matches a request for the item in the emp resource with id of 101) and will also match /objects/emp/. (Matches a request for the emp resource, because the :id parameter is annotated with the ? modifier, which indicates that the id parameter is optional.)

p_priority

The priority for the order of how the resource template should be evaluated: 0 (low priority. the default) through 9 (high priority).

p_etag_type

A type of entity tag to be used by the resource template. An entity tag is an HTTP Header that acts as a version identifier for a resource. Use entity tag headers to avoid retrieving previously retrieved resources and to perform optimistic locking when updating resources. Valid values are HASH, QUERY, NONE:

  • HASH (known as Secure HASH): The contents of the returned resource representation are hashed using a secure digest function to provide a unique fingerprint for a given resource version.

  • QUERY: Manually define a query that uniquely identifies a resource version. A manually defined query can often generate an entity tag more efficiently than hashing the entire resource representation.

  • NONE: Do not generate an entity tag.

p_etag_query

Query that is used to generate the entity tag.

p_comments

Comment text.

Usage Notes

he resource template pattern must be unique with a resource module.

Examples

The following example defines a resource for displaying ticket items.

BEGIN
  ORDS.DEFINE_TEMPLATE(
    p_module_name => 'my.tickets',
    p_pattern => '/:id'
  );
END;
/

4.9 ORDS.DELETE_MODULE

Format

ORDS.DELETE_MODULE(
   p_module_name  IN ords_modules.name%type);

Description

DELETE_MODULE deletes a resource module.

Parameters

p_module_name

Name of the owning RESTful service module. Case sensitive.

Usage Notes

If the module does not already exist or is accessible to the current user, then no exception is raised.

Examples

The following example deletes a resource module.

EXECUTE ORDS.DELETE_MODULE(p_module_name=>'my.tickets');

4.10 ORDS.DELETE_PRIVILEGE

Format

ORDS.DELETE_PRIVILEGE(
   p_name  IN sec_privileges.name%type);

Description

DELETE_PRIVILEGE deletes a provilege.

Parameters

p_name

Name of the privilege.

Usage Notes

If the privilege does not already exist or is not accessible to the current user, then no exception is raised.

Examples

The following example deletes a privilege.

EXECUTE ORDS.DELETE_PRIVILEGE(p_name=>'tickets.privilege');

4.11 ORDS.DELETE_ROLE

Format

ORDS.DELETE_ROLE(
   p_role_name IN sec_roles.name%type);

Description

DELETE_ROLE deletes the named role.

Parameters

p_name

Name of the role.

Usage Notes

This will also delete any association between the role and any privileges that reference the role.

No exception is produced if the role does not already exist.

Examples

The following example deletes a role.

EXECUTE ORDS.DELETE_ROLE(p_role_name=>'Tickets User');

4.12 ORDS.DROP_REST_FOR_SCHEMA

Format

ORDS.DROP_REST_FOR_SCHEMA(
   p_schema ords_schemas.parsing_schema%type DEFAULT NULL);

Description

DROP_REST_FOR_SCHEMA deletes all auto-REST Oracle REST Data Services metadata for the associated schema.

Parameters

p_schema

Name of the schema.

Usage Notes

This procedure effectively "undoes" the actions performed by the ORDS.Enable_Schema procedure.

Examples

The following example deletes all auto-REST Oracle REST Data Services metadata for the TICKETS schema.

EXECUTE ORDS.DROP_REST_FOR_SCHEMA('tickets');

Related Topics

4.13 ORDS.ENABLE_OBJECT

Format

ORDS.ENABLE_OBJECT(
   p_enabled         IN boolean DEFAULT TRUE,
   p_schema          IN ords_schemas.parsing_schema%type DEFAULT NULL,
   p_object          IN ords_objects.parsing_object%type,
   p_object_type     IN ords_objects.type%type DEFAULT 'TABLE',
   p_object_alias    IN ords_objects.object_alias%type DEFAULT NULL,
   p_auto_rest_auth  IN boolean DEFAULT NULL);

Description

ENABLE_OBJECT enables Oracle REST Data Services access to a specified function, materialized view, package, procedure, table, or view in a schema.

Parameters

p_enabled

TRUE to enable access; FALSE to disable access.

p_schema

Name of the schema for the table or view.

p_object

Name of the table or view.

p_object_type

Type of the object. Valid values: FUNCTION, MVIEW, PACKAGE, PROCEDURE, TABLE (default), or VIEW.

p_object_alias

Alias of the object.

p_auto_rest_auth

Controls whether Oracle REST Data Services should require user authorization before allowing access to the Oracle REST Data Services metadata for this object. If this value is TRUE, then the service is protected by the following roles:

  • oracle.dbtools.autorest.any.schema

  • oracle.dbtools.role.autorest.<SCHEMANAME>.<OBJECTNAME>

Usage Notes

Only database users with the DBA role can enable/access to objects that they do now own.

Examples

The following example enables a table named CATEGORIES.

EXECUTE ORDS.ENABLE_OBJECT(p_object=>'CATEGORIES');

The following example enables a view named TICKETS_FEED.

BEGIN
  ORDS.ENABLE_OBJECT(
    p_object => 'TICKETS_FEED',
    p_object_type => 'VIEW'
  );
END;
/

4.14 ORDS.DROP_REST_FOR_OBJECT

Format

ORDS.DROP_REST_FOR_OBJECT(
   p_object ords_objects.parsing_object%type);

Description

DROP_REST_FOR_OBJECT deletes all auto-REST Oracle REST Data Services metadata for the associated schema object.

Parameters

p_object

Name of the table or view.

Usage Notes

This procedure effectively "undoes" the actions performed by the ORDS.ENABLE_OBJECT procedure.

Examples

The following example deletes all auto-REST Oracle REST Data Services metadata for the curent user CATEGORIES table.

BEGIN
  ORDS.DROP_REST_FOR_OBJECT(
    p_object=>'CATEGORIES'
  );
END;
/

4.15 ORDS.ENABLE_SCHEMA

Format

ORDS.ENABLE_SCHEMA(
   p_enabled             IN boolean DEFAULT TRUE,
   p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
   p_url_mapping_type    IN ords_url_mappings.type%type DEFAULT 'BASE_PATH',
   p_url_mapping_pattern IN ords_url_mappings.pattern%type DEFAULT NULL,
   p_auto_rest_auth      IN boolean DEFAULT NULL);

Description

ENABLE_SCHEMA enables Oracle REST Data Services to access the named schema.

Parameters

p_enabled

TRUE to enable Oracle REST Data Services access; FALSE to disable Oracle REST Data Services access.

p_schema

Name of the schema. If the p_schema parameter is omitted, then the current schema is enabled.

p_url_mapping_type

URL Mapping type: BASE_PATH or BASE_URL.

p_url_mapping_pattern

URL mapping pattern.

p_auto_rest_auth

For a schema, controls whether Oracle REST Data Services should require user authorization before allowing access to the Oracle REST Data Services metadata catalog of this schema.

Usage Notes

Only database users with the DBA role can enable or disable a schema other than their own.

Examples

The following example enables the current schema.

EXECUTE ORDS.ENABLE_SCHEMA;

4.16 ORDS.PUBLISH_MODULE

Format

ORDS.PUBLISH_MODULE(
   p_module_name  IN ords_modules.name%type,
   p_status       IN ords_modules.status%type DEFAULT 'PUBLISHED');

Description

PUBLISH_MODULE changes the publication status of an Oracle REST Data Services resource module.

Parameters

p_module_name

Current name of the RESTful service module. Case sensitive.

p_status

Publication status. Valid values: PUBLISHED (default) or NOT_PUBLISHED.

Usage Notes

(None.)

Examples

The following example publishes a previously defined module named my.tickets. 

EXECUTE ORDS.PUBLISH_MODULE(p_module_name=>'my.tickets');

4.17 ORDS.RENAME_MODULE

Format

ORDS.RENAME_MODULE(
   p_module_name    IN ords_modules.name%type,
   p_new_name       IN ords_modules.name%type DEFAULT NULL,
   p_new_base_path  IN ords_modules.uri_prefix%type DEFAULT NULL);

Description

RENAME_MODULE lets you change the name or the base path, or both, of an Oracle REST Data Services resource module.

Parameters

p_module_name

Current name of the RESTful service module. Case sensitive.

p_new_name

New name to be assigned to the RESTful service module. Case sensitive. If this parameter is null, the name is not changed.

p_new_base_path

The base of the URI to be used to access this RESTful service. Example: hr/ means that all URIs starting with hr/ will be serviced by this resource module. If this parameter is null, the base path is not changed.

Usage Notes

Both the new resource module name and the base path must be unique within the enabled schema.

Examples

The following example renames resource module my.tickets to old.tickets. 

BEGIN
  ORDS.RENAME_MODULE(
    p_module_name =>'my.tickets',
    p_new_name=>'old.tickets',
    p_new_base_path=>'/old/tickets/');
END;
/

4.18 ORDS.RENAME_PRIVILEGE

Format

ORDS.RENAME_PRIVILEGE(
   p_name      IN sec_privileges.name%type,
   p_new_name  IN sec_privileges.name%type);

Description

RENAME_PRIVILEGE renames a privilege.

Parameters

p_name

Current name of the privilege.

p_new_name

New name to be assigned to the privilege.

Usage Notes

(None.)

Examples

The following example renames the privilege tickets.privilege to old.tickets.privilege. 

BEGIN
  ORDS.RENAME_PRIVILEGE(
    p_name =>'tickets.privilege',
    p_new_name=>'old.tickets.privilege');
END;
/

4.19 ORDS.RENAME_ROLE

Format

ORDS.RENAME_ROLE(
   p_role_name  IN sec_roles.name%type,
   p_new_name   IN sec_roles.name%type);

Description

RENAME_ROLE renames a role.

Parameters

p_role_name

Current name of the role.

p_new_name

New name to be assigned to the role.

Usage Notes

p_role_name must exist.

Examples

The following example renames an existing role.

BEGIN
  ORDS.RENAME_ROLE(
    p_role_name=>'Tickets User',
    p_new_name=>'Legacy Tickets User');
END;
/

4.20 ORDS.SET_MODULE_ORIGINS_ALLOWED

Format

ORDS.SET_MODULE_ORIGINS_ALLOWED(
   p_module_name     IN ords_modules.name%type,
   p_origins_allowed IN sec_origins_allowed_modules.origins_allowed%type);

Description

SET_MODULE_ORIGINS_ALLOWED configures the allowed origins for a resource module. Any existing allowed origins will be replaced.

Parameters

p_module_name

Name of the resource module.

p_origins_allowed

A comma-separated list of URL prefixes. If the list is empty, any existing origins are removed.

Usage Notes

To indicate no allowed origins for a resource module (and remove any existing allowed origins), specify an empty p_origins_allowed value.

Examples

The following restricts the resource module my.tickets to two specified origins. 

BEGIN 
  ORDS.SET_MODULE_ORIGINS_ALLOWED(
    p_module_name     => 'my.tickets',
    p_origins_allowed => 'http://example.com,https://example.com');
END;
/

4.21 ORDS.SET_URL_MAPPING

Format

ORDS.SET_URL_MAPPING(
   p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
   p_url_mapping_type    IN ords_url_mappings.type%type,
   p_url_mapping_pattern IN ords_url_mappings.pattern%type);

Description

SET_URL_MAPPING configures how the specified schema is mapped to request URLs.

Parameters

p_schema

Name of the schema to map. The default is the schema of the current user.

p_url_mapping_type

URL Mapping type: BASE_PATH or BASE_URL.

p_url_mapping_pattern

URL mapping pattern.

Usage Notes

Only DBA users can update the mapping of a schema other than their own.

Examples

The following example creates a BASE_PATH mapping for the current user. 

BEGIN
  ORDS.SET_URL_MAPPING(
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'https://example.com/ords/ticketing'
  );
END;
/

4.22 ORDS.SET_SESSION_DEFAULTS

Format

ORDS.SET_SESSION_DEFAULTS(
       p_runtime_user IN varchar2);

Description

Set defaults that apply for the duration of the database session.

Parameters

p_schema

Name of the schema to map. The default is the schema of the current user.

p_runtime_user

Sets a runtime user as the target when you REST enable or disable the schemas. Otherwise all runtime users are targeted.

Usage Notes

NULL values have no effect. Use RESET_SESSION_DEFAULTS to reset values and start again.

Examples

The following example sets the HR user as the only grantee target for the “connect through” proxy privilege when a schema is REST enabled or disabled: 

BEGIN
  ORDS.SET_SESSION_DEFAULTS(
    p_runtime_user => 'HR');
END;
/

4.23 ORDS.RESET_SESSION_DEFAULTS

Format

ORDS.RESET_SESSION_DEFAULTS;

Description

Reset session defaults back to the initial values.

Parameters

None.

Usage Notes

Use the SET_SESSION_DEFAULTS function to set the default values that are reset using this function.

Examples

The following example resets all the session default values:

BEGIN 
      ORDS.RESET_SESSION_DEFAULTS;
END;
/

4.24 ORDS.SET_PROPERTY

Format

ORDS.SET_PROPERTY(
      p_key             IN ords_prop_facts.key%type,
      p_value           IN ords_prop_values.value%type);

Description

SET_PROPERTY sets the value of the SCHEMA scoped property for the current enabled schema. The value must not be NULL.

Parameters

p_key

The property key.

p_value

The new property value.

Examples

The following example sets a property value:

BEGIN
  ORDS.SET_PROPERTY(
     p_key => 'a.key',
     p_value => 'a value');
END;
/

4.25 ORDS.UNSET_PROPERTY

Format

ORDS.UNSET_PROPERTY(
         p_key  IN ords_prop_facts.key%type);

Description

UNSET_PROPERTY unsets the value of the SCHEMA scoped property for the current enabled schema.

Parameters

p_key

The property key.

Examples

The following example unsets a property value:

BEGIN
  ORDS.UNSET_PROPERTY(
    p_key => 'a.key');
END;
/