7 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_ADMINSTRATOR_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.

7.1 Example Subprograms for Developing and Administering RESTful Services

This section provides example subprograms that are used for developing and administering the RESTful services using ORDS.

Following are some of the example subprograms:

Example 7-1 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);

The following example creates a role:

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

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

Example 7-2 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);

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

Parameters

p_schema

Specifies the name of the schema. This parameter is mandatory.

Example 7-3 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

Specifies whether to enable ORDS HOUSEKEEPING_JOB. Set the value to TRUE to enable ORDS HOUSEKEEPING_JOB. To disable, set the value to FALSE. A NULL value creates and enables the job if it does not already exists. Otherwise, the enabled state remains unchanged.

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;

Example 7-4 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;

Example 7-5 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 against the ORDS metadata repository.

EXECUTE ORDS_ADMIN.PERFORM_HOUSEKEEPING;