Oracle REST Data Services Administration PL/SQL Package Reference

ORDS_ADMIN.CREATE_ROLE

Format

ORDS_ADMIN.CREATE_ROLE(
   p_schema    IN ords_schemas.parsing_schema%type,
   p_role_name IN sec_roles.name%type);

Description

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

Parameters

p_schema: Name of the schema. This parameter is mandatory.

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.

BEGIN
  ORDS_ADMIN.CREATE_ROLE(
    p_schema => 'tickets',
    p_role_name => 'Tickets User'
  };
END;
/

ORDS_ADMIN.DEFINE_HANDLER

Format

ORDS_ADMIN.DEFINE_HANDLER(
p_schema           IN ords_schemas.parsing_schema%type,
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_admin.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_schema

Name of the schema. This parameter is mandatory.

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_ADMIN.DEFINE_HANDLER(
    p_schema => 'tickets',
    p_module_name => 'my.tickets',
    p_pattern => '.',
    p_method  => 'POST',
    p_mimes_allowed => 'application/json',
    p_source_type => ords_admin.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;
/

ORDS_ADMIN.DEFINE_MODULE

Format

ORDS_ADMIN.DEFINE_MODULE(
   p_schema          IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.
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_ADMIN.DEFINE_MODULE(
    p_schema => 'tickets',
    p_module_name => 'my.tickets',
    p_base_path => '/my/tickets/'
  );
END;
/

ORDS_ADMIN.DEFINE_PARAMETER

Format

ORDS_ADMIN.DEFINE_PARAMETER(
   p_schema             IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.
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.
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_ADMIN.DEFINE_PARAMETER(
    p_schema => 'tickets',
    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_ADMIN.DEFINE_PARAMETER(
    p_schema => 'tickets',
    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;
/

ORDS_ADMIN.DEFINE_PRIVILEGE

Format

ORDS_ADMIN.DEFINE_PRIVILEGE(
   p_schema            IN ords_schemas.parsing_schema%type,
   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_ADMIN.DEFINE_PRIVILEGE(
   p_schema            IN ords_schemas.parsing_schema%type,
   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_ADMIN.DEFINE_PRIVILEGE(
   p_schema            IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.
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_admin.create_role(
    p_schema    => 'tickets',
    p_role_name => 'Tickets User'
  };

  ords_admin.define_privilege(
    p_schema             => 'tickets',
    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_admin.create_role(
    p_schema    => 'tickets',
    p_role_name => 'Tickets User'
  };

  ords_admin.define_privilege(
    p_schema             => 'tickets',
    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_admin.create_role(
    p_schema    => 'tickets',
    p_role_name => 'Tickets User'
  };

  ords_admin.define_privilege(
    p_schema             => 'tickets',
    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;
/

ORDS_ADMIN.DEFINE_SERVICE

Format

ORDS_ADMIN.DEFINE_SERVICE(
   p_schema             IN ords_schemas.parsing_schema%type,
   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_admin.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_schema

Name of the schema. This parameter is mandatory.

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.

Examples

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

BEGIN
  ORDS_ADMIN.DEFINE_SERVICE(
    p_schema => 'tickets',
    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_ADMIN.DEFINE_SERVICE(
    p_schema => 'tickets',
    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;
/

ORDS_ADMIN.DEFINE_TEMPLATE

Format

ORDS_ADMIN.DEFINE_TEMPLATE(
   p_schema       IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.

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_ADMIN.DEFINE_TEMPLATE(
    p_schema => 'tickets',
    p_module_name => 'my.tickets',
    p_pattern => '/:id'
  );
END;
/

ORDS_ADMIN.DELETE_MODULE

Format

ORDS_ADMIN.DELETE_MODULE(
   p_schema       IN ords_schemas.parsing_schema%type, 
   p_module_name  IN ords_modules.name%type);

Description

DELETE_MODULE deletes a resource module.

Parameters

p_schema: Name of the schema. This parameter is mandatory.
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.

BEGIN
  ORDS_ADMIN.DELETE_MODULE(
    p_schema => 'tickets',
    p_module_name => 'my.tickets'
  );
END;
/

ORDS_ADMIN.DELETE_PRIVILEGE

Description

DELETE_PRIVILEGE deletes a privilege.

Parameters

p_schema: Name of the schema. This parameter is mandatory.
p_name: Name of the privilege.

Usage Notes

If the privilege does not already exist, then no exception is raised.

Examples

The following example deletes a privilege.

BEGIN
  ORDS_ADMIN.DELETE_PRIVILEGE(
    p_schema => 'tickets',
    p_name => 'tickets.privilege'
  );
END;
/

ORDS_ADMIN.DELETE_ROLE

Format

ORDS_ADMIN.DELETE_ROLE(
   p_schema    IN ords_schemas.parsing_schema%type,
   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.

BEGIN
  ORDS_ADMIN.DELETE_ROLE(
    p_schema => 'tickets',
    p_role_name => 'Tickets User'
  );
END;
/

ORDS_ADMIN.DROP_REST_FOR_SCHEMA

Format

ORDS_ADMIN.DROP_REST_FOR_SCHEMA(
   p_schema ords_schemas.parsing_schema%type);

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.

BEGIN
  ORDS_ADMIN.DROP_REST_FOR_SCHEMA(
    p_schema => 'tickets'
  );
END;
/

ORDS_ADMIN.ENABLE_OBJECT

Format

ORDS_ADMIN.ENABLE_OBJECT(
   p_enabled         IN boolean DEFAULT TRUE,
   p_schema          IN ords_schemas.parsing_schema%,
   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. This parameter is mandatory.

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

None.

Examples

The following example enables a table named CATEGORIES.

BEGIN
  ORDS_ADMIN.ENABLE_OBJECT(
    p_schema => 'tickets',
    p_object=>'CATEGORIES'
  );
END;
/

The following example enables a view named TICKETS_FEED.

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

ORDS_ADMIN.DROP_REST_FOR_OBJECT

Format

ORDS_ADMIN.DROP_REST_FOR_OBJECT(
   p_schema          IN ords_schemas.parsing_schema%,
   p_object          IN 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_schema: Name of the schema.
p_object: Name of the table or view.

Usage Notes

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

Examples

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

BEGIN
  ORDS_ADMIN.DROP_REST_FOR_OBJECT(
    p_schema => 'tickets',
    p_object=>'CATEGORIES'
  );
END;
/

ORDS_ADMIN.ENABLE_SCHEMA

Format

ORDS_ADMIN.ENABLE_SCHEMA
   p_enabled             IN boolean DEFAULT TRUE,
   p_schema              IN ords_schemas.parsing_schema%type,
   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. This parameter is mandatory.
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

None.

Examples

The following example enables the current schema.

BEGIN
  ORDS_ADMIN.ENABLE_SCHEMA(
    p_schema => 'tickets'
  );
END;
/

ORDS_ADMIN.PUBLISH_MODULE

Format

ORDS_ADMIN.PUBLISH_MODULE(
   p_schema       IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.

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.

BEGIN
  ORDS_ADMIN.PUBLISH_MODULE(
    p_schema => 'tickets',
    p_module_name => 'my.tickets'
  );
END;
/

ORDS_ADMIN.RENAME_MODULE

Format

ORDS_ADMIN.RENAME_MODULE(
   p_schema         IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.

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_ADMIN.RENAME_MODULE(
    p_schema => 'tickets',
    p_module_name =>'my.tickets',
    p_new_name=>'old.tickets',
    p_new_base_path=>'/old/tickets/');
END;
/

ORDS_ADMIN.RENAME_PRIVILEGE

Format

ORDS_ADMIN.RENAME_PRIVILEGE(
   p_schema    IN ords_schemas.parsing_schema%type,
   p_name      IN sec_privileges.name%type,
   p_new_name  IN sec_privileges.name%type);

Description

RENAME_PRIVILEGE renames a privilege.

Parameters

p_schema: Name of the schema. This parameter is mandatory.

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_ADMIN.RENAME_PRIVILEGE(
    p_schema => 'tickets',
    p_name =>'tickets.privilege',
    p_new_name=>'old.tickets.privilege');
END;
/

ORDS_ADMIN.RENAME_ROLE

Format

ORDS_ADMIN.RENAME_ROLE(
   p_schema     IN ords_schemas.parsing_schema%type,
   p_role_name  IN sec_roles.name%type,
   p_new_name   IN sec_roles.name%type);

Description

RENAME_ROLE renames a role.

Parameters

p_schema: Name of the schema. This parameter is mandatory.

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_ADMIN.RENAME_ROLE(
    p_schema=>'tickets',
    p_role_name=>'Tickets User',
    p_new_name=>'Legacy Tickets User');
END;
/

ORDS_ADMIN.SET_MODULE_ORIGINS_ALLOWED

Format

ORDS_ADMIN.SET_MODULE_ORIGINS_ALLOWED
   p_schema          IN ords_schemas.parsing_schema%type,
   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_schema: Name of the schema. This parameter is mandatory.

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_ADMIN.SET_MODULE_ORIGINS_ALLOWED(
    p_schema          => 'tickets',
    p_module_name     => 'my.tickets',
    p_origins_allowed => 'http://example.com,https://example.com');
END;
/

ORDS_ADMIN.SET_URL_MAPPING

Format

ORDS_ADMIN.SET_URL_MAPPING
   p_schema              IN ords_schemas.parsing_schema%,
   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. This parameter is mandatory.
p_url_mapping_type: URL Mapping type: BASE_PATH or BASE_URL.
p_url_mapping_pattern: URL mapping pattern.

Usage Notes

(None.)

Examples

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

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

ORDS_ADMIN.ENABLE_HOUSEKEEPING_JOB

Format

ORDS_ADMIN.ENABLE_HOUSEKEEPING_JOB(p_enabled IN boolean DEFAULT TRUE);

Description

ENABLE_HOUSEKEEPING_JOB creates and enables or disables the ORDS DBMS_SCHEDULER housekeeping job. The job name is ORDS_HOUSEKEEPING_JOB which replaces the deprecated job, CLEAN_OLD_ORDS_SESSIONS.

Parameters

p_enabled: TRUE to enable ORDS HOUSEKEEPING_JOB; FALSE to disable it. A NULL value will create and enable the job if it does not already exist otherwise its enablement state will remain changed.

Usage Notes

The job runs every hour and performs housekeeping actions on the ORDS metadata repository. No commit is required.

Examples

The following example enables the housekeeping job:

EXECUTE ORDS_ADMIN.ENABLE_HOUSEKEEPING_JOB;

ORDS_ADMIN.DROP_HOUSEKEEPING_JOB

Format

ORDS_ADMIN.DROP_HOUSEKEEPING_JOB;

Description

DROP_HOUSEKEEPING_JOB drops the ORDS DBMS_SCHEDULER housekeeping job. The job name is ORDS_HOUSEKEEPING_JOB.

Parameters

None.

Usage Notes

No commit is required.

Examples

The following example drops the housekeeping job:

EXECUTE ORDS_ADMIN.DROP_HOUSEKEEPING_JOB;

ORDS_ADMIN.PERFORM_HOUSEKEEPING

Format

ORDS_ADMIN.PERFORM_HOUSEKEEPING;

Description

PERFORM_HOUSEKEEPING performs ORDS housekeeping actions immediately. The following action is performed:

Removes expired sessions that are older than one day.

Parameters

None.

Usage Notes

No commit is required.

Examples

The following example performs the housekeeping actions immediately against the ORDS metadata repository:

EXECUTE ORDS_ADMIN.PERFORM_HOUSEKEEPING;

ORDS_ADMIN.SET_SESSION_DEFAULTS

Format

ORDS_ADMIN.SET_SESSION_DEFAULTS(
       p_runtime_user IN varchar2);

Description

Sets the default values that apply for the duration of the database session.

Parameters

p_runtime_user: Sets a runtime user as the target while REST enabling or disabling the schemas. Otherwise all runtime users are targeted.

Usage Notes

NULL values have no effect. Use RESET_SESSION_DEFAULTS function to reset the 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_ADMIN.SET_SESSION_DEFAULTS(
    p_runtime_user => 'HR');
END;
/

ORDS_ADMIN.RESET_SESSION_DEFAULTS

Format

ORDS_ADMIN.RESET_SESSION_DEFAULTS

Description

Resets the session defaults back to the initial values.

Parameters

None.

Usage Notes

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

Examples

The following example resets all the session default values:

BEGIN
  ORDS_ADMIN.RESET_SESSION_DEFAULTS;
END;
/

ORDS_ADMIN.PROVISION_ADMIN_ROLE

Format

ORDS_ADMIN.PROVISION_ADMIN_ROLE(
       p_ user IN varchar2);

Description

Provision a database user with the ORDS Administrator role so that it can administer ORDS.

Parameters

p_user: The name of the user to be provisioned.

Usage Notes

User ORDS_PUBLIC_USER cannot be configured using this interface.

Examples

The following example provisions the ORDS administrator role to the HR user:

BEGIN
  ORDS_ADMIN.PROVISION_ADMIN_ROLE(
  p_user => 'HR'
  );
END;
/

ORDS_ADMIN.PROVISION_RUNTIME_ROLE

Format

ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
       p_user                  IN  varchar2,
       p_proxy_enabled_schemas IN  boolean DEFAULT TRUE);

Description

Provision a database user so that it can act as an ORDS runtime user.

Parameters

p_user: The name of the user to be provisioned.

p_proxy_enabled_schemas: When the value is set to TRUE, “connect through” proxy grants are added for any enabled schemas.

Usage Notes

ORDS_PUBLIC_USER is an example of a runtime user. Additional changes to the ORDS configuration are required to use a user other than the ORDS_PUBLIC_USER.

Examples

The following example provisions the ORDS runtime role to the HR user and grants it the “connect through” proxy privilege for all the enabled schemas:

BEGIN 
  ORDS_ADMIN.PROVISION_RUNTIME_ROLE(    
   p_user => 'HR',    
   p_proxy_enabled_schemas => TRUE
 );
END;
/

ORDS_ADMIN.UNPROVISION_ ROLES

Format

ORDS_ADMIN.UNPROVISION_ROLES(
       p_user               IN  varchar2,
       p_administrator_role IN  boolean DEFAULT NULL,
       p_runtime_role       IN  boolean DEFAULT NULL);

Description

Unprovision the ORDS database roles.

Parameters

p_user: The name of the user to be unprovisioned.

p_administrator_role: Unprovision as an admin user.

p_runtime_role: Unprovision as a runtime user.

Usage Notes

NULL boolean values are evaluated to TRUE unless any value is set to TRUE. In such case, NULL values are evaluated to FALSE. So, by default all the roles are unprovisioned unless an explicit choice is made.

Examples

The following example unprovisions the ORDS administrator role from the HR user:

BEGIN
  ORDS_ADMIN.UNPROVISION_ROLES (
    p_user => 'HR',
    p_administrator_role => TRUE);
END;
/