Using FTS: An Example

Overview

This chapter provides an example of an API that provides a set of functions and procedures to facilitate data export from a database query to object store using FTS. The API consists of four main components:

get_idcs_token: Obtains an IDCS token for authentication.
generate_par: Generates a Pre-Authenticated Request (PAR) for uploading data to an object store.
put_object_in_store: Uploads a BLOB to object store using a PAR.
export_query_to_blob: Exports the result of a database query to a BLOB.

Functions and Procedures

get_idcs_token

Purpose: Obtain an IDCS token for authentication.
Parameters:
- p_idcs_url: IDCS URL.
- p_idcs_client_id: IDCS client ID.
- p_idcs_client_secret: IDCS client secret.
- p_scope_suffix: IDCS scope suffix.
Return Value: IDCS token (VARCHAR2).
Raises: INVALID_IDCS_CREDENTIALS if the IDCS credentials are invalid.

generate_par

Purpose: Generate a Pre-Authenticated Request (PAR) for uploading data to an object store.
Parameters:
- p_base_url: Base URL of object store.
- p_tenant: Tenant ID.
- p_idcs_token: IDCS token obtained using get_idcs_token.
- p_os_prefix: Object store prefix.
- p_file_name: File name to be uploaded.
Return Value: PAR access URI (VARCHAR2).
Raises: PAR_GENERATION_FAILED if PAR generation fails.

put_object_in_store

Purpose: Upload a BLOB to object store using a PAR.
Parameters:
- p_access_uri: PAR access URI obtained using generate_par.
- p_blob_data: BLOB data to be uploaded.
Raises: UPLOAD_FAILED if the upload fails.

export_query_to_blob

Purpose: Export the result of a database query to a BLOB.
Parameters:
- p_sql_query: SQL query to be executed.
Return Value: BLOB containing the query results.
Raises: QUERY_EXECUTION_FAILED if the query execution fails.

Example Usage

DECLARE
    l_idcs_token  VARCHAR2(4000);
    l_par_uri     VARCHAR2(32767);
    l_blob_data   BLOB;
BEGIN
    l_idcs_token := get_idcs_token('https://idcs-url.com', 'client-id', 'client-secret', 'scope-suffix');
    l_par_uri := generate_par('https://object-store-url.com', 'tenant-id', l_idcs_token, 'os-prefix', 'file-name.csv');
    l_blob_data := export_query_to_blob('SELECT * FROM my_table');
    put_object_in_store(l_par_uri, l_blob_data);
END;

Error Handling

The API raises specific exceptions for each function/procedure, allowing the caller to handle errors accordingly. The exceptions are:

INVALID_IDCS_CREDENTIALS
PAR_GENERATION_FAILED
UPLOAD_FAILED
QUERY_EXECUTION_FAILED

These exceptions can be caught and handled using standard PL/SQL error handling mechanisms.

Securely Obtaining an IDCS Token

Overview

The get_idcs_token_for_planning_app function provides a secure way to obtain an IDCS token for the Planning App. This function is designed to be used by authorized users and applications, and it abstracts away the underlying complexity of obtaining an IDCS token.

Security Considerations

The get_idcs_token_for_planning_app function is a privileged function that has access to sensitive information, such as the IDCS client ID and client secret. Therefore, execute permission on this function is restricted to authorized users and applications.
The function does not expose the underlying IDCS client ID and client secret, ensuring that these sensitive values are not compromised.

Using the `get_idcs_token_for_planning_app` Function

To obtain an IDCS token securely using the get_idcs_token_for_planning_app function, follow these steps:

Ensure that you have the necessary execute permission on the get_idcs_token_for_planning_app function.
Call the get_idcs_token_for_planning_app function using a secure connection (for example, over a secure database connection).
The function will return a valid IDCS token that can be used for authentication and authorization purposes.

Example Usage

DECLARE
    l_idcs_token VARCHAR2(4000);
BEGIN
    l_idcs_token := get_idcs_token_for_planning_app();
    -- Use the obtained IDCS token for authentication and authorization purposes
END;

Using the `export_query_to_object_store` Procedure

The export_query_to_object_store procedure is a template that exports the result of a SQL query to object store. To use this procedure, you need to replace the hardcoded placeholders with your actual values.

Prerequisites

The get_idcs_token_for_planning_app, export_query_to_blob, generate_par, and put_object_in_store functions/procedures are available and properly configured.
The object store is properly set up and accessible.

Usage

To use the export_query_to_object_store procedure, simply call it with the SQL query you want to export as an argument.

BEGIN
    export_query_to_object_store('SELECT * FROM my_table');
END;

Customization

Before using this procedure, you need to modify the export_query_to_object_store procedure to replace the hardcoded placeholders with your actual values. These placeholders include:

Object store prefix
Base URL
Tenant ID
IDCS URL
File name

You should update the procedure to use your specific values for these placeholders.

Example

Suppose you want to export the result of a query to an object store with a prefix of my_prefix, base URL of https://example.com, tenant ID of my_tenant, and file name of my_file.csv. You would need to modify the export_query_to_object_store procedure to use these values.

Once you have customized the procedure, you can use it to export your query results to the object store.

get_idcs_token

CREATE OR REPLACE FUNCTION get_idcs_token(
    p_idcs_url           IN VARCHAR2,
    p_idcs_client_id     IN VARCHAR2,
    p_idcs_client_secret IN VARCHAR2,
    p_scope_suffix       IN VARCHAR2
) RETURN VARCHAR2
IS
    l_response        CLOB;
    l_token           VARCHAR2(4000);
    idcs_base_64_identity VARCHAR2(2000);
BEGIN
    idcs_base_64_identity := REPLACE(REPLACE(REPLACE(UTL_ENCODE.TEXT_ENCODE(p_idcs_client_id || ':' || p_idcs_client_secret, 'WE8ISO8859P1', UTL_ENCODE.BASE64), CHR(9)), CHR(10)), CHR(13));
 
    APEX_WEB_SERVICE.G_REQUEST_HEADERS.DELETE;
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Authorization';
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'Basic ' || idcs_base_64_identity;
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).NAME := 'Content-Type';
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).VALUE := 'application/x-www-form-urlencoded; charset=UTF-8';
 
    l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        p_url         => p_idcs_url,
        p_http_method => 'POST',
        p_parm_name   => APEX_UTIL.STRING_TO_TABLE('grant_type:scope'),
        p_parm_value  => APEX_UTIL.STRING_TO_TABLE('client_credentials,rgbu:rpas:psraf-' || p_scope_suffix, ',')
    );
 
    IF l_response IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Failed to retrieve IDCS token. Response is null.');
    END IF;
 
    BEGIN
        APEX_JSON.PARSE(l_response);
        l_token := APEX_JSON.GET_VARCHAR2(p_path => 'access_token');
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20002, 'Failed to parse IDCS token response: ' || SQLERRM);
    END;
 
    IF l_token IS NULL THEN
        RAISE_APPLICATION_ERROR(-20003, 'IDCS token is null.');
    END IF;
 
    RETURN l_token;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20004, 'Error getting IDCS token: ' || SQLERRM);
END get_idcs_token;

generate_par

CREATE OR REPLACE FUNCTION generate_par(
    p_base_url           IN VARCHAR2,
    p_tenant             IN VARCHAR2,
    p_idcs_token         IN VARCHAR2,
    p_os_prefix          IN VARCHAR2,
    p_file_name          IN VARCHAR2
) RETURN VARCHAR2
IS
    l_response     CLOB;
    l_req_body     CLOB;
    l_access_uri   VARCHAR2(32767);
BEGIN
    -- Create the JSON request body for FTS invocation
    l_req_body := create_par_request_body(p_os_prefix, p_file_name);
 
    -- Get PAR from FTS service
    l_response := apex_web_service.make_rest_request(
        p_url         => p_base_url || '/' || p_tenant || '/RetailAppsReSTServices/services/private/FTSWrapper/upload',
        p_http_method => 'POST',
        p_body        => l_req_body,
        p_headers     => apex_web_service.g_request_headers
    );
 
    -- Check if the response is not null
    IF l_response IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, 'Failed to generate PAR: null response');
    END IF;
 
    -- Parse the response
    BEGIN
        APEX_JSON.parse(l_response);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20002, 'Failed to parse PAR response: ' || SQLERRM);
    END;
 
    -- Get the access URI from the response
    l_access_uri := APEX_JSON.get_varchar2(p_path => 'parList[%d].accessUri', p0 => 1);
 
    -- Check if the access URI is not null
    IF l_access_uri IS NULL THEN
        RAISE_APPLICATION_ERROR(-20003, 'Failed to generate PAR: null access URI');
    END IF;
 
    RETURN l_access_uri;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20004, 'Failed to generate PAR: ' || SQLERRM);
END generate_par;

put_object_in_store

CREATE OR REPLACE PROCEDURE put_object_in_store(
    p_access_uri  IN VARCHAR2,
    p_blob_data   IN BLOB
) AS
    l_error_msg VARCHAR2(4000);
BEGIN
    BEGIN
        DBMS_CLOUD.PUT_OBJECT(
            object_uri => p_access_uri,
            contents   => p_blob_data
        );
    EXCEPTION
        WHEN OTHERS THEN
            l_error_msg := 'Error uploading object to store: ' || SQLERRM;
            -- Log the error
            DBMS_OUTPUT.PUT_LINE(l_error_msg);
            -- Re-raise the error
            RAISE_APPLICATION_ERROR(-20001, l_error_msg);
    END;
END put_object_in_store;

export_query_to_blob

CREATE OR REPLACE FUNCTION export_query_to_blob(
    p_sql_query  IN VARCHAR2
) RETURN BLOB
IS
    l_context   apex_exec.t_context;
    l_export    apex_data_export.t_export;
    l_blob_data BLOB;
BEGIN
    BEGIN
        l_context := apex_exec.open_query_context(
            p_location    => apex_exec.c_location_local_db,
            p_sql_query   => p_sql_query
        );
    EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, 'Failed to open query context: ' || SQLERRM);
    END;
 
    BEGIN
        l_export := apex_data_export.export (
            p_context   => l_context,
            p_format    => apex_data_export.c_format_csv
        );
    EXCEPTION
        WHEN OTHERS THEN
            apex_exec.close(l_context);
            RAISE_APPLICATION_ERROR(-20002, 'Failed to export data: ' || SQLERRM);
    END;
 
    l_blob_data := l_export.content_blob;
 
    apex_exec.close(l_context);
 
    RETURN l_blob_data;
EXCEPTION
    WHEN OTHERS THEN
        IF l_context IS NOT NULL THEN
            apex_exec.close(l_context);
        END IF;
        RAISE;
END export_query_to_blob;

B Using FTS: An Example

Overview

Functions and Procedures

get_idcs_token

generate_par

put_object_in_store

export_query_to_blob

Example Usage

Error Handling

Securely Obtaining an IDCS Token

Overview

Security Considerations

Using the get_idcs_token_for_planning_app Function

Example Usage

Using the export_query_to_object_store Procedure

Prerequisites

Usage

Customization

Example

get_idcs_token

generate_par

put_object_in_store

export_query_to_blob

Using the `get_idcs_token_for_planning_app` Function

Using the `export_query_to_object_store` Procedure