create or replace PACKAGE               ORDS AUTHID current_user
AS
  source_type_query           CONSTANT ords_handlers.source_type%type := 'json/query';
  source_type_csv_query       CONSTANT ords_handlers.source_type%type := 'csv/query'; 
  source_type_query_one_row   CONSTANT ords_handlers.source_type%type := 'json/query;type=single'; 
  source_type_feed            CONSTANT ords_handlers.source_type%type := 'json/query;type=feed'; 
  source_type_media           CONSTANT ords_handlers.source_type%type := 'resource/lob'; 
  source_type_plsql           CONSTANT ords_handlers.source_type%type := 'plsql/block'; 
  source_type_collection_feed CONSTANT ords_handlers.source_type%type := 'json/collection'; 
  source_type_collection_item CONSTANT ords_handlers.source_type%type := 'json/item'; 
  
-- Services

  /**
   * Creates a resource module, template and handler in one convenience call.
   *
   * @deprecated                 Use define_service procedure instead.
   *
   * @param p_module_name        The name of the RESTful service module. Case sensitive. Must be unique.
   *
   * @param 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.
   *
   * @param 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).
   *
   * @param 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).
   *
   * @param 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 ORDS 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 ORDS 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 ORDS 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 ORDS 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
   *
   * @param p_source             The source implementation for the selected HTTP method
   *                             
   *
   * @param 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).
   *
   * @param p_status             The publication status. Valid values: 'PUBLISHED' or 'NOT_PUBLISHED'. Default: 'PUBLISHED'
   *
   * @param 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 valids '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.
   *
   * @param p_etag_query         A query that is used to generate the entity tag.
   *
   * @param p_mimes_allowed      A comma separated list of MIME types that the handler will accept. Applies to PUT and POST only.
   *
   * @param p_module_comments    Commentary text.
   *
   * @param p_template_comments  Commentary text.
   *
   * @param p_handler_comments   Commentary text.
   */
  PROCEDURE create_service(
      p_module_name        IN ords_modules.name%type,
      p_base_path          IN ords_modules.uri_prefix%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_method             IN ords_handlers.method%type DEFAULT 'GET',
      p_source_type        IN ords_handlers.source_type%type DEFAULT ords.source_type_collection_feed,
      p_source             IN ords_handlers.source%type,
      p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
      p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
      p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
      p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
      p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
      p_module_comments    IN ords_modules.comments%type DEFAULT NULL,
      p_template_comments  IN ords_modules.comments%type DEFAULT NULL,
      p_handler_comments   IN ords_modules.comments%type DEFAULT NULL);
      
  /**
   * Defines a resource module, template and handler in one convenience 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.
   *
   * @param p_module_name        The name of the RESTful service module. Case sensitive. Must be unique.
   *
   * @param 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.
   *
   * @param 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).
   *
   * @param 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).
   *
   * @param 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 ORDS 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 ORDS 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 ORDS 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 ORDS 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
   *
   * @param p_source             The source implementation for the selected HTTP method:
   *
   * @param 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).
   *
   * @param p_status             The publication status. Valid values: 'PUBLISHED' or 'NOT_PUBLISHED'. Default: 'PUBLISHED'
   *
   * @param 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 valids '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.
   *
   * @param p_etag_query         A query that is used to generate the entity tag.
   *
   * @param p_mimes_allowed      A comma separated list of MIME types that the handler will accept. Applies to PUT and POST only.
   *
   * @param p_module_comments    Commentary text.
   *
   * @param p_template_comments  Commentary text.
   *
   * @param p_handler_comments   Commentary text.
   */
  PROCEDURE define_service(
      p_module_name        IN ords_modules.name%type,
      p_base_path          IN ords_modules.uri_prefix%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_method             IN ords_handlers.method%type DEFAULT 'GET',
      p_source_type        IN ords_handlers.source_type%type DEFAULT ords.source_type_collection_feed,
      p_source             IN ords_handlers.source%type,
      p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
      p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
      p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
      p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
      p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
      p_module_comments    IN ords_modules.comments%type DEFAULT NULL,
      p_template_comments  IN ords_modules.comments%type DEFAULT NULL,
      p_handler_comments   IN ords_modules.comments%type DEFAULT NULL);
      
      
-- Privileges

  /**
   * Create a Privilege. Privileges are used to protect resources. 
   * A Party accessing the resource must posess at least one of the roles
   * enumerated by the Privilege.
   *
   * @deprecated Use define_privilege procedure instead.
   *
   * @param p_name The name of the privilege, must conform to the syntax
   *               of scope names specified by section 3.3 of the OAuth 2.0 spec.
   *               This value must not be null.
   *
   * @param p_roles The names of the roles that the privilege requires at
   *                least one of. May be empty in which case the user
   *                must be authenticated but does not require any specific 
   *                role. Unauthenticated users will be denied access.
   *                This value must not be null.
   *
   * @param p_label Human readable title to assign the privilege. Displayed
   *                to end user during approval phase of three-legged OAuth.
   *                If null Privilege can only be used in two-legged OAuth flows
   *
   * @param p_description Human readable description of purpose of Privilege.
   *                      Displayed to end user during approval phase of 
   *                      three-legged OAuth. If null Privilege can only be
   *                      used in two-legged OAuth flows.
   */  
  PROCEDURE create_privilege(
      p_name sec_privileges.name%type,
      p_roles owa.vc_arr,
      p_label sec_privileges.label%type DEFAULT NULL,
      p_description sec_privileges.description%type DEFAULT NULL);

  /**
   * Convenience method that makes creating a Privilege with a single role easier.
   *
   * @deprecated Use define_privilege procedure instead.
   *
   * @param p_role_name The name of the role that the privilege requires.
   *                    This value must not be null.
   *
   * @param p_label Human readable title to assign the privilege. Displayed
   *                to end user during approval phase of three-legged OAuth.
   *                If null Privilege can only be used in two-legged OAuth flows
   *
   * @param p_description Human readable description of purpose of Privilege.
   *                      Displayed to end user during approval phase of 
   *                      three-legged OAuth. If null Privilege can only be
   *                      used in two-legged OAuth flows.
   */  
  PROCEDURE create_privilege(
      p_name sec_privileges.name%type,
      p_role_name sec_roles.name%type,
      p_label sec_privileges.label%type DEFAULT NULL,
      p_description sec_privileges.description%type DEFAULT NULL);

  /**
   * Define an ORDS 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.
   *
   * @param p_privilege_name  The name of the privilege. No whitespaces allowed
   *
   * @param p_roles           The names of the roles that the privilege requires at
   *                          least one of. May be empty in which case the user
   *                          must be authenticated but does not require any specific 
   *                          role. Unauthenticated users will be denied access.
   *                          This value must not be null.
   *
   * @param p_patterns        A list of patterns.
   *
   * @param p_modules         A list of module names referencing modules created for the current schema
   *
   * @param p_label           The name of this security constraint as displayed to an end user. NULL value allowed
   *
   * @param p_description     A brief description of the purpose of the resources protected by this constraint
   *
   * @param p_comments        Commentary text.
   */
  PROCEDURE define_privilege(
      p_privilege_name     IN sec_privileges.name%type,
      p_roles              IN owa.vc_arr,
      p_patterns           IN owa.vc_arr,
      p_modules            IN owa.vc_arr,
      p_label              IN sec_privileges.label%type DEFAULT NULL,
      p_description        IN sec_privileges.description%type DEFAULT NULL,
      p_comments           IN sec_privileges.comments%type DEFAULT NULL);

  /**
   * Define an ORDS Privilege.
   * If the privilege already exists then the privilege and any existing patterns and any associations with roles will be replaced by this definition otherwise a new privilege is created.
   *
   * @param p_privilege_name  the name of the privilege. No whitespaces allowed
   * @param p_roles           a list of role names referencing roles created for the current schema and those of ORD_METADATA.
   * @param p_patterns        a list of patterns.
   * @param p_label           the name of this security constraint as displayed to an end user. NULL value allowed
   * @param p_description     a brief description of the purpose of the resources protected by this constraint
   * @param p_comments        commentary text.
   */
  PROCEDURE define_privilege(
      p_privilege_name     IN sec_privileges.name%type,
      p_roles              IN owa.vc_arr,
      p_patterns           IN owa.vc_arr,
      p_label              IN sec_privileges.label%type DEFAULT NULL,
      p_description        IN sec_privileges.description%type DEFAULT NULL,
      p_comments           IN sec_privileges.comments%type DEFAULT NULL);

  /**
   * Define an ORDS Privilege.
   * If the privilege already exists then the privilege and any existing patterns and any associations with roles will be replaced by this definition otherwise a new privilege is created.
   *
   * @param p_privilege_name  the name of the privilege. No whitespaces allowed
   * @param p_roles           a list of role names referencing roles created for the current schema and those of ORD_METADATA.
   * @param p_label           the name of this security constraint as displayed to an end user. NULL value allowed
   * @param p_description     a brief description of the purpose of the resources protected by this constraint
   * @param p_comments        commentary text.
   */
  PROCEDURE define_privilege(
      p_privilege_name     IN sec_privileges.name%type,
      p_roles              IN owa.vc_arr,
      p_label              IN sec_privileges.label%type DEFAULT NULL,
      p_description        IN sec_privileges.description%type DEFAULT NULL,
      p_comments           IN sec_privileges.comments%type DEFAULT NULL);

  /**
   * Update a privilege definition
   *
   * @deprecated Use define_privilege procedure instead.
   *
   * @param p_name The name of the privilege, must conform to the syntax
   *               of scope names specified by section 3.3 of the OAuth 2.0 spec.
   *               This value must not be null.
   *
   * @param p_role_names The names of the roles that the privilege requires at
   *                     least one of. May be empty in which case the user
   *                     must be authenticated but does not require any specific 
   *                     role. Unauthenticated users will be denied access.
   *                     This value must not be null.
   *
   * @param p_label Human readable title to assign the privilege. Displayed
   *                to end user during approval phase of three-legged OAuth.
   *                If null Privilege can only be used in two-legged OAuth flows
   *
   * @param p_description Human readable description of purpose of Privilege.
   *                      Displayed to end user during approval phase of 
   *                      three-legged OAuth. If null Privilege can only be
   *                      used in two-legged OAuth flows.
   */
  PROCEDURE update_privilege(
      p_name               IN sec_privileges.name%type,
      p_roles              IN owa.vc_arr,
      p_label              IN sec_privileges.label%type,
      p_description        IN sec_privileges.description%type);

  /**
   * Rename a privilege
   *
   * @param p_name Name of the privilege to rename
   * @param p_new_name New name to assign the privilege
   */
  PROCEDURE rename_privilege(
      p_name               IN sec_privileges.name%type,
      p_new_name           IN sec_privileges.name%type);
   
  /**
   * Delete a privilege and all its associated mappings
   *
   * @param p_name The name of the privilege
   */
  PROCEDURE delete_privilege(
      p_name               IN sec_privileges.name%type);
   
-- Privilege Mappings

  /**
   * <p>Associate a privilege with a set of resources. Before servicing any
   * resource matching the pattern, the authenticated user will be verified
   * to ensure they possess at least one of the roles enumerated by the Privilege.
   * </p>
   * <p>The pattern must conform to the following syntax:</p>
   * <ul>
   *  <li>The pattern may contain any printable character except those defined by
   *      RFC 3986 section 2.2 as 'reserved', however the / and *
   *      characters are permitted, subject to the rules below.
   *  </li>
   *  <li>The pattern must start with the <code>/</code> character</li>
   *  <li>The pattern may end with the * character. If present the * character
   *      must be immediately preceded by the / character</li>
   * </ul>
   * <p>Each pattern is matched against the sub-portion of the path of a request 
   * URI immediately after the context root of the current request.</p>
   * <p>The pattern is matched against the non URL encoded form of the request
   * path.</p>
   * <p>If a pattern ends with the <code>/*</code> sequence, then the pattern
   * will match any path starting with the characters preceding the * character.
   * Otherwise the pattern will match an exact character for character match.
   * </p>
   * <h3>Examples</h3>
   * Assume ORDS schema deployed at <code>https://example.com/ords/scott/</code>.
   *
   * The pattern <code>/foo/bar/*</code> will match the following request URIs:
   * <ul>
   *  <li><code>https://example.com/ords/scott/foo/bar/</code></li>
   *  <li><code>https://example.com/ords/scott/foo/bar/baz/resource.json</code></li>
   * </ul>
   * The pattern will not match the following request URIs:
   * <ul>
   *  <li><code>https://example.com/ords/scott/foo/bar</code></li>
   *  <li><code>https://example.com/ords/scott/foo/barstool</code></li>
   * </ul>
   *
   * @deprecated Use define_privilege procedure instead.
   *
   * @param p_privilege_name The name of the Privilege.
   *                         This value must not be null.
   *
   * @param p_patterns       The patterns of resources to match.
   */
  PROCEDURE create_privilege_mapping(
      p_privilege_name sec_privileges.name%type,
      p_patterns owa.vc_arr);

  /**
   * Convenience method that makes creating a mapping with a single pattern
   * easier.
   *
   * @deprecated Use define_privilege procedure instead.
   *
   * @param p_privilege_name The name of the Privilege.
   *                         This value must not be null.
   *
   * @param p_pattern        The pattern of a resource to match.
   */
  PROCEDURE create_privilege_mapping(
      p_privilege_name sec_privileges.name%type,
      p_pattern sec_privilege_mappings.pattern%type);
   
  /**
   * Delete the specified privilege mapping.
   *
   * @deprecated Use define_privilege procedure instead.
   *
   * @param p_privilege_name The name of the Privilege.
   *                         This value must not be null.
   *
   * @param p_pattern       The pattern to remove, if this value is null all
   *                        mappings for the named privilege are removed.
   */
  PROCEDURE delete_privilege_mapping(
      p_privilege_name     IN sec_privileges.name%type,
      p_pattern            IN sec_privilege_mappings.pattern%type);
 
-- Privilege Modules

  /**
   * Associate a resource module with a privilege
   * Unassociated resource modules are considered to be public. 
   *
   * Any pre-existing association will be replaced.
   *
   * @param p_module_name     The name of the resource module.
   *
   * @param p_privilege_name  The name of the privilege. When NULL is passed then any existing
   *                          privilege assication is removed.
   */
  PROCEDURE set_module_privilege(
      p_module_name        IN ords_modules.name%type,
      p_privilege_name     IN sec_privileges.name%type DEFAULT NULL);

-- Module Origins Allowed

  /**
   * Configure allowed origins for a resource module
   *
   * Any pre-existing allowed origins will be replaced.
   *
   * @param p_module_name      The name of the resource module.
   *
   * @param p_origins_allowed  A comma separated list is of URL prefixes. If the list is empty
   *                           any pre-existing origins are removed.
   */
  PROCEDURE set_module_origins_allowed(
      p_module_name     IN ords_modules.name%type,
      p_origins_allowed IN sec_origins_allowed_modules.origins_allowed%type);

-- Roles

  /**
   * Create a role with the specified name
   *
   * @param p_role_name The role name
   */
  PROCEDURE create_role(p_role_name IN sec_roles.name%type);
 
  /**
   * Delete the named role, this will delete any association between
   * the role and any privileges that reference the role
   *
   * @param p_role_name The role name
   */
  PROCEDURE delete_role(p_role_name IN sec_roles.name%type);
 
  /**
   * Rename the named role
   *
   * @param p_role_name The role name
   *
   * @param p_new_name  The new role name
   */
  PROCEDURE rename_role(
      p_role_name          IN sec_roles.name%type,
      p_new_name           IN sec_roles.name%type);
 
-- Modules

  /**
   * Define 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.
   *
   * @param p_module_name     The name of the RESTful service module. Case sensitive. Must be unique.
   *
   * @param 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.
   *
   * @param 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 rows
   *
   * @param p_status          The publication status. Valid values: 'PUBLISHED' or 'NOT_PUBLISHED'. Default: 'PUBLISHED'
   *
   * @param p_comments        Commentary text.
   */
  PROCEDURE define_module(
      p_module_name        IN ords_modules.name%type,
      p_base_path          IN ords_modules.uri_prefix%type,
      p_items_per_page     IN ords_modules.items_per_page%type DEFAULT 25,
      p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED',
      p_comments           IN ords_modules.comments%type DEFAULT NULL);

  /**
   * Change the publication status on an ORDS resource module.
   *
   * @param p_module_name  The name of the RESTful service module.
   *
   * @param p_status       The new publication status to be applied. Valid values: 'PUBLISHED' or 'NOT_PUBLISHED' 
   */
  PROCEDURE publish_module(
      p_module_name        IN ords_modules.name%type,
      p_status             IN ords_modules.status%type DEFAULT 'PUBLISHED');

  /**
   * Rename an ORDS resource module.
   *
   * @param p_module_name    The original name of the RESTful service module.
   *
   * @param p_new_name       A new module name. If NULL is passed then the old module name is retained.
   *
   * @param p_new_base_path  A new base path. If NULL is passed then the old base path is retained.
   */
  PROCEDURE rename_module(
      p_module_name        IN ords_modules.name%type,
      p_new_name           IN ords_modules.name%type DEFAULT NULL,
      p_new_base_path      IN ords_modules.uri_prefix%type DEFAULT NULL);

  /**
   * Delete an ORDS resource module.
   * If the module does not already exist or is accessible to the current user then no exception is raised.
   *
   * @param p_module_name  The name of the RESTful service module to be deleted.
   */
  PROCEDURE delete_module(
      p_module_name        IN ords_modules.name%type);

-- Templates

  /**
   * Define an 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.
   *
   * A URI pattern can be either a route pattern or a URI template, although you are encouraged 
   * to use route patterns. Route patterns focus on decomposing the path portion of a URI into 
   * its component parts, while URI templates focus on forming concrete URIs from a template. 
   * For a detailed explanation of route patterns, see docs\javadoc\plugin-api\route-patterns.html, 
   * under <sqldeveloper-install>\ords and under the location (if any) where you manually installed 
   * Oracle REST Data Services.
   *
   * @param p_module_name  The name of the owning RESTful service module.
   *
   * @param 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).
   *
   * @param p_priority     The priority for the order of how the resource template should be evaluated (0 (low priority) through to 9 (high priority)).
   *
   * @param 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 valids '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.
   *
   * @param p_etag_query   A query that is used to generate the entity tag.
   *
   * @param p_comments     Commentary text.
   */
  PROCEDURE define_template(
      p_module_name        IN ords_modules.name%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_priority           IN ords_templates.priority%type DEFAULT 0,
      p_etag_type          IN ords_templates.etag_type%type DEFAULT 'HASH',
      p_etag_query         IN ords_templates.etag_query%type DEFAULT NULL,
      p_comments           IN ords_templates.comments%type DEFAULT NULL);

-- Handlers

  /**
   * Define an 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. Only one handler for each HTTP method (source type) is permitted.
   *
   * @param p_module_name     The name of the owning RESTful service module.
   *
   * @param p_pattern         The matching pattern for the owning resource template.
   *
   * @param p_method          The HTTP Method to which this handler will respond.
   *                          '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).
   *
   * @param 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 ORDS 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 ORDS 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 ORDS 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 ORDS 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
   *
   * @param p_source          The source implementation for the selected HTTP method
   *
   * @param 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).
   *
   * @param p_mimes_allowed   A comma separated list of MIME types that the handler will accept. Applies to PUT and POST only.
   *
   * @param p_comments        Commentary text.
   */
  PROCEDURE define_handler(
      p_module_name        IN ords_modules.name%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_method             IN ords_handlers.method%type DEFAULT 'GET',
      p_source_type        IN ords_handlers.source_type%type DEFAULT ords.source_type_collection_feed,
      p_source             IN ords_handlers.source%type,
      p_items_per_page     IN ords_handlers.items_per_page%type DEFAULT NULL,
      p_mimes_allowed      IN ords_handlers.mimes_allowed%type DEFAULT NULL,
      p_comments           IN ords_handlers.comments%type DEFAULT NULL);

-- Parameters

  /**
   * Define an module handler parameter.
   * If the parameter already exists then the parameter will be replaced by this definition 
   * otherwise a new parameter is created.
   *
   * @param p_module_name         The name of the owning RESTful service module.
   *
   * @param p_pattern             The matching pattern for the owning resource template.
   *
   * @param p_method              The owning handler HTTP Method.
   *
   * @param 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 legal 
   *                              SQL parameter names.
   *
   * @param p_bind_variable_name  The name of the parameter, as it will be refered to in the SQL. 
   *                              Where NULL is specified then the parameter is unbound.
   *
   * @param p_source_type         The type which identifies if the parameter originates in the 
   *                              URI Template or a HTTP Header. 
   *                              Valid values: 'HEADER','RESPONSE','URI'
   *
   * @param p_param_type          The native type of the parameter. 
   *                              Valid values are: 'STRING','INT','DOUBLE','BOOLEAN','LONG','TIMESTAMP'
   *
   * @param p_access_method       The parameter access method. Identifies if the parameter is an input value, 
   *                              output value or both. Valid values: 'IN','INOUT','OUT'.
   *
   * @param p_comments            Commentary text.
   */
  PROCEDURE define_parameter(
      p_module_name        IN ords_modules.name%type,
      p_pattern            IN ords_templates.uri_template%type,
      p_method             IN ords_handlers.method%type,
      p_name               IN ords_parameters.name%type ,
      p_bind_variable_name IN ords_parameters.bind_variable_name%type DEFAULT NULL,
      p_source_type        IN ords_parameters.source_type%type DEFAULT 'HEADER',
      p_param_type         IN ords_parameters.param_type%type DEFAULT 'STRING',
      p_access_method      IN ords_parameters.access_method%type DEFAULT 'IN',
      p_comments           IN ords_parameters.comments%type DEFAULT NULL);

-- Enablements  

  /**
   * Enable/disable the specified schema for ORDS access.
   * Only DBA users can enable/disable a schema other than their own.
   *
   * @param p_enabled              True if ORDS schema access is to be enabled, othewise disabled.
   *
   * @param p_schema               The name of the schema.
   *
   * @param p_url_mapping_type     URL mapping type, Valid values: 'BASE_PATH','BASE_URL'.
   *
   * @param p_url_mapping_pattern  URL mapping pattern.
   *
   * @param p_auto_rest_auth       True if authentication is required prior to actioning by default, otherwise  
   *                               no authenication is required by default.
   */
  PROCEDURE enable_schema(
      p_enabled             IN boolean DEFAULT TRUE,
      p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
      p_url_mapping_type    IN ords_url_mappings.type%type DEFAULT 'BASE_PATH',
      p_url_mapping_pattern IN ords_url_mappings.pattern%type DEFAULT NULL,
      p_auto_rest_auth      IN boolean DEFAULT NULL);
    
  /**
   * delete all auto rest ords metadata associated schema
   */
  PROCEDURE drop_rest_for_schema(p_schema ords_schemas.parsing_schema%type DEFAULT NULL);

  /**
   * Enable/disable the specified database object for ORDS access.
   * Only DBA users can enable/disable a objects other than their own.
   *
   * @param p_enabled         True if ORDS access is to be enabled, othewise disabled.
   *
   * @param p_schema          The name of the schema with access to the database object.
   *
   * @param p_object          The name of the database object to enable/disable.
   *
   * @param p_object_type     The database object type as configured in the Oracle catalog.
   *
   * @param p_object_alias    Used as the URI path element for this object. When NULL a lowercase
   *                          representation is used.
   *
   * @param p_auto_rest_auth  True if authentication is required prior to actioning, otherwise the 
   *                          owning schema setting is enforced.
   */
  PROCEDURE enable_object(
      p_enabled             IN boolean DEFAULT TRUE,
      p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
      p_object              IN ords_objects.parsing_object%type,
      p_object_type         IN ords_objects.type%type DEFAULT 'TABLE',
      p_object_alias        IN ords_objects.object_alias%type DEFAULT NULL,
      p_auto_rest_auth      IN boolean DEFAULT NULL);

  /**
   * Check the specified object for ORDS support.
   * Returns a list of unsupported object attributes.
   * Only DBA users can check an objects support other than their own.
   * Only objects fully supported by ORDS can be enabled.
   *
   * @param p_schema       The name of the schema with access to the database object.
   *
   * @param p_object       The name of the database object.
   *
   * @param p_object_type  The database object type as configured in the Oracle catalog.
   */
  FUNCTION check_object_support(
      p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
      p_object              IN ords_objects.parsing_object%type,
      p_object_type         IN ords_objects.type%type DEFAULT 'TABLE')
    RETURN t_ords_attr_support_tab;

-- URL Mappings

  /**
   * Configure how the specified schema is mapped to request URLs.
   * Only DBA users can update the mapping of a schema other than their own.
   *
   * @param p_schema               The name of the schema to map.
   *
   * @param p_url_mapping_type     URL mapping type, Valid values: 'BASE_PATH','BASE_URL'.
   *
   * @param p_url_mapping_pattern  URL mapping pattern.
   */
  PROCEDURE set_url_mapping(
      p_schema              IN ords_schemas.parsing_schema%type DEFAULT NULL,
      p_url_mapping_type    IN ords_url_mappings.type%type,
      p_url_mapping_pattern IN ords_url_mappings.pattern%type);
      
  /**
   * Return the installed ORDS version number
   */
  FUNCTION installed_version RETURN VARCHAR2;
END;
