Oracle REST Data Services Administration PL/SQL Package Reference

The Oracle REST Data Services (ORDS) ADMIN PL/SQL package contains subprograms (procedures and functions) for developing and administering the RESTful services using Oracle REST Data Services for a privileged user.

Before a database user can invoke the ORDS_ADMIN package, they must be granted the ORDS_ADMINISTRATOR_ROLE database role.

The following example grants the ORDS_ADMINISTRATOR_ROLE role to the ADMIN user:

GRANT ORDS_ADMINISTRATOR_ROLE TO ADMIN;

The ORDS_ADMIN package is identical to the ORDS package except for the AUTHID CURRENT_USER right, without the deprecated methods and a p_schema parameter for every method where the target schema must be specified and some additional methods.

• ORDS_ADMIN.CREATE_ROLE

• ORDS_ADMIN.DEFINE_HANDLER

• ORDS_ADMIN.DELETE_HANDLER

• ORDS_ADMIN.DELETE_ALL_HANDLERS

• ORDS_ADMIN.DEFINE_MODULE

• ORDS_ADMIN.DEFINE_PARAMETER

• ORDS_ADMIN.DEFINE_PRIVILEGE

• ORDS_ADMIN.DEFINE_SERVICE

• ORDS_ADMIN.DEFINE_TEMPLATE

• ORDS_ADMIN.DELETE_TEMPLATE

• ORDS_ADMIN.DELETE_ALL_TEMPLATES

• ORDS_ADMIN.DELETE_MODULE

• ORDS_ADMIN.DELETE_PRIVILEGE

• ORDS_ADMIN.DELETE_ROLE

• ORDS_ADMIN.DROP_REST_FOR_SCHEMA

• ORDS_ADMIN.ENABLE_OBJECT

• ORDS_ADMIN.DROP_REST_FOR_OBJECT

• ORDS_ADMIN.ENABLE_SCHEMA

• ORDS_ADMIN.PUBLISH_MODULE

• ORDS_ADMIN.RENAME_MODULE

• ORDS_ADMIN.RENAME_PRIVILEGE

• ORDS_ADMIN.RENAME_ROLE

• ORDS_ADMIN.SET_MODULE_ORIGINS_ALLOWED

• ORDS_ADMIN.SET_URL_MAPPING

• ORDS_ADMIN.ENABLE_HOUSEKEEPING_JOB

• ORDS_ADMIN.DROP_HOUSEKEEPING_JOB

• ORDS_ADMIN.PERFORM_HOUSEKEEPING

• ORDS_ADMIN.SET_SESSION_DEFAULTS

• ORDS_ADMIN.RESET_SESSION_DEFAULTS

• ORDS_ADMIN.PROVISION_ADMIN_ROLE

• ORDS_ADMIN.PROVISION_RUNTIME_ROLE

• ORDS_ADMIN.UNPROVISION_ ROLES

• ORDS_ADMIN.CONFIG_PLSQL_GATEWAY

• ORDS_ADMIN.SET_PROPERTY

• ORDS_ADMIN.SET_PROPERTY

• ORDS_ADMIN.UNSET_PROPERTY

• ORDS_ADMIN.INSTALLED_VERSION

• ORDS_ADMIN.SET_MODULE_PRIVILEGE

Related Topics

• ORDS 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:

• ORDS.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

• ORDS.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.DELETE_HANDLER

Format

ORDS_ADMIN.DELETE_HANDLER(
   p_schema             IN VARCHAR2,
   p_module_name        IN VARCHAR2,
   p_uri_template       IN VARCHAR2,
   p_method             IN VARCHAR2);

Description

DELETE_HANDLER deletes a specific handler from a template.

Parameters

p_schema

Name of the schema. This parameter is mandatory.

p_module_name

The name of the module that contains the template.

p_uri_template

The URI template that contains the handler to be deleted

Usage Notes

• The parameters p_schema , p_module_name, p_uri_template, and p_method are mandatory.

• If the handler does not exist, no error is returned.

Example 6-1

BEGIN
  ORDS_ADMIN.delete_handler(
    p_schema    	  => 'test_schema',
    p_module_name        => 'my_module',
    p_uri_template       => '/my/template',
    p_method             => 'GET'
  );
END;
/

ORDS_ADMIN.DELETE_ALL_HANDLERS

Format

ORDS_ADMIN.DELETE_ALL_HANDLERS(
   p_schema             IN VARCHAR2,
   p_module_name        IN VARCHAR2,
   p_uri_template       IN VARCHAR2);

Description

DELETE_ALL_HANDLERS deletes all handlers from a template.

Parameters

p_schema

Name of the schema. This parameter is mandatory.

p_module_name

The name of the module that contains the template.

p_uri_template

The URI template that contains the handlers to be deleted.

Usage Notes

• The parameters p_schema, p_module_name, and p_uri_template are mandatory.

• If the template does not exist or does not contain any handlers, no error is returned.

Example 6-2

BEGIN
    ORDS_ADMIN.delete_all_handlers(
    p_schema    	  => 'test_schema',
    p_module_name        => 'my_module',
    p_uri_template       => '/my/template'
  );
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, RESULTSET, VECTOR, VECTOR_BINARY, VECTOR_FLOAT32, VECTOR_FLOAT64, VECTOR_INT8.

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.

• VECTOR parameter types enable REST handlers to bind Oracle Database VECTOR values directly from the JSON request payloads.

• Supported VECTOR parameter types:

  • VECTOR

  • VECTOR_BINARY

  • VECTOR_FLOAT32

  • VECTOR_FLOAT64

  • VECTOR_INT8

  VECTOR values are represented as JSON arrays. Sparse vectors use the Oracle Database–supported sparse vector JSON format.

Examples

Example 6-3 Defining Outbound Parameters

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;
/

Example 6-4 Defining Outbound Parameters

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;
/

Example 6-5 Defining an Inbound Vector Parameter

The following example defines an inbound VECTOR_FLOAT32 parameter named readings for a POST handler:

BEGIN
  ORDS_ADMIN.DEFINE_PARAMETER(
    p_module_name        => 'vector-demo',
    p_pattern            => 'float32',
    p_method             => 'POST',
    p_name               => 'readings',
    p_bind_variable_name => 'readings',
    p_source_type        => 'URI',
    p_param_type         => 'VECTOR_FLOAT32',
    p_access_method      => 'IN'
  );
END;
/

Example request payload:

{"readings":[1.25,2.5,3.75] }

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:

• ORDS.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

• ORDS.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_TEMPLATE

Format

ORDS_ADMIN.DELETE_TEMPLATE(
   p_schema             IN VARCHAR2,
   p_module_name        IN VARCHAR2,
   p_uri_template       IN VARCHAR2);

Description

DELETE_TEMPLATE deletes a specific template from a module.

Parameters

p_schema

Name of the schema. This parameter is mandatory.

p_module_name

The name of the module that contains the template to be deleted.

p_uri_template

The URI template to be deleted.

Usage Notes

• The parameters p_schema, p_module_name, and p_uri_template are mandatory.

• If the template does not exist, no error is returned.

Example 6-6

BEGIN
    ORDS_ADMIN.delete_template(
    p_schema       	  => 'test_schema',
    p_module_name        => 'my_module',
    p_uri_template       => '/my/template'
  );
END;
/

ORDS_ADMIN.DELETE_ALL_TEMPLATES

Format

ORDS_ADMIN.DELETE_ALL_TEMPLATES(
   p_schema             IN VARCHAR2,
   p_module_name        IN VARCHAR2);

Description

DELETE_ALL_TEMPLATES deletes all the templates from a module.

Parameters

p_schema

Name of the schema. This parameter is mandatory.

p_module_name

The name of the module that contains the templates to be deleted.

Usage Notes

• The parameters p_schema and p_module_name parameter is required.

• If the module does not exist or does not contain any templates, no error is returned.

Example 6-7

BEGIN
    ORDS_ADMIN.delete_all_templates(
    p_schema    	  => 'test_schema',
    p_module_name        => 'my_module'
  );
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  IN ords_schemas.parsing_schema%type);

Description

Permanently deletes all Oracle REST Data Services metadata for the associated schema. Prior to executing this procedure, the metadata can be exported through the ORDS client EXPORT command using the ORDS_EXPORT PL/SQL package directly.

DROP_REST_FOR_SCHEMA deletes all auto-REST Oracle REST Data Services metadata for the associated schema. When a database user is dropped, the ORDS REST metadata of that schema remains in place. Use this procedure to remove that metadata. Otherwise, if a database account is created again with the same username it will inherit all the existing metadata.

Parameters

p_schema

The name of the schema.

Usage Notes

This procedure effectively “undoes” the actions performed by the ORDS.Enable_Schema procedure.

The schema may have active sessions with the database, and so the request may still be accepted for a period of time after the metadata of the schema has been updated. If you want this change to take effect immediately, then a database administrator must disconnect the associated sessions for that schema.

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

If p_enabled is set to FALSE for a schema that has been in use and the schema may have active sessions with the database, and so the request may still be accepted for a period of time after the metadata of the schema has been updated. If you want this change to take effect immediately, then a database administrator must disconnect the associated sessions for that schema.

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;
/

ORDS_ADMIN.CONFIG_PLSQL_GATEWAY

Format

ORDS_ADMIN.CONFIG_PLSQL_GATEWAY(
   p_runtime_user        IN varchar2 DEFAULT NULL,
   p_plsql_gateway_user  IN varchar2,
   p_comments            IN varchar2 DEFAULT NULL);

Description

Configures the database proxy user that must be used for PL/SQL Gateway calls serviced by the specified runtime user.

Parameters

p_runtime_user

Name of the runtime user to be configured.

p_plsql_gateway_user

Name of the proxy user.

p_comments

Comment text.

Usage Notes

When p_runtime_user is NULL, then the value provided through ORDS_ADMIN.SET_SESSION_DEFAULTS is used. Otherwise, ORDS_PUBLIC_USER is used. When p_plsql_gateway_user is NULL, then the PL/SQL Gateway for the runtime user is unconfigured.

Examples

The following example configures the PL/SQL Gateway for ORDS_PUBLIC_USER runtime user:

BEGIN
  ords_admin.config_plsql_gateway(
    p_runtime_user    => 'ORDS_PUBLIC_USER',
    p_plsql_gateway_user => 'GATEWAY_USER'
  );
END;
/

The following example unconfigures the PL/SQL Gateway for ORDS_PUBLIC_USER runtime user:

BEGIN
  ords_admin.config_plsql_gateway(
    p_runtime_user    => 'ORDS_PUBLIC_USER',
    p_plsql_gateway_user => NULL
  );
END;
/

ORDS_ADMIN.SET_PROPERTY

Format

ORDS_ADMIN.SET_PROPERTY(
      p_schema          IN ords_schemas.parsing_schema%type,
      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 specified enabled schema. The value must not be NULL.

Parameters

p_schema

The name of the owning enabled schema. This parameter is mandatory.

p_key

The property key.

p_value

The new property value.

Usage Notes

(None.)

Examples

The following example sets a property value:

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

ORDS_ADMIN.SET_PROPERTY

Format

ORDS_ADMIN.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 non-SCHEMA scoped property. The value must not be NULL.

Parameters

p_key

The property key.

p_value

The new property value.

Usage Notes

(None.)

Examples

The following example sets a property value:

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

ORDS_ADMIN.UNSET_PROPERTY

Format

ORDS_ADMIN.UNSET_PROPERTY(
      p_schema          IN ords_schemas.parsing_schema%type,
      p_key             IN ords_prop_facts.key%type);

Description

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

Parameters

p_schema

The name of the owning enabled schema. This parameter is mandatory.

p_key

The property key.

Usage Notes

(None.)

Examples

The following example unsets a property value:

BEGIN
  ORDS_ADMIN.UNSET_PROPERTY(
    p_schema => 'tickets',
    p_key => 'a.key'
  );
END;
/

ORDS_ADMIN.INSTALLED_VERSION

Format

ORDS_ADMIN.INSTALLED_VERSION;

Description

Returns the installed ORDS version number.

Parameters

None.

Usage Notes

Use the INSTALLED_VERSION function to return the installed ORDS version number.

Example

The following example prints the installed version of ORDS:

BEGIN
DBMS_OUTPUT.PUT_LINE('ORDS version : ' || ORDS_ADMIN.INSTALLED_VERSION);
END;
/

ORDS_ADMIN.SET_MODULE_PRIVILEGE

Format

ORDS_ADMIN.SET_MODULE_PRIVILEGE(
   p_module_name        IN ords_modules.name%type,
   p_privilege_name     IN sec_privileges.name%type);

Description

SET_MODULE_PRIVILEGE package associates a resource module with a specified privilege replacing any existing association. If no privilege is provided (that is, if NULL value is passed), any existing privilege association is removed, effectively making the resource module public.

Parameters

p_module_name

Name of the RESTful service module. This parameter is case sensitive and it must be unique.

p_privilege_name

Name of the privilege. No spaces are allowed.

Uasge Notes

Associates a resource module with a privilege, overriding any previous associations. Use this procedure to restrict access to a resource module by assigning it a specific privilege. To remove an existing privilege association and make the module public, pass NULL value as the privilege name.

Example 6-8

The following example associates the module my.tickets with the privilege tickets.privilege:

DECLARE
  P_MODULE_NAME VARCHAR2(255);
  P_PRIVILEGE_NAME VARCHAR2(255);
BEGIN
  P_MODULE_NAME := 'my.tickets';
  P_PRIVILEGE_NAME := 'tickets.privilege';
  ORDS_ADMIN.SET_MODULE_PRIVILEGE(
    P_MODULE_NAME => P_MODULE_NAME,
    P_PRIVILEGE_NAME => P_PRIVILEGE_NAME
  );
END;
/