How do I Handle the Response

 Handling the REST response effectively is crucial when working with Oracle APEX REST services. The response from a REST service contains the data or message returned after a client request, and properly processing this response ensures that your application can display, use, or store the information as needed. Understanding how to interpret the response format, manage success or error statuses, and transform the returned data enables you to build dynamic and user-friendly APEX applications that integrate seamlessly with external or internal REST APIs.

Handling the response from a REST service in Oracle APEX involves processing the data returned by the service and integrating it into your application. When you make a REST call, the service sends back a response, typically in JSON or XML format, along with an HTTP status code that indicates whether the request was successful or if an error occurred. Proper handling of this response ensures your application behaves correctly, displays the expected data, and manages errors gracefully.

To handle the response in Oracle APEX, follow these steps:

1. Invoke the REST Service: Use a REST Data Source or a PL/SQL procedure with UTL_HTTP or APEX_WEB_SERVICE package to call the REST endpoint.

2. Check the HTTP Status Code: After receiving the response, always check the HTTP status code. Codes like 200 indicate success, while codes like 400 or 500 indicate client or server errors. You can write conditional logic to handle these scenarios accordingly.

3. Parse the Response Data: Most REST responses come in JSON format. Use Oracle's built-in JSON functions (such as JSON_VALUE, JSON_TABLE) or PL/SQL packages to extract the relevant data fields from the response.

4. Bind Data to APEX Components: After parsing, the extracted data can be stored in collections, session state items, or directly used to populate reports, forms, or interactive grids within your APEX application.

5. Error Handling and Logging: If the response indicates an error, display appropriate messages to users or log the errors for debugging. Use APEX’s APEX_ERROR or custom error handling logic to manage this.

6. Refresh UI Components: Once the response data is processed, trigger dynamic actions or page refreshes to update the UI components like reports or charts to reflect the new data.

Example PL/SQL snippet to handle a JSON REST response:

DECLARE
  l_response CLOB;
  l_status_code NUMBER;
  l_value VARCHAR2(100);
BEGIN
  l_response := APEX_WEB_SERVICE.make_rest_request(
                  p_url => 'https://api.example.com/data',
                  p_http_method => 'GET');
  l_status_code := APEX_WEB_SERVICE.g_status_code;
  
  IF l_status_code = 200 THEN
    -- Extract a value from JSON response
    SELECT JSON_VALUE(l_response, '$.data.item_name') INTO l_value FROM dual;
    -- Use l_value as needed, e.g., store in page item or collection
  ELSE
    -- Handle error
    raise_application_error(-20001, 'REST service call failed with status ' || l_status_code);
  END IF;
END;

By carefully managing the REST response in your Oracle APEX applications, you ensure reliable integration with external systems, providing seamless and dynamic user experiences.

Another Example

Once you make the RESTful API call, you will get a JSON or XML response, depending on how the service is configured.

1. If JSON: Use APEX’s JSON utilities to parse and extract the values. For example:

DECLARE

    l_json       JSON_OBJECT_T;

    l_name       VARCHAR2(100);

    l_position   VARCHAR2(100);

BEGIN

    -- Parse the response

    l_json := JSON_OBJECT_T.parse(l_response);

    -- Extract specific values from JSON

    l_name := l_json.get('name').to_string();

    l_position := l_json.get('position').to_string();

    -- Populate APEX items with the extracted data

    :P1_EMPLOYEE_NAME := l_name;

    :P1_EMPLOYEE_POSITION := l_position;

END;

If XML: Use Oracle’s XML utilities to parse and extract values if the response is XML.

Once you have extracted the relevant data (e.g., name, position), you can display it on your APEX page by setting values to page items like P1_EMPLOYEE_NAME, P1_EMPLOYEE_POSITION, etc.

In conclusion, mastering the handling of REST responses in Oracle APEX allows developers to create robust, responsive applications that can interact smoothly with various RESTful services. By correctly parsing response data, managing HTTP status codes, and incorporating error handling, you enhance both the functionality and reliability of your applications, providing a better experience for end-users and maintaining data integrity throughout the system.

How do I Use Query Parameters

 Using query parameters in Oracle APEX REST services enables you to pass additional data to your endpoints through the URL, allowing for flexible and dynamic data retrieval or processing. Query parameters are appended to the URL after a question mark (?) and consist of key-value pairs, such as ?status=active&limit=10. This approach is essential for filtering, sorting, or paginating results without changing the REST service's base URL structure.

In Oracle APEX, you can access query parameters in your RESTful services by referring to bind variables that correspond to the parameter names. For example, if the URL contains ?status=active, you can use :status in your SQL or PL/SQL code to filter data accordingly. Handling query parameters correctly allows developers to create more powerful and user-friendly APIs that respond dynamically to client requests, improving both performance and user experience.

Using query parameters in Oracle APEX REST services allows you to pass extra information through the URL to dynamically control the behavior of your service. Query parameters appear after a question mark (?) in the URL and consist of key-value pairs separated by ampersands (&), for example: ?status=active&limit=10. These parameters enable filtering, sorting, pagination, or other custom logic within your REST service.

To use query parameters in Oracle APEX, first define your RESTful service module and its REST endpoints in SQL Workshop or through RESTful Services. Within the SQL or PL/SQL code of your REST endpoint, you can reference query parameters as bind variables using the colon notation. For instance, if your URL contains ?status=active, you can access the parameter in your SQL query as :status. This allows you to write flexible queries such as:

SELECT * FROM employees WHERE status = :status

If a query parameter is not provided by the client, the bind variable will be NULL. You can handle this with NVL or CASE statements to provide default behavior. For example:

SELECT * FROM employees
WHERE status = NVL(:status, 'ACTIVE')

In addition to filtering, you can use query parameters for pagination by accepting parameters like limit and offset and applying them in your SQL using FETCH FIRST :limit ROWS ONLY or OFFSET :offset ROWS.

It is important to validate query parameters to ensure they contain expected and safe values. You can do this either within PL/SQL blocks or by adding validation logic in your REST service code to prevent SQL injection or errors.

In summary, query parameters in Oracle APEX REST services provide a powerful way to tailor responses dynamically based on client input. By referencing these parameters as bind variables in your SQL or PL/SQL, you can implement flexible filtering, sorting, and pagination while maintaining clean, reusable REST endpoints. Proper validation and default handling of parameters help ensure secure and reliable REST service operation.

Alternatively, if the RESTful service expects the employee_id as a query parameter, you can pass it like this:

Example URL:

https://your-server/ords/schema/rest/employees?id=123

In this case, modify the code to append the ID as a query parameter:

DECLARE

    l_url        VARCHAR2(32767);

    l_response   CLOB;

    l_employee_id VARCHAR2(100);

BEGIN

    -- Get the employee ID from the page item

    l_employee_id := :P1_EMPLOYEE_ID;

    -- Construct the RESTful URL with the employee ID as a query parameter

    l_url := 'https://your-server/ords/schema/rest/employees?id=' || l_employee_id;

    -- Make the REST call to the service

    l_response := APEX_WEB_SERVICE.make_rest_request(

                      p_url          => l_url,

                      p_http_method  => 'GET'

                  );

    -- Process the response

    -- (Handle response as JSON or XML depending on your API)

    DBMS_OUTPUT.put_line(l_response);

    

    -- You can also populate the page items with data from the response

    :P1_EMPLOYEE_DATA := l_response; -- Example: Store response in a page item

END;

Here, id is passed as a query parameter, and the API can handle this as such.

Mastering the use of query parameters in Oracle APEX REST services is vital for building robust and adaptable web applications. By efficiently capturing and using these parameters, you can tailor data responses to meet specific client needs, optimize data transfer, and maintain clean, organized REST endpoints. Implementing query parameters thoughtfully enhances your API’s flexibility and scalability, making it easier to support diverse use cases and evolving business requirements.

How do I USE Path Parameters in REST

 Using path parameters in REST APIs is a fundamental technique to create dynamic and flexible endpoints that can handle variable data within the URL itself. In Oracle APEX and RESTful services, path parameters allow developers to define parts of the URI that act as placeholders for values such as IDs or names. This approach makes REST APIs more intuitive and resource-oriented, enabling users to request specific data by embedding identifiers directly into the URL path.

Using path parameters in REST services within Oracle APEX allows you to create dynamic and flexible REST endpoints that respond to variable inputs embedded directly in the URL. Path parameters are placeholders in the URL path that are replaced by actual values when the service is called. This enables clients to request specific resources or filter data without relying on query strings.

To use path parameters in REST in Oracle APEX, first you need to define a RESTful service module and resource. When creating a resource, specify the URI pattern with placeholders wrapped in curly braces {}. For example, /employees/{employee_id} defines a path parameter named employee_id. This indicates that any request to /employees/123 will pass 123 as the value for employee_id.

Next, in the RESTful service resource handler (GET, POST, PUT, DELETE), you can reference the path parameter in your SQL query or PL/SQL code. Oracle APEX automatically maps the path parameter value to a bind variable with the same name. For example, in the SQL source you can use :employee_id to filter records like:

SELECT * FROM employees WHERE employee_id = :employee_id

Similarly, in PL/SQL, you can access the path parameter by referring to the bind variable :employee_id.

When testing the REST endpoint, replace the path parameter placeholder with an actual value in the URL. The REST service will then use that value in the backend logic to return or manipulate the targeted resource.

In summary, the key steps are:

1. Define a RESTful service module in Oracle APEX or ORDS.

2. Create a resource with a URI template containing path parameters using {}.

3. Use the corresponding bind variables in your SQL or PL/SQL to access parameter values.

4. Call the REST endpoint with actual values replacing the path parameters in the URL.

This method makes your REST services more intuitive and RESTful, adhering to resource-oriented URL design principles. It also simplifies filtering and accessing specific records, improving API usability and flexibility.

You would call the service by embedding the ID number directly in the URL path.

Example URL:

https://your-server/ords/schema/rest/employees/{employee_id}

To dynamically call the service using the ID number provided by the user (e.g., through a page item like P1_EMPLOYEE_ID), you can use the following approach:

1. Create a Button or Process to trigger the call.

2. Use the APEX_WEB_SERVICE package in PL/SQL to make the RESTful call.

DECLARE

    l_url        VARCHAR2(32767);

    l_response   CLOB;

    l_employee_id VARCHAR2(100);

BEGIN

    -- Get the employee ID from the page item

    l_employee_id := :P1_EMPLOYEE_ID;

    -- Construct the RESTful URL with the employee ID

    l_url := 'https://your-server/ords/schema/rest/employees/' || l_employee_id;

    -- Make the REST call to the service

    l_response := APEX_WEB_SERVICE.make_rest_request(

                      p_url          => l_url,

                      p_http_method  => 'GET'

                  );

    -- Process the response

    -- (Handle response as JSON or XML depending on your API)

    DBMS_OUTPUT.put_line(l_response);

    

    -- You can also populate the page items with data from the response

    :P1_EMPLOYEE_DATA := l_response; -- Example: Store response in a page item

END;

Here, the l_url is dynamically constructed by appending the employee_id from the page item P1_EMPLOYEE_ID to the base URL.

Mastering the use of path parameters in REST services is essential for building scalable and maintainable APIs in Oracle APEX. By effectively utilizing path parameters, you can design cleaner, more efficient endpoints that clearly represent resources and their relationships. This improves both the developer experience and the overall usability of your RESTful interfaces.

 Creating RESTful Service Modules in Oracle APEX enables developers to build scalable and organized APIs that facilitate seamless communication between applications. These modules group related REST endpoints, making it easier to manage and maintain your services. By leveraging RESTful Service Modules, you can design clean, modular APIs that follow best practices for resource organization, improving both development efficiency and API usability.

Creating RESTful Service Modules in Oracle APEX involves organizing related REST endpoints into a cohesive unit, which simplifies management and improves API clarity. Here’s how you do it in detail:

1. Access Oracle REST Data Services (ORDS) Console
   RESTful Service Modules are managed via ORDS, which integrates with Oracle Database and APEX. Start by logging into the ORDS administration interface or Oracle APEX RESTful Services section.

2. Create a New Module
   Within the RESTful Services area, choose to create a new Module. The module represents a logical grouping of REST endpoints, usually by functionality or resource type, for example, “Employees” or “Orders.”

3. Define Module Properties
   Provide a unique module name, URI prefix, and optionally a description. The URI prefix defines the base path for all REST endpoints within this module, such as /api/employees.

4. Create Templates (Endpoints)
   Inside the module, create one or more templates. A template defines the URI pattern for an endpoint, such as /list or /{employee_id}. Templates can include variables in curly braces to accept dynamic input.

5. Define Handlers for HTTP Methods
   For each template, define handlers that specify how different HTTP methods (GET, POST, PUT, DELETE) are processed. Each handler has an associated SQL or PL/SQL code block that implements the business logic or database interaction.

6. Set Parameters and Request/Response Formats
   Configure any parameters the handler requires, such as path parameters or query parameters. Also specify the request and response media types (typically JSON or XML), ensuring data is exchanged correctly.

7. Test and Publish the Module
   Use the built-in testing tools to verify your REST endpoints. Once confirmed, publish the module to make it available for client applications.

8. Manage Security and Access Controls
   Assign authentication schemes and privileges to control who can access each module or endpoint. This ensures your RESTful services are secure.

Using RESTful Service Modules promotes reusable, well-structured APIs in Oracle APEX. This modular approach facilitates easier maintenance and scalability as your application grows.

Create RESTful Service Modules

Calloing a RESTful Service by ID number in Oracle APEX , you need to pass the ID as part of the URL or query parameters. This can be done in a variety of ways, depending on the structure of the RESTful service you're calling.

We have 3 options, with the last option being the simplest (It also has step by step descriptions)

Key Concepts:

1. RESTful Web Services: REST (Representational State Transfer) is an architectural style used for designing networked applications. RESTful web services allow you to perform operations like GET, POST, PUT, and DELETE over HTTP, typically using JSON or XML as the data format.

2. RESTful Web Service in APEX: Oracle APEX provides built-in functionality to consume and interact with external REST APIs directly through APEX components, such as Web Source or RESTful Services.

3. Data Synchronization: The goal of synchronization is to keep the data in APEX consistent with the data on an external system. This can involve:

• Pulling data from an external REST API to populate your APEX application (e.g., fetching remote data and displaying it in APEX forms or reports).

• Pushing data to an external system by sending data from APEX to the REST API (e.g., submitting a form and sending the data to a remote service).

• Updating or deleting data from APEX to keep both systems in sync.

Scenario: Calling a RESTful Service by ID

Let’s assume you have an existing RESTful Service for a resource, such as employees, and you want to retrieve details for a specific employee based on an ID number.

Steps to Call a RESTful Service by ID Number in Oracle APEX 23:

Step 1: Define the RESTful Web Service

Let’s say the resource employees is exposed as a RESTful Web Service and you have a query parameter for the id.

1. Define the RESTful Resource in APEX: 

• The Resource Path can be something like /employees/{employee_id}.

• The employee_id would be a parameter in the RESTful Web Service URL.

Example of defining the resource path:

/employees/{employee_id}

Where {employee_id} is a placeholder for the actual ID passed by the user or application.

Step 2: Prepare the Call to the RESTful Service in APEX

To call this service by employee_id, you can either pass the ID in the URL as a path parameter or use a query parameter.

In conclusion, RESTful Service Modules provide a structured approach to developing and managing REST APIs in Oracle APEX. By grouping related endpoints and applying consistent design principles, you ensure your services are maintainable, scalable, and easy to understand. This approach not only enhances developer productivity but also delivers a better experience for API consumers.

How do I Apply Best Practices for Creating REST Services in APEX

 Creating REST services in Oracle APEX is a powerful way to enable seamless communication between your database and external applications. However, to ensure these services are efficient, secure, and maintainable, it’s essential to follow best practices during their design and implementation. This blog will guide you through key considerations such as proper endpoint structuring, security configurations, performance optimization, and consistent error handling, helping you build robust REST services that align with industry standards and your application needs.

When creating REST services in Oracle APEX, applying best practices ensures that your APIs are secure, efficient, and maintainable. First, carefully design your REST endpoints by following a clear and consistent URL structure that reflects the resource hierarchy. Use meaningful HTTP methods (GET, POST, PUT, DELETE) corresponding to actions performed on resources. For example, use GET to retrieve data and POST to create new records.

Security is paramount. Always implement authentication and authorization mechanisms. In APEX, this often involves using OAuth2 or API key validation to restrict access. Limit the data exposed by your REST service to only what is necessary, and avoid sending sensitive information unless properly secured.

Performance optimization includes writing efficient SQL queries behind your REST endpoints, avoiding unnecessary joins or large data sets, and leveraging pagination where applicable. Use bind variables in your SQL to improve execution plans and reduce parsing overhead.

Maintainability improves by adopting consistent naming conventions for your REST resources and methods. Use clear and descriptive parameter names. Include proper error handling in your service logic, returning meaningful HTTP status codes and error messages to help consumers of the API handle responses appropriately.

Lastly, document your REST services clearly. Provide details about each endpoint, expected parameters, and response formats. This helps your team and external developers understand and use your services correctly.

By following these best practices, you create REST services in Oracle APEX that are robust, secure, and easy to manage over time.

• Use parameterized queries to prevent SQL injection.

• Implement authentication to restrict access to sensitive data.

• Optimize queries to improve performance for large datasets.

• Enable pagination for GET requests using OFFSET and FETCH NEXT.

• Use CORS policies to allow secure cross-origin API calls.

Creating a REST service from a table in Oracle APEX allows applications to expose database data securely over HTTP. By defining GET, POST, PUT, and DELETE handlers, developers can build robust APIs that integrate with other systems, mobile apps, and web applications. This approach simplifies data exchange while maintaining security and performance.

EXAMPLE:

Navigate to: SQL Workshop > RESTful Services

Select: RESTful Data Services > Enable Object

Click on the green Button

Select the table that you want to create a RESTful Service from.

Copy the service URL from the screen.

https://adbnphxadcdev01.adb.us-phoenix-1.oraclecloudapps.com/ords/xxdii_paul_test/airportslist/

Result when the url is entered into the browser.

Applying best practices when creating REST services in APEX not only improves the reliability and security of your APIs but also enhances their scalability and ease of maintenance. By carefully planning your service architecture, securing access, optimizing queries, and adopting consistent naming conventions, you create REST endpoints that serve your users effectively while minimizing potential issues. Following these guidelines ensures your REST services remain a valuable asset in your APEX applications for the long term.