Oracle ORDS REST modules - use optional parameters in PL/SQL Procedure-CodePudding

I have to create a RESTful service and into the GET handler I have to call a pl/sql procedure. This service can take optional parameters (e.g. http://localhost:8080/ords/Orders/getOrders?status=Pending&orderID=1). How can I use the optional parameters into the pl/sql procedure, because I need to return the data based on them?

Thank you!

CodePudding user response：

Define parameters for your REST API.

SOURCE_TYPE => URI ACCESS_METHOD => IN

Calling the API...

Here's actual code, full demo:

-- Generated by ORDS REST Data Services 22.3.3.r3111929
-- Schema: ADMIN  Date: Tue Dec 20 03:39:01 2022 
--

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'ADMIN',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'admin',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'orders',
      p_base_path      => '/orders/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'question for stack overflow');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'orders',
      p_pattern        => 'getOrders',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'orders',
      p_pattern        => 'getOrders',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'begin
    -- order id
    -- status
    :output := ''Submitted status is '' || :status1 || '' and Order ID is '' || :orderid || ''.'' ;
end;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'orders',
      p_pattern            => 'getOrders',
      p_method             => 'GET',
      p_name               => 'response',
      p_bind_variable_name => 'output',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'orders',
      p_pattern            => 'getOrders',
      p_method             => 'GET',
      p_name               => 'orderid',
      p_bind_variable_name => 'orderid',
      p_source_type        => 'URI',
      p_param_type         => 'INT',
      p_access_method      => 'IN',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'orders',
      p_pattern            => 'getOrders',
      p_method             => 'GET',
      p_name               => 'status',
      p_bind_variable_name => 'status1',
      p_source_type        => 'URI',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);

    
        
COMMIT;

END;

Disclaimer: I work for Oracle and am the product manager for Oracle REST Data Services.