Using Declarative APEX REST Data Sources via PL/SQL APIs #JoelKallmanDay

I have written and done several APEX Instant Tips about the topics of using the declarative features of APEX (instead of writing code) and about using APEX REST Data Sources:

• APEX Instant Tips #180: Deploying APEX applications that include REST Data Sources

• APEX Instant Tips #99: Connecting an APEX app to GPT-3

• APEX Instant Tips #98: An easier way to give secure access to a REST enabled schema

• APEX Instant Tips #92: Using RESTful services to synch data during deployment

• APEX Instant Tips #41 How to secure a RESTful API with ORDS and OAuth2

• APEX Instant Tips #27 REST Data Synchronization, and an homage to Joel ( #JoelKallmanDay )

• APEX Instant Tips #130: Downloading files (mostly) declaratively

The most recent tip on these topics (180) covers deploying APEX applications with REST Data Sources. It highlights how much the APEX team takes into account the lifecycle of an application. When using a declarative APEX REST Data Source, you get all of the benefits described in AIT episode 180.

What if you need to call a REST service from PL/SQL, though? This may be during an automation or for a host of other reasons. It may even be code that is unrelated to an APEX application. I still recommend you create an APEX application and a declarative REST Data Source. You can then reference the declarative REST Data Source in your PL/SQL code. In the end you will get all of the benefits of the declarative REST Data Source and have less custom code to maintain. Let’s take a look at what would be required to call the REST service defined in AIT 99—a call to ChatGPT—with and without APEX.

Without APEX Components

Skip to “With APEX Components” if you just want to get to the “right” way to do it.

Without a declarative REST DATA Source (or any APEX features) I would need the following:

1. A way to securely store the web credential

2. A way to store the REST endpoint

3. A way to have both of those be different in DEV, TEST, and PROD

4. A way to call the REST endpoint

Securely storing web credentials is something I never want to be responsible for. I want to rely on a credential store that has been created for this purpose. If I’m not using APEX to store the web credential, I could use the Oracle Wallet associated with the database. Most people, though, tend to store this in a table or even just hard-code it into a PL/SQL package. Entirely unacceptable. So, right away, we have some work to do here. This post is about how to do things the right way, so I’m not going to waste your time or mine describing this further.

Storing the REST endpoint is less problematic in that it doesn’t have the same security implications, but you do have to consider how to maintain it in different environments. So, probably, you will create a table for this and update the table in each environment. Again, totally taken care of by APEX when done declaratively.

Finally, a way to call the endpoint. In this case you could use the UTL_HTTP API to create your own calls to the endpoint. More likely, though, you would still use APEX by calling the APEX_WEB_SERVICE API. During this exercise, you might stumble upon the fact that you can use an APEX Web Credential in your APEX_WEB_SERVICE API calls. This would be great! You could get rid of the issues associate with storing a web credential.

I’ve done it in all of the variations listed above—starting before APEX existed. This method ranges from a few hundred lines of code to a few thousand, depending on how fully you want to implement the solution. Every line of code costs money to write and to maintain.

With APEX Components

Obviously, I recommend using APEX declarative components to the maximum extent possible. Using the ChatGPT example from AIT 99, you would simply create the APEX REST Data Source as shown in the episode. In this case, I have given the REST Data Source a static ID: OpenAI___GPT3. Th code below calls the REST Data Source from within the application:

declare
    l_params apex_exec.t_parameters;
begin
    apex_exec.add_parameter( l_params, 'PROMPT', apex_json.stringify(:P2_PROMPT));
    apex_exec.add_parameter( l_params, 'TEMP', :P2_TEMP );

    apex_exec.execute_rest_source(
        p_static_id        => 'OpenAI___GPT3',
        p_operation        => 'POST',
        p_parameters       => l_params );

    :P2_RESPONSE := apex_exec.get_parameter_clob(l_params,'RESPONSE');

end;

If you are not calling the REST Data Source, the APEX documentation for APEX_EXEC gives the following advice:

All APEX_EXEC procedures require an existing APEX session to function. In a pure SQL or PL/SQL context, use the APEX_SESSION package to initialize a new session.

This just means you need to create an APEX session prior to to calling APEX_EXEC (and delete or detach after):

declare
    l_params apex_exec.t_parameters;
begin

    apex_session.create_session (
      p_app_id   => 123456, -- possibly look this up based upon the APP Alias
      p_page_id  => 1, -- fine to hard code this
      p_username => 'EXAMPLE_USER' ); -- also fine to hard code this

    apex_exec.add_parameter( l_params, 'PROMPT', apex_json.stringify(:P2_PROMPT));
    apex_exec.add_parameter( l_params, 'TEMP', :P2_TEMP );

    apex_exec.execute_rest_source(
        p_static_id        => 'OpenAI___GPT3',
        p_operation        => 'POST',
        p_parameters       => l_params );

    :P2_RESPONSE := apex_exec.get_parameter_clob(l_params,'RESPONSE');

    apex_session.delete_session;
end;

That’s less than 20 lines of code. It requires no custom database objects aside from the PL/SQL package this is stored in. You can easily use APEX_DEBUG messages. In my book declarative wins.