Category: PL/SQL

Example: an ORDS API that returns a user-configured JSON object

This actually came about from a Support Request. We were troubleshooting a JWT issue, that had ORDS in the mix. Ultimately, this is looking more and more like a networking issue (maybe something to do with routing rules between the load balancer and the backend server).

But in the course of our troubleshooting, we wanted to rule out some variables. One of those variables was the JWK URL we were using for the ORDS_SECURITY.CREATE_JWT_PROFILE procedure. So we had this idea to kind of fool ORDS into thinking it was visiting the IDCS (the predecessor to Oracle IAM) JWK URL when, in fact, it was just visiting another ORDS endpoint.

Obvious disclaimer: NOT FOR PRODUCTION USE. For troubleshooting purposes only. This article just serves as an example of how you can build TONs of ORDS APIs without always having to create a table, JSON collection, function, procedure, view, etc…

If you’ve seen my JWT + ORDS tutorials (one for OCI IAM, another for Microsoft Azure), then you know when you create an ORDS JWT Profile, it will look something like this:

BEGIN
  ORDS_SECURITY.CREATE_JWT_PROFILE(
      p_issuer => 'https://identity.oraclecloud.com/',
      p_audience => 'my_primary_audience' ,
      p_jwk_url =>'https://idcs-10a10a10a10a10a10a10a10a.identity.oraclecloud.com/admin/v1/SigningCert/jwk'
  );
  COMMIT;
END;
/

In the above code block, just know that p_issuer is your Identity Provider (e.g., IAM, IDCS, Microsoft Entra, etc.), p_audience is the Primary Audience (which you created when you registered your client application with your Identity Provider), and the p_jwk_url is where ORDS goes to look up the keys for decoding a JWT.

The eventual JWT that ORDS receives would include the Issuer and Audience, too. But it would also include other values such as the Scope. That Scope name would be associated with the ORDS Privilege–the privilege you created specifically to use for protecting/associating with your ORDS Resource Module. And ORDS would use all these values (and the JWK “keys” found at that JWK URL to decode the JWT) together to:

Verify you are who you say you are, and
You have the proper Scope (or as ORDS interprets it…the proper privilege) to access this protected resource

TL;DR This recap wasn't meant to explain in detail how JWTs work, but rather meant to demonstrate how everything is interconnected. ORDS can't do anything with a JWT unless an ORDS JWT Profile has been created beforehand. That JWT Profile itself works in tandem with the ORDS Privilege. And that Privilege is the same name as your Scope. So if the Privilege doesn't exist, the Profile is worthless. And if the Scope doesn't exist, well then certainly nothing is going to work! And until all that "stuff" is connected, then that protected resource cannot be reached.

Back to the use case. So, for troubleshooting reasons, we took that JSON object located at that JWK URL and recreated it as an ORDS endpoint. Let me point out that this is cool, but what you should take away from this is that there are tons of things you can do with ORDS, and you have a lot of control over how ORDS will send back information to you, your application, or your web client.

Here is how we structured the Handler code:

BEGIN
    HTP.P('{"
    keys": [
        {
            "kty": "RSA",
            "x5t#S256": "Lorem ipsum dolor sit amet__pellentesque elementum",
            "e": "AQAB",
            "x5t": "xwSmccaQZDvAZPOpZPHOiQDlLgo",
            "kid": "SIGNING_KEY",
            "x5c": [
                "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum==",
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum"
            ],
            "key_ops": [
                "verify",
                "wrapKey",
                "encrypt"
            ],
            "alg": "RS256",
            "n": 
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla"
        }
    ]
}');
END;

The complete PL/SQL Module Export (if you want to take a closer look):


-- Generated by ORDS REST Data Services 24.4.1.r0381713
-- Schema: ADMIN  Date: Thu Apr 03 07:10:42 2025 
--
        
BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'jwk',
      p_base_path      => '/jwk/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

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

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'jwk',
      p_pattern        => 'jwk',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'BEGIN
    HTP.P(''{"
    keys": [
        {
            "kty": "RSA",
            "x5t#S256": "Lorem ipsum dolor sit amet__pellentesque elementum",
            "e": "AQAB",
            "x5t": "xwSmccaQZDvAZPOpZPHOiQDlLgo",
            "kid": "SIGNING_KEY",
            "x5c": [
                "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent ' || 'placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum==",
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum"
            ],
            "key_ops": [
                "verify",
              ' || '  "wrapKey",
                "encrypt"
            ],
            "alg": "RS256",
            "n": 
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla"
        }
    ]
}'');
END;');

    
        
COMMIT;

END;

With that new ORDS endpoint, we (temporarily) recreated the ORDS JWT Profile with the new ORDS API as a proxy for our true JWK URL. Like this (this JWK URL is entirely fake, BTW):

BEGIN
  ORDS_SECURITY.CREATE_JWT_PROFILE(
      p_issuer => 'https://identity.oraclecloud.com/',
      p_audience => 'my_primary_audience' ,
      p_jwk_url => 'https://abcdefg-myadb.region-01.oraclecloudapps.com/ords/admin/jwk/jwk'
  );
  COMMIT;
END;
/

Hypothetically if you were to navigate to that ORDS URI, you’d see a facsimile of the actual JWK information:

So now, we’ve temporarily removed the JWK URL from the troubleshooting equation. However, we can still access that protected endpoint since we have a valid token and keys to decode it (courtesy of the ORDS endpoint we created)!

And this my friends, is the type of mad scientist stuff that I live for. I had to share our experience to underscore two main points:

You have a lot of flexibility with these ORDS APIs, and
When it comes to troubleshooting/root cause analysis, you must break things down into individual parts!

And that’s it for today. Class dismissed 🤓

BTW, I took some inspiration from here too. And of course, a link to the JWT section of our docs.

If you liked this, consider sharing. And if you have any cool or clever ORDS tricks you use, comment below!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

April 3, 2025

ORDS as a JotForm webhook: An actual customer use case
Recently, we (one of our Support engineers and I) worked with a customer who had integrated various ORDS POST APIs with a web-based form application called JotForm. JotForm offers integrations for numerous workflows, some official and others you can create independently. That’s where we come in.

In this example, a user would enter information into an online JotForm form, and when the user hits Submit, that would trigger a Webhook to an ORDS endpoint. But in this customer’s scenario, some of the data he was expecting wasn’t arriving in his database (he was on a 21c, but I tested these in a 23ai Autonomous database—the same still applies to both). For our customer, some of the fields in his table showed NULL.

A Webhook example

We wanted to understand better what was happening, so I created an account in Jotform and set up a Webhook similar to his configuration. This Webhook essentially forwards the JotForm form data to a target endpoint (an ORDS POST handler in our case).
```
NOTE: This isn't a JotForm tutorial. But to get the gist, below you'll see how I set up one of my Webhooks. 
```
Webhooks are weird

Here is what I don’t like about Webhooks (perhaps this is just my ignorance on full display):
1. What if the request doesn’t work or fails? Are reattempts automatic, or is that user information lost forever?
2. In this case, we have no real idea what is being sent to ORDS (at least not without some creative interrogation).
3. Without spending too much time with Webhooks, would an APEX front end not be more straightforward (i.e., same ecosystem and it is a direct POST as opposed to a Webhook that forwards a POST request to an POST endpoint…say that five times fast! More on this later…).
Where our problem starts

Here, we have an online form in JotForm. On the front end, I’ve set up three fields (these are all customizable in their web application):
1. Full Name, which consists of First Name and Last Name
2. Contact number
3. And a “Radio” button selection ¹
The Workflow

A user enters their information and clicks Submit. However, after inspecting the Console and Network tabs (in your Developer tools), you’ll notice two things:
1. No POST requests
2. No available request body (What does JotForm send to us?)
The absence of information is unsurprising since I’m working with a third-party tool (it’s a business, man!). There are enumerable reasons why you’d obscure information. Regardless of the cause, that obfuscation complicates matters when you want to ingest the form data into your database. You can follow the debugging advice to view the PHP output sent internally to JotForm. But I don’t know PHP, and I wouldn’t even know how to map PHP to JSON or SQL datatypes, for that matter.

ORDS Implicit Parameters to the Rescue

Here’s where you have to get creative. To better understand what was in that POST request, I relied on ORDS’ Implicit Bind parameters to understand what data was sent to us by the Webhook. Figuring this out required some trial and error on my part. But you can start with the :content_type bind parameter to see what kind of POST it is and go from there. In my example, I kept it simple and relied on the :content_type parameter and whatever was included in the POST body (up to this point, I wasn’t sure what to expect). I named that second value incoming_payload (which maps to the “PAYLOAD” column in the JOTFORM_DATA table)
```
INFO: I used the :body_json bind parameter, for the sole fact that neither the :body nor the :body_text parameters seemed to work for me. 
```
Resource Handler code

A look at the ORDS Resource Handler code:
```
-- The :content_type implicit bind parameter would "map" to the CONTENT_TYPE column in the 
-- JOTFORM_DATA table, while incoming_payload would map to the PAYLOAD column. 

DECLARE
    incoming_payload CLOB;
BEGIN
    incoming_payload := :body_json;
    INSERT INTO JOTFORM_DATA VALUES ( :content_type,
                                      incoming_payload );

END;
```
```
REMINDER: Remember, this is the endpoint I use in the "Webhook" integration in JotForm (i.e. the first two images at the top of this page).
```
Table DDL

You’ll notice a table in the handler code called JOTFORM_DATA. Well, I ended up creating a table of two columns, as seen here:
1. Content_type
2. Payload
```
CREATE TABLE JOTFORM_DATA 
    ( 
     CONTENT_TYPE VARCHAR2 (4000) , 
     PAYLOAD      VARCHAR2 (4000) 
    ) 
;
```
```
Why VARCHAR2(4000)?  Two reasons. First, its just that it seems like VARCHAR2 is catch-all/the most flexible data type for testing like this. Secondly, since the PAYLOAD might be JSON or possibly malformed JSON, if I decide, I can always use an "IS JSON" check constraint. But that can come later.²
```
Moving through the workflow

With the Resource Handler code in place and the table defined, we can test this Webhook and see what it sends across HTTP.

Next, a look at what my Autonomous database received:

Okay! So now we have something to work with. And we know at least two things:
1. The POST request is a multipart/form-data Content-Type, and³
2. The payload we received appears to be JSON
Next, we can copy the contents from the PAYLOAD column. And throw it into any number of online JSON Formatting tools, to see what we are working with:

Final thoughts

Well, I can already see why somebody might struggle with API integrations (in general, not just here). There is a lot to learn and A LOT of unknowns. Take this actual use case, for instance. Of course, you’d assume this payload to be multipart/form-data. You’d also expect the payload contents to be the JotForm form’s data. However, in this case, the form is pre-processed before it even reaches ORDS or the Autonomous database. So it is a good thing we did some investigation because there is way more “stuff” than we originally anticipated!

Next, we’ll have to parse through the payload within the payload. For that, we can also use the ORDS Implicit Bind parameters, Automatic Binding, and JSON_VALUE functions. But we’ll save that for the next post. Stay tuned!

Complete code
Click for code

You can find the most up-to-date code here. The code used in this post:
-- Table DDL CREATE TABLE JOTFORM_DATA ( CONTENT_TYPE VARCHAR2 (4000) , PAYLOAD VARCHAR2 (4000) ) ; -- ORDS Resource Module PL/SQL BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => '[Your username/schema]', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => '[same as p_schema in all lowercase]', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'ords_and_jotform', p_base_path => '/oj/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'ords_and_jotform', p_pattern => 'jotform_data', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'ords_and_jotform', p_pattern => 'jotform_data', p_method => 'POST', p_source_type => 'plsql/block', p_items_per_page => 25, p_mimes_allowed => NULL, p_comments => NULL, p_source => 'DECLARE incoming_payload CLOB; BEGIN incoming_payload := :body_json; INSERT INTO JOTFORM_DATA VALUES ( :content_type, incoming_payload ); END;'); COMMIT; END;
Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
1. What is a Radio button? ↩︎
2. Directly from the JSON Developer’s Guide Release 23, “You can store JSON data in Oracle Database using columns whose data types are JSON, VARCHAR2, CLOB, or BLOB.” Although, the guide then continues to state that, “Oracle recommends that you use JSON data type, which stores JSON data in a native binary format.” This is an easy change to make early on though. Should I choose to, I can adjust future table columns to accommodate the JSON data type. ↩︎
3. About the multipart/form-data Content-Type. This section discusses the multipart/form-data Content-Type, but also ExampleBoundaryString why it is used/included. You’ll see that in some of the screenshots throughout. ↩︎
December 31, 2024
An ORDS GET request to retrieve CGI Environment variables from the PRINT_CGI_ENV PL/SQL procedure
```
ALERT: This is going to seem extremely out of context! But this post actually loosely relates to the ORDS Pre-hook functions section of our docs. I'm in the process of (1) working on a presentation and (2) updating this section of the docs as well (productivity trifecta for the win!), hence why we are here. 
```
Hypothetical scenario

Hypothetically speaking, let’s say you were interested in learning more about Common Gateway Interface (CGI) Environment variables¹, what they are, and how to use ORDS to REST-enable a function to produce these variables. If that is the case, you are in luck, my friend!

What follows is a quick way for you to learn more about these variables (as they relate to the Oracle database) and use ORDS in the process!

An excerpt from another “work in progress”

For this example, we’ll rely on the OWA_UTIL PL/SQL package, specifically the PRINT_CGI_ENV procedure (an HTML utility; one of three utility subprograms in the OWA_UTIL package). First, create a Resource Module and Template. Then, when creating a Handler, choose plsql/block as the Source Type and use the PRINT_CGI_ENV procedure in the Handler code.

Like this:
```
Begin 
  OWA_UTIL.PRINT_CGI_ENV;
End;
```
I created this Resource Module on my “localhost;” your URI will differ in an Autonomous Database – Always Free account (sign-up here).

Remember you can export PL/SQL definitions.

In case you want to reproduce the Resource Module.

Save your code and Handler.
Sample PL/SQL code here
BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'ORDSDEMO', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'ordsdemo', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'scratch.pad', p_base_path => '/v1/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'scratch.pad', p_pattern => 'oga-cgi-example', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'scratch.pad', p_pattern => 'oga-cgi-example', p_method => 'GET', p_source_type => 'plsql/block', p_mimes_allowed => NULL, p_comments => NULL, p_source => 'begin OWA_UTIL.PRINT_CGI_ENV; end;'); COMMIT; END;
From there, either copy and paste this Handler’s URI (in the above example, that is https://localhost:8443/ords/ordstest/v1/api) into a new terminal session (if using a tool like a curl), or Postman (or a similar testing tool), or navigate to the URI in a new browser tab or window. You’ll see all the CGI Environment variables that are sent back (in an unauthenticated server response) to you, a client, or an application. Pretty neat trick, eh?

Here is an example of the response from an Autonomous Database – Always Free tenancy:

Here is a curl command response from a development configuration (i.e., A locally installed ORDS instance running in Standalone mode and a 23ai database in a Podman container).

About using the -k --insecure option in this curl command (HINT: to circumvent TLS for development purposes).
```
LEARN: Learn more about ORDS and Podman.
```
As you can see, there is tons of data to work with; something to remember if you want to use CGI Environment variables with your ORDS pre-hook (YOU DO NOT HAVE TO; I’m just showing you an example of one of the countless possibilities!).

Start small

You might want to start small by implementing a security policy using something as simple as the QUERY_STRING variable (e.g., where perhaps your ORDS prehook function calls upon an underlying function or procedure that uses a query string as a parameter). Our pre-hook example does something like this, actually 😀.

Check this out; look what happens when I append ?chris to the end of this URI:

And like magic, the QUERY_STRING CGI Environment variable now has a value assigned to it! See how simple and automatic this is?

Something to think about: even if you don’t care about CGI Environment variables today, I guarantee this will be useful in the future. I bet you’ve been in a position where at least some of this is relevant to you on any given week. So, if nothing else, maybe REST-enable this PRINT_CGI_ENV procedure, so you have it ready whenever you need it!

The end

That’s all for now, folks. This is a quick post that hopefully will come in handy one day 😎. Until next time, keep calm and query on.

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
1. All I could find (that was reputable and not simply Wikipedia) on CGI: CGI draft 1.1, W3.org references, CGI Programming on the WWW, mod_cgi (oh, the irony of me referencing the Apache HTTP server 🤣, please migrate if you haven’t already 🙏🏼) ↩︎
November 13, 2024

New ORDS feature: handling multiple files from a multipart/form-data POST request

A new feature

An ORDS user (or application) can now upload multiple files as part of a multipart/form-data POST request under various conditions. How can this be achieved with an ORDS endpoint?

NOTE: Before you continue, it might be helpful to refresh your memory on, or learn more about multipart/form-data as a Content-Type. Details here.

First, you must become acquainted with some newly introduced PL/SQL procedures and functions. You can find these in your ORDS Metadata.

From the SQL Worksheet, locate the ORDS_METADATA schema.

The single function and procedures used in this example.

The code

Now that you know where to look for these new procedures and functions, I’ll walk through my code.

Resource Handler

Here is the code I’m using for my Resource Handler (i.e., the ORDS API). It is a POST request that accepts the following parameters:

l_body_json
l_parameter_name
l_file_name
l_content_type
l_file_body

NOTE: These parameters shouldn't be confused with the required parameters of the procedures themselves. These are parameters that I'm declaring to be used later in the ORDS procedures.

Also, pay special attention to these three pieces of Resource Handler code:

Line 2	`L_BODY_JSON CLOB := :BODY_JSON`
Lines 9 and 10	`ORDS.BODY_FILE_COUNT`
Line 11	`ORDS.GET_BODY_FILE`

These are new additions to ORDS; we’ll be revisiting them shortly.

DECLARE
    L_BODY_JSON      CLOB := :BODY_JSON;
    L_PARAMETER_NAME VARCHAR2(4000);
    L_FILE_NAME      VARCHAR2(4000);
    L_CONTENT_TYPE   VARCHAR2(200);
    L_FILE_BODY      BLOB;

BEGIN
    HTP.P( 'Number of files that were received: ' || ORDS.BODY_FILE_COUNT);
    FOR i IN 1..ORDS.BODY_FILE_COUNT LOOP
        ORDS.GET_BODY_FILE(
            P_FILE_INDEX     => i,
            P_PARAMETER_NAME => L_PARAMETER_NAME,
            P_FILE_NAME      => L_FILE_NAME,
            P_CONTENT_TYPE   => L_CONTENT_TYPE,
            P_FILE_BLOB      => L_FILE_BODY
        );

        INSERT INTO BODYFILESDEMO (
            FILENAME,
            CONTENTTYPE,
            FILEBODY
        ) VALUES ( L_FILE_NAME,
                   L_CONTENT_TYPE,
                   L_FILE_BODY );
        HTP.P('Inserted File: ' || L_FILE_NAME );
    END LOOP;

END;

Expand for this example’s complete PL/SQL ORDS Resource Module definition.

-- Generated by ORDS REST Data Services 24.3.0.r2620924
-- Schema: ORDSDEMO  Date: Fri Oct 04 07:28:00 2024 
--
        
BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'body.files.demo',
      p_base_path      => '/v1/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'body.files.demo',
      p_pattern        => 'example',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'body.files.demo',
      p_pattern        => 'example',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE
    L_BODY_JSON      CLOB := :BODY_JSON;
    L_PARAMETER_NAME VARCHAR2(4000);
    L_FILE_NAME      VARCHAR2(4000);
    L_CONTENT_TYPE   VARCHAR2(200);
    L_FILE_BODY      BLOB;
BEGIN
    HTP.P( ''Number of files that were received: '' || ORDS.BODY_FILE_COUNT);
    FOR i IN 1..ORDS.BODY_FILE_COUNT LOOP
        ORDS.GET_BODY_FILE(
            P_FILE_INDEX     => i,
            P_PARAMETER_NAME => L_PARAMETER_NAME,
            P_FILE_NAME      => L_FILE_NAME,
            P_CONTENT_TYPE   => L_CONTENT_TYPE,
            P_FILE_BLOB      => L_FILE_BODY
        );

        INSERT INTO BODYFILESDEMO (
            FILENAME,
            CONTENTTYPE,
            FILEBODY
        ) VALUES ( L_FILE_NAME,
                   L_CONTENT_TYPE,
                   L_FILE_BODY );
        HTP.P(''Inserted File: '' || L_FILE_NAME );
    END LOOP;

END;');

    
        
COMMIT;

END;

And here is the table I’m using:

And the DDL, should you choose to recreate the table:

CREATE TABLE BODYFILESDEMO 
    ( 
     ID          NUMBER (*,0) GENERATED BY DEFAULT AS IDENTITY 
        ( START WITH 1 CACHE 20 )  NOT NULL , 
     FILENAME    VARCHAR2 (200) , 
     CONTENTTYPE VARCHAR2 (200) , 
     FILEBODY    BLOB 
    ) 
;

Practical example

In practice, here is how everything works. I’m using Postman as a proxy for my application (i.e., client); I have it set up like this:

Next, if I were to issue a POST request to my ORDS endpoint (using Postman), here are the results:

ORDS responds that the files were received (since I’ve coded it as such).

In short, where before, I couldn’t INSERT multiple files via an ORDS POST request, now I can.

Dissecting the POST

Using the :body_json implicit parameter (you can review all the ORDS implicit parameters here), in concert with the ORDS.body_file_count function and the ORDS.get_body_file procedure, you can now send multipart/form-data with files (as seen in this POST request). You might also include JSON form data in the POST request body in these requests, but it is not compulsory.

There are considerations though; read on.

A closer look

When POSTing a multipart/form-data request (such as the one in this example), I must bind this :BODY_JSON implicit parameter to something, even if that something will be an empty property (i.e., even if my POST request doesn’t have form data in JSON format).
ORDS interprets this handler code as such:
- ORDS knows to receive form data in JSON format; however, even if no form data is present, it knows that there may be multiple files in the POST request.
And if there are numerous files, ORDS can use the ORDS.BODY_FILE_COUNT function to count how many files there are (using the FOR LOOP) and then with the ORDS.GET_BODY_FILE function perform the next operation, which in this case is an INSERT INTO table operation.

DECLARE
    L_BODY_JSON      CLOB := :BODY_JSON;
    L_PARAMETER_NAME VARCHAR2(4000);
    L_FILE_NAME      VARCHAR2(4000);
    L_CONTENT_TYPE   VARCHAR2(200);
    L_FILE_BODY      BLOB;

BEGIN
    HTP.P( 'Number of files that were received: ' || ORDS.BODY_FILE_COUNT);
    FOR i IN 1..ORDS.BODY_FILE_COUNT LOOP
        ORDS.GET_BODY_FILE(
            P_FILE_INDEX     => i,
            P_PARAMETER_NAME => L_PARAMETER_NAME,
            P_FILE_NAME      => L_FILE_NAME,
            P_CONTENT_TYPE   => L_CONTENT_TYPE,
            P_FILE_BLOB      => L_FILE_BODY
        );

        INSERT INTO BODYFILESDEMO (
            FILENAME,
            CONTENTTYPE,
            FILEBODY
        ) VALUES ( L_FILE_NAME,
                   L_CONTENT_TYPE,
                   L_FILE_BODY );
        HTP.P('Inserted File: ' || L_FILE_NAME );
    END LOOP;

END;

TL;DR implicit bind parameter review

As far as a multipart/form-data with files POST request goes, ORDS “:BODY_" bind parameters now include and support the following:

Impact Bind Parameter	About files…	About form data…	About JSON…
`:BODY`	Accepts only one file (to be used with BLOB files).	Multiple files are accessible using the new `ORDS.BODY_FILE` function and procedures.	n/a
`:BODY_TEXT`	Accepts only one file (to be used with CLOB files).	ORDS will automatically recognize form data and handle it accordingly.	n/a
`:BODY_JSON`	ORDS will automatically recognize form data and handle it accordingly.	Form data will *NOT* be automatically recognized.	Will be treated accordingly where form data, in JSON format, exists in the `POST` body.

How ORDS now treats multipart/form-data with files POST requests under various conditions.

One final note

These changes only relate to multipart/form-data with files POST requests! Media types such as the following are not the focus of this article:

application/x-url-urlencoded
application/x-www-form-urlencoded
application/json, and
multipart/form-data without files

The end

That’s it for now. I’m sure there will be questions. I hope to put together a working example in a Flask app (a remix of our LiveLab workshop) soon. So stay tuned. I’ll update you as I learn more. Leave a comment if anything is unclear, and share if you think this is helpful.

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

October 7, 2024

Random Access Memories: ORDS and JWTs
```
EXTRA EXTRA! ORDS now supports JSON Web Tokens (JWTs). You can find most of the details in the OAuth PL/SQL Package Reference chapter of the ORDS Developer's Guide. 
```
Why is this in the OAuth chapter?

Apparently, JWTs fall under the purview of the OAuth Working Group, a section of the Internet Engineering Task Force (IETF). Here is a draft of the JWT specification I found. This makes sense; I’ve since relearned that OAuth = Open Authorization 🤦🏻.

ORDS JWT OAUTH parameters

You’ll notice two new procedures in that package: OAUTH.CREATE_JWT_PROFILE and OAUTH.DELETE_JWT_PROFILE. After getting acquainted with them, I wanted to highlight three parameters of the OAUTH.CREATE_JWT_PROFILE procedure:
- p_issuer
- p_audience
- p_jwk_url
Your JWT issuer (e.g., Microsoft Entra or Oracle Identity Cloud Service) will provide you with these three values required for the OAUTH.CREATE_JWT_PROFILE procedure.

However, they might be referred to by slightly different names. I first noticed this as I set up Microsoft Entra to work with ORDS (below are images taken from a slide deck I’m working on).
Learn more about these parameters.
- About Registered Claim Names (this includes “iss” and “aud“)
- About JSON Web Keys (JWKs)
```
In fact, I could use some help finding [what we refer to as] the p_jwk_url in the Microsoft Entra dashboard. I'm sure it's there, but I couldn't find it. In case you need it, this is the link that I used.
```
So, the names are all slightly different. But if I can figure it out, you definitely can.

Decoding JWTs

Once you acquire your JWT, you’ll need a way to decode it so you can use it for testing or development, and you’ll need some of the information for the ORDS profile procedure! Several resources exist, but here is a [non-exhaustive] list I put together:
- the JWT.io debugger
- Microsoft’s JWT debugger
- Comprehensive list of libraries for various languages and frameworks
If you choose to use a web-based decoder, it’ll look like this (paste your Token):

But you might prefer something other than putting your JWT into a browser for decoding.

Homegrown

So, if you’re like me (and didn’t do your research beforehand), you might try to come up with something independently. Something you can run locally.

I created a JavaScript function that expects a JWT and “splits” on the periods. From there, Base64 decodes the necessary stuff for you and returns it:
```
function decodeJwt(newjwt) {
    var headerJwt = newjwt.split(".")[0];
    var headerJwtdecoded = atob(headerJwt);
    
    var payloadJwt = newjwt.split(".")[1];
    var payloadJwtdecoded = atob(payloadJwt);

    var signatureJwt = newjwt.split(".")[2];
    // var signatureJwtdecoded = atob(signatureJwt);

    // var signatureJwtBase64 = signatureJwt.replace(/-/g, "+").replace(/_/g, "/");
    // var signatureJwtBase64decoded = atob(signatureJwtBase64);

    console.log(headerJwt, payloadJwt, signatureJwt);

    console.log(headerJwtdecoded, payloadJwtdecoded);

    return(headerJwt, payloadJwt);
  };


decodeJwt("Your JWT goes here.");
```
To illustrate how this function works, I took some “boilerplate” HTML from the Bootstrap docs page and spun up a LiveServer in VS Code. I’m also “inspecting” the results from the console.log();. I’m not really sure how far I’ll take this, especially now that I’ve learned about all the existing libraries. But feel free to remix this code!

Thank you for your time 🙇🏻‍♂️

And that’s all I have to share for today!

If you still need to download and upgrade to the latest ORDS, you can find the.zip file here. Be sure to explore GraalVM, too; you can “unlock” some newer ORDS features by using GraalVM as your primary Java Developer Kit (JDK)!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
May 2, 2024
Tinkering: a SQL script for the ORDS_ADMIN.ENABLE_SCHEMA procedure
Post-ORDS installation

Once you’ve installed ORDS, you need to REST-enable your schema before taking advantage of ORDS (I used to forget this step, but now it’s like second nature).
```
RESOURCES: I've discussed ORDS installation here and here. I'd check both pages if you're unfamiliar with it or want a refresher. 
```
ORDS.ENABLE_SCHEMA / ADMIN_ORDS.ENABLE_SCHEMA

While logged into your database with SQLcl, you can issue the following commands to not only create a new user but grant them the required Roles (and the underlying Privileges) and REST-enable their schema (aka “ORDS-ifying” a schema):
```
/* Remember to remove the brackets when you run this code */ 

Create User [username] Identified By [password];
Grant Connect to [username];
Grant Resource to [username];

Grant Unlimited Tablespace to [newuser];

/* This PL/SQL procedure assumes you are logged in as the SYS.
If you are logged in as that new user, AND HAVE BEEN GRANTED
THE DBA ROLE, then you can execute the ORDS.ENABLE_SCHEMA 
procedure */

Begin
    ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
End;
/
Create User [username] Identified By [password];
Grant Connect to [username];
Grant Resource to [username];

Grant Unlimited Tablespace to [newuser];

Begin
    ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
    Commit;
End;
/
```
Automate because lazy

But even that is too much work for me, so I took a stab at automating this via an SQL script. As you can see, the above commands are simple and repeatable (a perfect candidate for automation). And since I’m constantly adding and dropping users for various reasons, copying and pasting code from an old blog or writing everything by hand gets annoying. Additional reasons for automating:
1. laziness
2. a desire to improve SQL and PL/SQL skills
3. an interest in scripting
4. I get easily distracted
The script

After about a day and a half, I have a working prototype script to call upon when I’m on the SQLcl command line. Here is what I came up with:
```
NOTE: If you just came here to remix the code, I have this in the scripts folder in my GitHub blog repo as well. Please feel free to sample it and/or roast it/me 🔥. But if you keep scrolling, I'll go into more detail section-by-section.
```
```
18-OCT-2023 UPDATE: I've slightly changed this code to include the Commit; command in the PL/SQL portion of the script. You'll see that reflected in line 44. Thanks René 🙌🏻!
```
```
INPUT
PROMPT Choose a new database username:
ACCEPT NEWUSER CHAR PROMPT 'Enter new user name hurrr:'
PROMPT Choose a temporary password for &&NEWUSER: 
ACCEPT NEWPASS CHAR PROMPT 'Make it super secret:'

/*
I wish I could figure out a way to ONLY ask for username > check 
that against existing database users > AND THEN allow a user to 
continue with the 'choose password' step. I was only able to figure 
out how to ask for the username and password AND THEN checks 
against the database. I stole the code from this thread: 
https://stackoverflow.com/questions/30710990/creating-an-oracle-user-if-it-doesnt-already-exist 

Currently, its just extra steps for the user; kind of annoying. If you're 
reading this and can figure out a way to get this working, let me know! 
I'll make the change and attribute you in the comments :) 
*/

Set Verify On 

/*
You can refer to section 6.3.10.11 for more details on this 
SET VERIFY OFF command 
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-3ACD41F3-A5A2-48D5-8E81-C29F9C14C865
*/

/*
The difference between using single and double ampersands: 
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-C6BE6E41-821F-413E-B4B1-56AAE4A46298
*/

Declare
check_if_user_exists Integer;
plsql_block VARCHAR2(500);
NEWUSER VARCHAR2(20) := '&&NEWUSER';
Begin
  Select count(*) Into check_if_user_exists From dba_users Where username=NEWUSER;
  If (check_if_user_exists = 0) Then
  Execute Immediate 'Create User &&NEWUSER Identified By &&NEWPASS';
  Execute Immediate 'Grant Connect To &&NEWUSER';
  Execute Immediate 'Grant Resource To &&NEWUSER';
  Execute Immediate 'Grant Unlimited Tablespace To &&NEWUSER';
  plsql_block := 'Begin ORDS_ADMIN.ENABLE_SCHEMA(p_schema => :1); Commit; End;';
  Execute Immediate plsql_block using NEWUSER;
  End If;
End;
/

/*
The p_schema parameter is mandatory, that's why I'm including it. 
If you omit the other parameters, the procedure will use the default 
parameter values. 

Learn more about this procedure here: 
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-459B8B6F-16EC-4FEC-9969-E8231668AD85

I was able to get this entire thing to work through trial-and-error, 
while also using this for reference: 
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm
*/

PROMPT
PROMPT
PROMPT Congrats 🎉 the user: &&NEWUSER, with the password: &&NEWPASS is now a bona fide database user 🙌🏻! 
PROMPT Not only that, &&NEWUSER can log into Database Actions and REST-Enable their database objects too 😍!
PROMPT
PROMPT
PROMPT Click RETURN to return to the SQLcl prompt. And NEVER forget:
PAUSE "You're good enough, you're smart enough, and doggone it, people like you!"
```
```
PROPS: I owe much credit to this StackOverflow post and Jon Heller's and Mark Bobak's comments.
```
Breaking it down

Starting up podman

I’ll first start up podman and cd in the correct directory. “Correct” insomuch that this is where my ordsuserl.sql script lives.

ordsuser.sql, the script I’ll be working with.

Once my container status displays healthy, I’ll execute the ords serve command. This will launch ORDS in standalone mode (using the embedded Jetty server). I’ll use ORDS in a few minutes, so I should prepare it now.

Checking the container health.

Issuing the ords serve command.

ORDS is ready.

Prompting the user

I’ll then log into my podman container using the following string:
```
sql sys/password1234@//localhost:41465/ORCLPDB1 as sysdba
```
But why not the podman exec command?
```
NOTE: I'm logging in as SYS (I've since been informed you shouldn't really be doing this as SYS; in fact, it looks like you shouldn't be doing this as SYSTEM. Tom has some good comments in this post here. 
```
Why am I not using the podman exec command here?

If you’ve seen my latest post on podman ports and networking, this command contradicts that entire article. There are actually two ways (maybe more, if I’m unaware) you can connect to your Oracle database in a podman container. The first way is to simultaneously hop on over to the container and sign in from inside that container. Your connection string would look like this:
```
podman exec -it 21entdb sql sys/password1234@//localhost:1521/ORCLPDB1 as sysdba
```
The second option is to sign in remotely like I’m doing in this current example:
```
sql system/password1234@//localhost:41465/ORCLPDB1
```
This is analogous to when you SSH into a machine remotely. I should have mentioned it in this recent YUM/Oracle Linux post. Still, when you create a Compute Instance, you can later SSH into that Instance and perform actions like you usually would in the Terminal or the Command Prompt. But instead of being on your own machine, you’re on a remote machine. Ports are very confusing (for me, at least), so please read that podman ports post.

When you use the exec command, consider yourself on another computer on the Linux operating system. Once there, you must log in using the 1521 port because that is where the database’s TNS Listener (deep dive on Oracle database connection) is. However, when you are outside that container (i.e., that machine’s Linux OS), you need to use your local port (in this case, 41465) because it essentially acts as a proxy or a pass-through to the container’s 1521 port. Savvy 🏴‍☠️?
```
DISCLAIMER: This my best-effort attempt at explaining this confusing concept. It is subject to change. But I really want people to take advantage of our tools in the Oracle Container Registry, so I hope this helps! 
```
Hath connected to the database

Once I’m in, I can call upon my script to quickly create a new user and REST-enable their schema (recall, I “cd” into the correct directory in an earlier step). The syntax:
```
@ordsuser.sql
```
```
MORE SQLcl: You can read the different SQL, SQLcl, and PL/SQL commands here.
```
After pressing Return/Enter, a prompt will appear; this is what it looks like on the “front end”:

Notice the prompts in those first four lines.
```
HINT: I would also spend some time here, learning about the PROMPT, ACCEPT, and INPUT commands. Yes, this comes from the SQL*Plus documentation, but this works as you'd expect in SQLcl.
```
Meanwhile, here is the corresponding section in the script:

The first section of the SQL script.

Once I enter the password and press Enter/Return on my keyboard, the rest of the script is automatically executed using the provided username and password as substitution variables for the rest of the script! There is an in-depth explanation in the docs here, but you should grab the code and tinker with it to see how everything interacts and works together.
```
NOTE: The Set Verify On command displays the changes made from the original PL/SQL procedure and the updated PL/SQL procedure (with the updated username and password). It isn't necessary, but I wanted to provide some feedback to a user. 
```
PL/SQL procedure

Assuming the user (i.e., the one you selected) doesn’t exist, the PL/SQL procedure should execute without issues. In the following image, you can see what is happening in real time:
1. A new user is created with the assigned username and password
2. That user is granted the Connect and Resource roles
3. The schema is then REST-enabled using the ORDS_ADMIN.ENABLE_SCHEMA PL/SQL procedure
Why NEWUSER in the Declare block?

Yeah, good question. At a minimum, you need to include the p_schema in the ORDS_ADMIN.ENABLE_SCHEMA procedure. Read up on that here. But, I wanted the username to remain in lowercase since that will later be used for my schema’s URIs (e.g., http://localhost:8080/ords/ordstest/emp/).

So I quickly taught myself/attempted a crash course on define variables and bind arguments; most of what I learned came from this EXECUTE IMMEDIATE documentation. And that’s why you see this in lines 25 and 34 of the code:
```
NEWUSER VARCHAR2(20) := '&&NEWUSER'; 

/* as well as */

ORDS_ADMIN.ENABLE_SCHEMA(p.schema => :1);
```
You can see the interaction between lines 25 and 34.

And on the front end, courtesy of the Set Verify On command, you’ll see that updated block of code:

The username and password fields are updated.

Feedback

I’ll then provide myself (or the user) with helpful feedback (along with words of encouragement). Once satisfied, I can click the Return/Enter key to exit the script and sign in to Database Actions as that new user.

The end of the script, after pressing Return/Enter.

Exiting from SQLcl.

The corresponding section in the script looks like this:

I’m cheating by adding PROMPT to give line breaks.

Sign-in to Database Actions

Now, I can navigate to the Database Actions sign-in page at localhost:8080/ords/sql-developer. If I wanted to, I could also navigate to the newly introduced landing page at http://localhost:8080/ords/_/landing (obviously, depending on your deployment, this address will differ).

The landing page you’ve probably seen before.

Signing in with the ordtest user I created with the script.

This is our recently introduced landing page with more options.

SQL Worksheet then the proof

I’ll head to a SQL Worksheet, select All Objects in the Navigator tab, and a clean schema ready to take on the world!

Navigating to a SQL Worksheet.

Selecting All Objects in the Navigator Tab.

Summary

And this marks the end of today’s lesson. So what did we learn?
1. You can execute SQL scripts directly from the SQLcl command line.
2. My script is cool, but I wish I could verify if a user exists sooner (I end up forcing the user to go through that password step).
3. The script has no exception handling (I’m giving myself a pass on this since I’m just now getting into PL/SQL).
4. Spend some time with the documentation on one screen and your script on another. After some time, you can actually understand how everything interacts.
One final thought. My process required a lot of trial and error, but seeing how everything flows and works is entertaining.

Please sample/remix my code; it’s located in my GitHub blog repo. Make it better, and let me know what you come up with! And be sure to check out ORDS and SQLcl 😍!

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
- YouTube
- LinkedIn
- Instagram
- TikTok
- GitHub
- Mail
- Facebook
- Medium
October 17, 2023