Tag: Oracle REST Database Services

  • 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).

    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 1

    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;

    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.2

    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, and3
    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!

    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. ↩︎

  • ORDS 24.4 Release Highlights

    ORDS 24.4 Release Highlights

    Pre-Authenticated endpoints

    Using the new ORDS_PAR PL/SQL package, users can create, revoke, issue and set tokens and token life for specific resources. Your REST-enabled schema will automatically have access to this new feature in 24.4. You can execute these functions and procedures from within the Database Actions SQL Worksheet, the SQL Developer for VS Code extension, the SQL Developer desktop client, and SQLcl (new version out) too!

    A mini-tutorial

    Here is an easy way to test these new PAR functions and procedures from the SQL Worksheet. First, Select the ORDS_METADATA schema from the SQL Worksheet Navigator. Then, select “All Objects,” scroll down to, and right-mouse-click on the ORDS_PAR PL/SQL package. Then select Run.

    The first function you see will be the DEFINE_FOR_HANDLER function. Enter your details in the required fields, and execute the code in the SQL Worksheet. You’ll see a new URI populate.

    You can now share that URI (or use it in your test). And it will remain valid for however long you set for the P_DURATION parameter.

    A reminder of where you can locate all these required fields

    Navigate to the REST Workshop; choose your target Resource Module, then…

    ORDS Central Configuration

    ORDS Central Configuration now natively supports pool identifiers in URLs. We still have the Header method of mapping for Central Configuration. But now we support the Request Host Method, too. For instance, if your Global configuration’s (when using a Central Configuration deployment) URI is:

    https://central-config.example.com:8585/central/v1/config/pool/{host}

    You can issue a GET request (something like this, perhaps) to:

    curl https://my_database_pool.localhost:8080.com/ords/hr/employees/

    Your ORDS Central Configuration will take that database pool “prefix” and use it to “look up” that database pool’s settings (in the Central Configuration server). From there, your ORDS instance would have both Global and Pool configuration settings, and it would then be able to satisfy the above GET request.

    Previously, to “inform” the Central Configuration of the {host} value (the URI you see in the first code block), you’d have to pass in a request header. YOU CAN STILL DO THIS! But we support both methods now. Depending on your use case, you may prefer one method over the other. Details here.

    Plain-text in XML Files

    ORDS will notify users when plain-text “secrets,” such as passwords, are present in the ORDS configuration XML files. What does this look like? You can “test” this new functionality by retrieving something like the db.password configuration property.

    Typically, ORDS looks for this value in the cwallet.sso file, but you can add it here (it will just be redundant).

    We then warn you not once but twice! Once when ORDS first starts up and then again when it fully initializes.

    A new ORDS CLI option

    The ORDS CLI now includes a script-friendly --quiet option, which hides banner, copyright, and configuration location information from the ORDS STDOUT.

    Here is an example where we use the standard command:

    ords config get db.servicename

    Followed by the much leaner version:

    ords config --quiet get db.servicename

    As you can see, this makes it much easier for scripts and automation tools to navigate the ORDS STDOUT.

    APEX updates to ORDS access logs

    Standalone access log records now include an APEX app_id and APEX page_id for records, where applicable. The end of the log captures both app_id and page_id (in this example, 4550:1, respectively).

    [0:0:0:0:0:0:0:1] - [21/Nov/2024:17:37:42 +0000] "POST /ords/wwv_flow.accept?p_context=workspace-sign-in/oracle-apex-sign-in/12558700474753 HTTP/1.1" 200 6494 "http://localhost:8080/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36" 1362 localhost:8080 - 4550:1

    In cases where APEX is absent, dashes will replace these fields.

    JSON Syntax highlighting

    When viewing JSON data from a query result in the SQL Worksheet, pretty printing, and JSON syntax highlighting are now applied. In this example, you’ll notice a single-row table with the column “Glossary.” Clicking the “Eyeball” icon on the Select * From... results reveal this gorgeously formatted and highlighted JSON object.

    Click to add Implicit, Handler parameters

    You can now add user-defined parameters (i.e., Handler parameters) and Implicit parameters to Handler code blocks with a single mouse click of the parameter name. Take a look at the examples below:

    SQL Worksheet file functionality

    You can now rename SQL Worksheet files (from within Database Actions and the OCI console). This update applies to browser files and OCI object storage files. You can now open, rename, and trash these files.

    And those are just some of the highlights. But be sure to review the links below!

    The links

    1. Download ORDS /latest today.
    2. Read the complete, official Release Notes.
    3. Read the latest edition of the ORDS Developer’s Guide and ORDS Installation/Configuration Guide.
    4. Check out our latest ORDS npm samples.

    And that’s all for now! Enjoy 😊

    Follow

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

  • ORDS WARNING Cannot find the real static path of

    ORDS WARNING Cannot find the real static path of

    Problem description

    You’ve just upgraded ORDS and issued the ords serve command. As ORDS is starting up, you see the following warning (or something like it):

    WARNING Cannot find the real static path of https://static.oracle.com/cdn/23.2.0/

    How to fix

    Easy! In this case, I removed the standalone.static.path property of the ORDS Global configuration settings.

    Removing ORDS configuration settings

    You can remove/add/update ORDS configuration settings in two ways:

    1. Manipulating the Global settings.xml file directly, or
    2. Using the ORDS CLI
    NOTE: Using the ORDS CLI is the preferred method. I cannot stress this enough. 

    Whenever an ORDS installation or upgrade displays errors or warnings, go straight to the ORDS upgrade logs. Review the associated log for anything out of place and anything relevant to the warning or error you observed. In this case, the error pointed to two clues:

    1. APEX
    2. “static path”
    REMEMBER: When you are troublshooting, you are on a fact-finding mission. You're looking for clues that can help you solve a mystery. 

    Here, I see that APEX isn’t even installed in my target database. Perhaps Chris was trying to do something with/in APEX in the past and got distracted.

    Since it looks like I haven’t installed APEX, I feel comfortable ruling out APEX as the culprit.

    More than likely, this is pointing toward user error as the culprit. Perhaps it was some setting that I applied incorrectly. Next, I’ll inspect the Global configuration settings for ORDS. I can do this in two ways:

    1. A visual inspection of the settings.xml file
    2. Or using the ORDS CLI

    Visual inspection

    Immediately, you can see the offending configuration property: standalone.static.path. This property “specifies the path to the folder containing static resources required by APEX.”1 Since I am reasonably sure that APEX doesn’t exist, nor does any APEX metadata exist in my database, I can test my theory by removing the property from the settings.xml file.

    I can do this pretty quickly in a text editor. However, if you are skittish about manipulating these files directly (and rightfully so), then use the ORDS CLI (this is the preferred method anyway).

    Updating settings with the ORDS CLI

    In this case, you’ll see a fictitious standalone.static.path directory, /123456/abcdef.

    You can easily remove this, and other settings with the delete option.

    The command used in this example:

    ords config delete standalone.static.path

    Once you’ve made the change, close out your Terminal session and start a new one. Closing out your session and restarting with a new one is good practice to ensure that your Terminal session recognizes any configuration changes you’ve made.

    I next issued the ords serve command and ORDS started up with no problem.

    And that is how we fixed this warning in this oddly specific scenario. I hope this helped!

    And one more thing

    You may have noticed I’m on ORDS Version 24.4. That is because I was using a pre-release version that will be available when this post is published. So, if you haven’t done so yet, download the latest ORDS here.

    Follow

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

    1. See Table C-1 in the ORDS Installation and Configuration Guide. ↩︎

  • ORDS Standalone: specifying a response header with jetty.xml

    NOTE: A Load Balancer or Reverse Proxy can achieve this same result. If your current ORDS deployment consists of either, you may prefer to add header "rules" there instead.
    Should you choose to operate ORDS in Standalone mode, you can rely on the Jetty server to provide this header rule. See the this section of my latest article for configuring the /etc folder.

    Start here

    Once you have created the /etc folder, save the following code block as a XML file using an easily recognizable file name.

    <?xml version="1.0"?>
    <!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
    <Configure id="Server" class="org.eclipse.jetty.server.Server">
      <Call name="insertHandler">
      <Arg>
        <New class="org.eclipse.jetty.rewrite.handler.RewriteHandler">
          <Get id="Rewrite" name="ruleContainer" />
          <Call name="addRule">
            <Arg>
              <New id="header"  class="org.eclipse.jetty.rewrite.handler.HeaderPatternRule">
                <Set name="pattern">*</Set>
                <Set name="name">Strict-Transport-Security</Set>
                <Set name="value">max-age=31536000;includeSubDomains</Set>
              </New>
            </Arg>
          </Call>
        </New>
      </Arg>
    </Call>
    </Configure>

    In this example, we use jetty-response.xml as the file name. With this file included in the /etc directory, ORDS will “pick up” this configuration setting during runtime.1

    Some details of this file

    This jetty-response.xml file will enable ORDS Standalone to include the Strict-Transport-Security header name and its values max-age=3153600;includeSubDomains in each response to a request.2 In lay terms this XML file establishes a new Jetty response header, named Strict-Transport-Security, it applies to its responses for all requests (denoted by the *), and this header’s value is comprised of the following:

    • max-age=31536000;
    • includeSubDomains

    To illustrate this behavior, consider the following curl command and subsequent response. A request is sent to a resource (in this case /departments_json) and the subsequent ORDS response includes:

    1. a JSON payload
    2. the typical/standard headers, and
    3. additional headers indicated in the jetty-response.xml file

    And that’s it, you’ve yet again customized the Jetty server (that is responsible for ORDS Standalone)!

    That’s it for now

    Similarly to the Jetty Access Log example, these XML files can be a quick and easy way to introduce additional functionality into your ORDS Stand-Alone deployment. What else can you imagine? Take a look at the Jetty APIs for inspiration. Did you know you can extend ORDS with plugins, too?

    And of course, obligatory ORDS resources: Download ORDS | Oracle ORDS forum | ORDS Docs | ORDS playlist

    Follow

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

    Footnotes

    1. What the hell is runtime? Having no formal education in software engineering, my understanding is that runtime has to do with the execution of a program. Runtime relates to the initial and continued execution of the program. In the case of these XML files, the instructions therein are not formally part of the Jetty server but are included in the instructions when you issue the ords serve command. Doing so effectively starts up the Jetty web server. Jetty then recognizes there are files in the /etc folder and includes them when it enters into “runtime” or the “runtime environment.” This Wikipedia post is a great place to start. But I certainly wouldn’t use that as the “official” definition. This stackoverflow thread is extremely helpful as well. ↩︎
    2. Strict-Transport-Security (about this header) is a response header. This header is used to inform the browser that HTTPS should only be used to access ORDS resource/s. You’ve probably seen * used in the ORDS documentation. In this case, <Set name="pattern">*</Set> found in the XML file is used * as a wildcard (i.e. I interpret this as “apply this rule to everything and anything.”). The <Set name="value">max-age=31536000;includeSubDomains</Set> line includes the “directives”: max-age=3153600; and includeSubDomains. Examples of subdomains would be something like: en.wikipedia.org, where en (English language) is a subdomain of wikipedia.org; more details here. ↩︎

  • ORDS Standalone access logs: Jetty and customizing with XML

    ORDS Standalone access logs: Jetty and customizing with XML

    Did you know that you can very quickly and easily enable access logging when you are running ORDS in standalone mode (i.e., when using the embedded Jetty server)?

    Proposed/updated section

    5.2.3 Using Jetty XML Configuration Files

    This section describes how to configure the ORDS Jetty server (i.e., Standalone mode) for additional functionality using Jetty XML configuration files.

    Some background

    When ORDS is in Standalone mode (i.e., relying on the embedded Jetty server as its web server), ORDS can detect and “pick up” user-provided configuration settings found in the [ORDS configuration directory]/global/standalone/etc directory.

    NOTE: You must create the [ORDS configuration directory]/global/standalone/etc directory. The /etc directory is not part of a standard ORDS configuration.

    5.2.3.1 Jetty Access Logs

    NOTE: Beginning with ORDS version 24.1, the standalone ORDS Access Log format was updated to include additional format codes.2
    ORDS versionsJetty Format codes usedAccess Log example
    23.4 and earlier“%h %l %u %t “%r” %s %b”127.0.0.1 – frank [10/Oct/2000:13:55:36 -0700] “GET /ords HTTP/1.1” 302
    24.1 and later“%{client}a %u %t “%r” %s %{CLF}O “%{Referrer}i” “%{User-Agent}i” %{ms}T %{Host}i”192.168.122.1 – [27/Mar/2023:23:00:07 +0000] “GET /ords/ HTTP/1.1” 302 – “-” “Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/111.0” 132 192.168.122.149:8080
    Comparing ORDS access log formats pre- and post-version 24.1.

    ORDS Standalone access logs will automatically be enabled once an access log location has been configured. You can enable Standalone logging by executing the following command:

    ords config set standalone.access.log [/Path to the access log location of your choosing]

    After issuing this configuration command, the ORDS CLI will echo back the location. You can review your configuration settings by executing the ords config list --include-defaults command.

    You will also see a new <entry></entry> has been saved to your /global/standalone/settings.xml file in your ORDS configuration folder.

    In most cases, the ORDS-provided Access Log data should be sufficient. However, should you choose to create your own custom access log, you may do so with Jetty XML files.3

    5.2.3.1.1 Jetty examples

    The behavior of the access log will differ depending on your configuration settings. Three possible scenarios for access logs are included.

    Scenario 1: a jetty-access-log.xml file IS included and standalone.access.log location IS NOT set

    In this first scenario, you do not need to “set” the standalone.access.log location. As can be seen in the below image:

    You will, however, need to create an /etc directory in your ORDS configuration folder, similar to how you see below:

    [ORDS configuration directory]/global/standalone/etc

    An example:

    In the above image the /etc folder is nested under the /standalone folder, the /standalone folder is nested under the /global folder, and the /global folder is nested under the ORDS configuration folder (your unique configuration folder name and absolute folder paths will differ).

    Place the following jetty-access-log.xml4 file into that /etc folder.

    This jetty-access-log.xml file is comprised of the following properties:

    <?xml version="1.0"?>
    <!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
    <Configure id="Server" class="org.eclipse.jetty.server.Server">
        <Ref id="Handlers">
          <Call name="addHandler">
            <Arg>
              <New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
                <Set name="requestLog">
                  <New id="RequestLogImpl" class="org.eclipse.jetty.server.CustomRequestLog">
                    <Arg>/Users/choina/ords_access_logs/access.log</Arg>
                    <Arg>%{remote}a - %u %t "%r" %s %O "%{Referer}i" "%{User-Agent}i"</Arg>
                  </New>
                </Set>
              </New>
            </Arg>
          </Call>
        </Ref>
    </Configure>

    Pay special attention to the <Arg></Arg> tags. The first <Arg>ument informs Jetty where and how to save the access log file (i.e., save as access.log at the location indicated). The second argument specifies the format strings to include in the log file. For a detailed explanation of these format strings, see the Jetty Access Logs section of this document.

    NOTE: The format strings used in this sample XML file were chosen arbitrarily. You can include whatever relevant information, assuming it is available. See here for details.

    Once you have saved this file, you may then start ORDS normally (i.e., with the ords serve command). ORDS will then save and append Jetty (Standalone) access log information to the access.log file. You can later view the results and formatting of this log at the location you specified:

    NOTE: You can remove this file from your ORDS configuration prior to the next time ORDS is started, and it will have no impact on your service.
    Scenario 2: A jetty-access-log.xml file IS included and standalone.access.log location IS set

    In this scenario, you will have already completed the following two steps:

    1. Configured the standalone.access.log setting using the ORDS CLI, and
    2. You have included a jetty-access-log.xml file (or whatever file name of your choosing) in the /etc folder.
    NOTE: You will also see a new <entry></entry> has been saved to your /global/standalone/settings.xml file in your ORDS configuration folder.

    After executing the ords serve command, you’ll also see the standalone.access.log configuration setting included as ORDS initializes.

    You’ll also notice two versions of the access log files in the /[access log] folder location.

    One file for the access.log, which was created from the jetty-access-log.xml file you included.

    The other will be a log file with the format of: ords_[log file date].log.

    This second file5 (and subsequent log files saved by date), is the one that is automatically created for you as a result of setting the standalone.access.log property in your ORDS configuration.

    Scenario 3: A jetty-access-log.xml file IS NOT included and standalone.access.log location IS set

    This is effectively the standard, typical way you would configure ORDS for Standalone access logging.

    Set the standalone.access.log configuration setting with the following command:

    ords config set standalone.access.log [/Path to the access log location of your choosing]
    NOTE: You will also see a new <entry></entry> has been saved to your /global/standalone/settings.xml file in your ORDS configuration folder.

    Remove any [jetty].xml files from your /etc folder.

    Once you start ORDS (i.e., ords serve) you’ll find access logs in your access log folder. These and subsequent logs will be saved in the ords_[log file date].log format.

    And that’s it! You made it 😃.

    And some considerations

    The third scenario (the standard option) seems to be the most logical and convenient method for saving Standalone access logs, for a few reasons:

    • No need to rely on XML files
    • Log files are conveniently saved with an intuitive naming convention, and
    • Log files are saved in a recognized format; making it easier for third-party logging analytics tools to ingest the data

    Footnotes are below. If you thought this was helpful, please share and comment! That’s all for now 😬.

    Follow

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

    Footnotes

    1. It’s not crucial for you to have a deep understanding of how Jetty works. At a basic level, just know that there will always be a JETTY_BASE as well as a JETTY_HOME directory. And, in a standard Jetty installation, JETTY_BASE is where your modules and any customization live. While JETTY_HOME is where the Jetty binaries live. For the purposes of ORDS, the [ORDS configuration directory]/global/standalone/etc directory can be looked at as your JETTY_BASE. That is where you’ll place JETTY.XML files; similar to the ones you’ll see in this section’s examples. Again, none of this is important for you to know unless, of course, you’d like to learn more about Jetty. ↩︎
    2. About Jetty Custom Request Log format codes and syntax ↩︎
    3. Jetty XML files can be viewed as a simpler way to add additional configuration settings to your Jetty server without having to create a custom Jetty Module. For a deeper dive into Jetty modules, how to configure them, and customizations see here. ↩︎
    4. This file can be named [anything].xml. The format, contents, and arguments therein are what are important. ↩︎
    5. The ORDS-provided access logs, automatically save in the NCSA Common log format. Since other logging applications and tools may expect to ingest logs in this format, it might be worth considering whether or not you actually want to customize your own Jetty access logs. You can find details on the NCSA Common log format here or visit the now-archived HTTPd page for more information on the creation of this format. ↩︎
  • ORDS Configuration options: about HTTP error responses

    ORDS Configuration options: about HTTP error responses

    Reading docs can be hard

    We send you poor folks to our documentation, and sometimes, it’s a section we haven’t reviewed in quite some time! I don’t think this is specific to any one organization. But over the years, details get lost, forgotten, or stored in an obscure guide section, only to be reviewed once in a blue moon.

    Below is a perfect example. This week, while attempting to assist one of our support engineers, I directed them (and the customer) to Chapter 2.18 of our ORDS Developer’s Guide. In it, we discuss the various configuration settings for how ORDS can return HTTP error responses (HTML, JSON, or Auto).

    However, we don’t explicitly tell you how to change these settings or where to look 😭. I desperately need context and references because I find tech challenging to understand. To me, that’s a big part of providing users with context.

    Current version

    Chapter 2.18 About HTTP Error Responses (click to expand)

    ORDS can now generate HTTP error responses in JSON or HTML format. Prior to ORDS release 20.4, only HTML responses were supported. To preserve the backward compatibility, by default, ORDS attempts to automatically determines the best format to render the error responses.

    You can configure error.responseFormat setting and force ORDS to always render the error responses in either HTML or JSON format.

    2.18.1 About error.responseFormat

    The error.responseFormat setting is a global setting that supports the following values:

    • html – Force all error responses to be in HTML format.
    • json – Force all error responses to be in JSON format.
    • auto (default value) – Automatically determine most appropriate format for a request. 
    2.18.1.1 HTML Mode

    When error.responseFormat value is set to html, all the error responses are rendered in HTML format. This setting can be used to match the behaviour of ORDS 20.3.1 and prior releases. The HTML format displays properly in web-browsers. However, for non-human clients, HTML format is verbose and challenging to parse.

    2.18.1.2 json Mode

    When error.responseFormat value is set to json, all the error responses are rendered in JSON format. The JSON format complies with the Problem Details for HTTP APIs standard. The JSON format is terse, and straightforward for non-human clients to parse. However, it does not display properly in browsers and is not user friendly for non-technical users.

    2.18.1.3 auto Mode

    The default value for error.responseFormat is auto. When this value is configured, ORDS applies the following rules and automatically chooses the most appropriate format to use: 

    If the client supplies a User-Agent header whose value starts with curl/, then the response must be in JSON format. cURL is a popular command line tool for making the HTTP requests. The terser JSON format is more readable in a command line environment. If none of the preceding rules apply, then the response will be in HTML format. See Also:cURL

    If the client supplies an Accept request header, where application/json or application/problem+json is the most preferred media type, then the response must be in JSON format.

    If the client supplies an Accept request header where text/html is the most preferred media type, then the response must be in HTML format.

    If the client supplies a X-Requested-With header, then the response must be in JSON format. Presence of this header indicates that the request is initiated from the JavaScript code and so JSON would be the appropriate response format.

    If the client supplies an Origin header, then the response must be in JSON format. Presence of this header indicates that the request is initiated from the JavaScript code and so JSON would be the appropriate response format.

    There is one exception to this rule, if the request method is POST and the Content-Type of the request is application/x-www-form-urlencoded, then the response will be in HTML format.

    Proposed version/updates

    2.18 About HTTP Error Responses

    You may configure ORDS to generate HTTP error responses exclusively in HTML or JSON format (the default setting is “Auto”). You can modify the error response format by issuing the following ORDS CLI commands:

    FormatCommand
    HTMLords config set error.responseFormat html
    JSONords config set error.responseFormat json
    Auto (default)ords config set error.responseFormat auto

    After issuing one of the above commands two things will occur:

    1. The ORDS CLI will respond with a message that your configuration setting has been updated.
    2. Any pool.xml files associated with this ORDS installation will automatically update to reflect the changes.

    The following images illustrate these changes:

    HTML
    JSON
    Auto

    NOTE: Prior to ORDS 20.4, only HTML responses were supported. To preserve this backward compatibility, by default (i.e., via the Auto setting), ORDS attempts to automatically determine the best format to render error responses.

    2.18.1 About the error.response Format

    The error.responseFormat setting is a global1 setting that supports the following values:

    • HTML – error responses are returned in HTML format.
    • JSON – error responses are returned in JSON format.
    • Auto (default setting) – Automatically determines the most appropriate format for error responses.

    You may use the following ORDS command line command to review your existing configuration settings:

    ords config list --include-defaults

    1Global settings are those settings found in the /[your ORDS configuration folder]/global/settings.xml file. These settings apply to all ORDS instances, regardless of whether they are installed at the Container database (CDB) or Pluggable database (PDB) level.

    NOTE: An ORDS best practice is to install ORDS at the PDB level. This configuration supports High-Availability, Fast Failover, rolling updates, etc. See our Best Practices page for more details.

    2.18.1.1 HTML Mode

    ORDS will render error responses in HTML format when you set the error.responseFormat value to html. You may use this setting to match the behavior of ORDS 20.3.1 and prior releases. The HTML format displays properly in web browsers. However, the HTML format is verbose for non-human clients and may be challenging to parse. The JSON format may be a better alternative for these applications.

    2.18.1.2 JSON Mode

    ORDS will render error responses in JSON format when you set the error.responseFormat value to json. The JSON format complies with the Problem Details for HTTP APIs standard2.

    While the JSON format may not display correctly in browsers and can be challenging for non-technical users to decipher. Although it is terse and straightforward for non-human clients to parse. An exception to this may be in a command line environment; tools such as curl3 make inspecting JSON simple.

    2Learn more: RFC 7807 Problem Details for HTTP APIs
    3Download curl

    2.18.1.3 Auto Mode

    The default value for ORDS’ error.responseFormat setting is auto. When auto is selected, ORDS automatically applies rules according to various conditions and returns responses in the appropriate format. The following conditions and their dispositions are below:

    HTML

    ORDS will return the HTML format when the client supplies an…

    • Accept request header where text/html is the “most preferred” media type.4
    • Origin header and request method is a POST and Content-Type is application/x-www-form-urlencoded.
    JSON

    ORDS will return the JSON format when the client supplies an…

    • Accept request header and application/json or application/problem+json is the “most preferred” media type.4
    • X-Requested-With request header.5,6
    • User-Agent header whose value starts with curl/.
    • Origin request header.5
      • EXCEPTION: Responses will be rendered in the HTML format when the request method is POST and Content-Type is application/x-www-form-urlencoded.

    4About q-factor weighting
    5The presence of this header indicates the request was initiated via JavaScript code. Accordingly, JSON is the most appropriate response format.
    6When performing an asynchronous HTTP (Ajax) request, the header X-Requested-With: XMLHttpRequest is always added. See Settings > headers for details.

    Thoughts?

    So what do you think? Is this format helpful? Does it read and flow better? My goal is to give you exactly what you need upfront. Then, if you choose, you can explore further. Are the references helpful? Are you “learning as you’re learning”? I wish all of life were like this; you get a glimpse of the levels upon levels of context

    Welp, this doc bug has been filed! On to the next one! Until next time 😊.

    Follow

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

  • Microsoft Entra OAuth2.0 JWTs and ORDS secure APIs Tutorial: Configuration and Testing

    Microsoft Entra OAuth2.0 JWTs and ORDS secure APIs Tutorial: Configuration and Testing

    Since ORDS first introduced support for authenticating with JWTs, there have been many questions surrounding configuration and testing. In this example, I cover, from start to finish:

    1. Registering an application in Microsoft Entra
    2. Creating a Client ID, Secret, and related Scope
    3. Use Postman and OAuth2.0 to request a JWT from Microsoft
    4. Decode the JWT (token) so you can use parts of it to create a JWT profile in ORDS
    5. Create an ORDS privilege with relevant roles, which you’ll use later for testing a protected ORDS endpoint (in Postman)

    If this sounds like what you are looking for, then read on.

    There’s a lot to cover here, so there’s no faffing about on this one. Let’s go!

    Configuring Microsoft Entra

    I assume you know how to locate Microsoft Entra (formerly Microsoft AD; details here).

    App registration

    Locate the Applications category, and from the drop-down menu, select App registrations.

    Fill out the information that best fits your scenario. Make sure you understand which Supported account type to use. I chose option three: Accounts in any organizational directory and personal Microsoft accounts.

    NOTE: If you want to test this OAuth code flow with a personal Microsoft email, GitHub, Skype, or another Microsoft "property" then you'll want to make the same choice as me.

    Retrieve the Callback URL from Postman

    You’ll notice in the Redirect URL section that there is another drop-down menu. You’ll want to select “Web.” You’ll need the Postman-provided “Callback” URL for this to work.

    NOTE: This walkthrough assumes you are using Postman, but Insomnia, cURL, and language-specific libraries will have similar, but distinct procedures for the "URL redirect."

    The Callback URL looks disabled in Postman, but you can still highlight and copy it. Copy and save it, then return to the App registration page (in Microsoft Entra).

    Complete app registration

    Click Register. A message will appear, notifying you of the successful app registration. Copy the Application (Client) ID and save it to your clipboard or a text editor. I’m using TextEdit, which you’ll see later.

    Create a new Client Secret ID and Value

    Next, you must create a Client Secret ID and Secret Value. Navigate to the Certificates & Secrets category. Then click the New client secret button. Add a description of the client’s secret along with an expiration date. 180 days is the recommendation.

    Once you’ve completed all the fields, click the Add button. Another confirmation message will appear. You should see your Client Secret Value and Client Secret ID in plain text. COPY your Client Secret Value now, this will be the only time it is visible! Copy your Client Secret ID, too.

    Paste them to your clipboard or text editor. We’ll be using them shortly and in more than one place!

    Add a scope pt 1

    Next, you’ll need to add a scope. Click the Expose an API category and the Add a scope button.

    Application ID URI

    If you haven’t added an Application ID URI, this wizard will prompt you to set one up before you can proceed. You’ll see this screen if this is your first time doing this.

    Microsoft Entra will have prepopulated the Application ID URI field for you. Click the Save and continue button.

    Add a scope pt 2

    The scope name appends to the Application ID URI as you fill in the first field. In this example, I’ve used a “dot notation” for the scope name. Since this is the naming convention we use in ORDS for privileges, I decided to keep everything standardized.

    Once you’ve filled in all the fields, click the Add scope button. Copy the scope name and save it to your clipboard or text editor. This scope name should combine your Application ID URI + the scope you just created. In my case, it looks like this:

    api://367ad5fd-a417-49f9-aed2-7b2290bd4ce3/my.ords.app.scope

    Obtain required OAuth endpoints

    The final step in Microsoft Entra is to retrieve all relevant OAuth2.0 and JWT endpoints. You’ll find 2/3rds of these from the Overview. Click Overview, then the Endpoints tab. You’ll want to copy and save the following endpoints:

    • OAuth 2.0 authorization endpoint(v2)
    • OAuth 2.0 token endpoint (v2)

    The final endpoint identifies the public keys used for decoding JWTs. For some reason, it’s not found in Microsoft Entra, so save this link and copy it to your clipboard or text editor.

    That’s it for Microsoft Entra configuration! Two more steps, and then we can test our secure ORDS endpoint.

    Request a JWT

    With any luck, you’ve been following along and saved all the required information for Postman. Here is what my clipboard looks like, hopefully yours does too:

    Configuring Postman

    Open Postman and enter the following information:

    • Authorization URL
    • Access Token URL
    • Client ID
    • Client Secret
    • Scope
    • Client Authentication

    Once you’ve filled everything in, click the Get New Access Token button.

    Granting access

    If you’ve kept everything identical to what I have, you should see the following prompts asking you to authorize/grant permission to this app so that you may acquire a JWT (Access Token).

    Redirecting back to Postman

    After the browser redirects you back to Postman, copy the token value. You’ll need to decode it next. You’ll use some of the properties (referred to as “claims” in this context) in the JWT when configuring ORDS.

    Decode the JWT

    There are many language libraries available to decode these JWTs. However, for a quick test scenario, these browser-based tools work well:

    Copy and paste the JWT string into one of the decode boxes to decode the “claims” contained in the JWT. Whichever one you choose, it doesn’t matter, they’ll look identical. Copy the “iss”, or issuer value, and the “aud”, or audience value. Save both of these values to your clipboard or text editor.

    Database Actions

    Finally, we move to Database Actions! There are two areas of focus here:

    1. The SQL Worksheet and
    2. The REST Workshop

    Navigate to the SQL Worksheet first.

    SQL Worksheet

    Regardless of whether you are an ADMIN user, a user with the ORDS ADMINISTRATOR role, or a regular database user, you can find the relevant CREATE_JWT_PROFILE PL/SQL procedure in the ORDS_SECURITY or ORDS_SECURITY_ADMIN packages. You can find them in the ORDS_METADATA schema. I could use either as the ADMIN.

    ORDS_SECURITY_ADMIN & ORDS_SECURITY

    After you’ve located the procedure, select and copy it.

    Obtaining the CREATE_JWT_PROFILE procedure

    Open a new SQL Worksheet and paste the procedure. It is missing a lot; hopefully, you’ve been taking notes.

    Editing the CREATE_JWT_PROFILE procedure

    To “fix” this procedure, you’ll need most of the things you’ve been saving along the way:

    • Issuer value
    • Audience value
    • Public keys (also known as JWKs or JSON Web Keys)

    Once your PL/SQL procedure looks the same (with your unique details, obviously), click the Run Script button. You’re done with the SQL Worksheet and can move on to the REST Workshop!

    REST Workshop

    You’ll need to create a privilege next. Remember, this privilege will be identical to the application’s scope name (which you created in Microsoft Entra). Navigate to Privileges, then click the Create Privilege button.

    ORDS privilege setup

    You can choose whatever you like for the Label and Description, but the name MUST match your application’s scope name.

    You’ll need to select SQL Developer for the Role and

    CORRECTION: Role is not necessary for this implementation. The association between the ORDS privilege name and the Application's scope takes care of this for you.

    choose the “target” resource you want to protect. In my case, it’s just an SQL statement that I’ve REST-enabled (it performs a simple query on a database table).

    When complete, you should see a privilege tile similar to the one in my example. The name of that privilege tile should match the name of your application’s scope.

    Test a secure ORDS resource with JWTs

    Believe it or not, you’ve finished the configuration. Onto testing!

    Test an ORDS endpoint

    Here, I select an endpoint I’ve protected (with the privilege I just created). I’ll use that in my Postman example.

    Executing in Postman

    I return to Postman, ensuring that none of my configurations has changed. This time, I’m adding my ORDS URI to the GET address bar. To be safe, I request a new Access Token and add that to my configuration. You’ll see that in the “Current Token” Field in the image below.

    Next, I click the Send button, and volià, it works like a charm!

    Success

    You’ll see a 200 OK Successful Response Status Code and my ORDS payload. And just like that, you’ve done it. You’ve just learned how to access a secure ORDS resource using JWTs!

    And what exactly are we looking at here? Well, these are baby names from 2023 US Social Security Card applications filtered using the following SQL:

    SELECT DISTINCT
        NAME
    FROM
        NAMES2023
    WHERE
        NAME LIKE '%eigh%'

    Forcing a 401 Response Status Code

    And in case you think I’m lying, set Auth Type to “No Auth” and reattempt the GET request. You should see a 401 Unauthorized Response Status Code. If you don’t, “Houston, we have a problem!

    The end

    I think this could help many people, so if you have made it this far, please share and bookmark it for later use. I’m also including the PDF version of the images. I didn’t include notes but placed the arrows and boxes with the utmost precision.

    Follow

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

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

    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?

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

    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

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

    Line 2L_BODY_JSON CLOB := :BODY_JSON
    Lines 9 and 10ORDS.BODY_FILE_COUNT
    Line 11ORDS.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:

    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

    1. 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).
    2. 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.
    3. 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 ParameterAbout files…About form data…About JSON…
    :BODYAccepts 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_TEXTAccepts only one file (to be used with CLOB files).ORDS will automatically recognize form data and handle it accordingly.n/a
    :BODY_JSONORDS 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!

  • ORDS 24.3 Release Highlights

    ORDS 24.3 Release Highlights

    Overview

    There’s plenty to talk about in this release. However, I’m most excited about the performance improvements, ORDS sample applications, and documentation changes. Read on to get the whole story.

    Enhancements

    API Performance

    REST API responses from either AutoREST or customer based modules could see as much as a 30% improvement in response times.

    About a year ago, we introduced (we owe all our progress to Orla though) an internal program to track performance changes/improvements across ORDS APIs quantitatively. I can’t go into too much detail, but here is what I can divulge:

    • Although we use K6 for our performance testing, we are not promoting its use over any other available performance testing solution. There are other great tools available (e.g., Artillery, JMeter, and, of course, k6).
    • Testing is performed nightly against a 23ai database (installed in a PDB); we also include APEX in these tests.
    • For the tests, 250 schemas are created and then populated with various database objects (e.g., Functions, Materialized Views, PLSQL Packages, Sequences, Tables, Triggers, JSON Relational Duality Views, etc.)
      • These schemas are then absolutely hammered with Virtual Users. Users perform actions such as auto-REST enabling objects, creating custom Resource Modules, creating JSON Relational Duality Views, interrogating ORDS Metadata, and performing bulk inserts (BATCHLOAD) GETs, POSTs, etc.
    • These metrics are what we use to track the ORDS quantitative metrics longitudinally.

    So, that’s what we mean by “performance improvements.” Pretty cool, eh?

    ORDS Sample applications

    We have not one but TWO sample ORDS applications for you 😍!

    Flask/Python

    The first is a fully contained LiveLabs sandbox workshop, which can be found here. But if you want to remix the code, check out my repo here (everything is heavily commented; hopefully, this will ease your pain).

    Node.js/React

    Secondly, our development team has created a brand new advanced application. Details are here.

    OAuth2.0 changes

    A consolidation and streamlining of the OAUTH and OAUTH_ADMIN PLSQL packages. The details:

    • We’ve consolidated those mentioned above into these two new packages: 
      • ORDS_SECURITY
      • ORDS_SECURITY_ADMIN
    • The OAUTH and OAUTH_ADMIN PL/SQL Packages have been deprecated by royal decree. However, they’ll still be included until ORDS version 25.3 (this time next year).
    • Creating a client and receiving your Client ID and Client Secret is now streamlined, and Client Secrets can now be rotated (by supporting two active Client Secrets while in rotation).

    Locating the new PL/SQL Packages:

    Finding PL/SQL Packages in Database Actions.

    23ai Boolean

    ORDS now returns BOOLEAN types as JSON TRUE|FALSE properties instead of 0|1.

    What this looks like in practice with various configurations1:

    Oracle Database 23ai + ORDS 24.3
    Oracle Database 23ai + ORDS 24.2
    Oracle DB 21c Enterprise Edition + ORDS 24.3

    1Thank you internet stranger for providing us with this juicy bit of code.

    Mong[ooohhh, no, you didn’t?!] DB API

    • Support for even more Database Administration commands:
      • listIndexes
      • dropIndexes, and
      • optional parameter expireAfterSeconds (which applies to the createIndexes command)
    • The following MongoDB Aggregation Stages are now supported:
    • Users may now specify a JSON Schema validator when creating a collection
    • You may now create MongoDB API connections without TLS connections (Oracle Database 19c and later) can now be made.
    • Users can now set the createIndexes’ “online” option to TRUE to allow DML operations at index creation time.

    In the meantime, here are some resources I found:

    UPDATE: MongoDB API update article (October 10, 2024)

    A brand new article about the latest MongoDB API updates just dropped! Thanks to Hermann for publishing and sharing the latest. Details are here

    Documentation

    Introduced the following new sections:

    • 6.2.4 Using OCI Monitoring Service with Oracle REST Data Services
      • This new section details the configuration of the recently added ords-metrics utility. You can find details on how to set up this monitoring service (to communicate with OCI) here.
    • 3.2 Deploying ORDS with Central Configuration Server
    • Appendix D ORDS Central Configuration OpenAPI
      • Along with the updated docs, we’ve included the OpenAPI spec for creating the endpoints required for a central configuration server (and a special video clip of me retrieving the PL/SQL definitions and the OpenAPI spec in Database Actions).
      • OpenAPI spec doc here.

    Java notes

    In our Release Notes, we claim support for the following JDKs:

    • Oracle Java 11, 17, or 21
    • Oracle GraalVM Enterprise Edition for Java 11
    • Oracle GraalVM Enterprise Edition for Java 17
    • Oracle GraalVM Enterprise Edition for Java 21

    However, this may be confusing regarding Oracle GraalVM Enterprise Editions. You should know that there are currently TWO Oracle GraalVM Enterprise Edition JDKs:

    • Oracle GraalVM Enterprise Edition 20
    • Oracle GraalVM Enterprise Edition 21

    Instead of how we’ve presented, here is another, cleaner presentation of these JDKs:

    Oracle GraalVM Enterprise Edition 20Oracle GraalVM Enterprise Edition 21
    Linux (x86-64): Java 8, 11Linux (x86-64 and aarch64): Java 8, 11, 17
    macOS (x86-64): Java 8, 11macOS (x86-64 only): 8, 11, 17
    Windows (x86-64): Java 8, 11Windows (x86-64 only): 8, 11, 17
    Oracle GraalVM Enterprise Edition details

    So when you are choosing your JDK (to use with ORDS), make sure you consider your platform and use cases. Details on using GraalVM with ORDS here.

    fin

    This concludes the release notes supplement.

    This space ⬇️ left intentionally blank.

    Follow

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

  • How to kill an ORDS process

    How to kill an ORDS process

    How do I “kill” an ORDS process?


    Here are the options I’ve found to “kill” an ORDS process:

    1. Use the kill command followed by the ORDS PID (i.e., Process ID) and the related JVM (i.e., Java Virtual Machine) PID
    2. Press the Control + C keys in the same terminal session that ORDS is running in (assuming it remains uninterrupted)

    It looks like we’ve included in our docs how to start ORDS (with the ords serve command), but we leave it up to you to figure out how to close down/kill/terminate an ORDS process.

    Some observations

    First approach

    The Control + C key option is the simplest option. But this might be the most impractical. If you start ORDS in Standalone mode (using the embedded Jetty server), then you might have ORDS running in its own terminal tab, kind of like this:

    Assuming this is the case, while ORDS is running in a separate tab, you can issue the ps command to review a list of the running processes:

    Afterward, you can return to where ORDS is running and press and hold the Control key followed by the C key. You’ll see a new shell prompt appear:

    There isn’t any real feedback (maybe we should change that). But if you issue the ps If you run the command in a new tab, you’ll see that both the ORDS and Java processes have been “killed.” This signifies that ORDS and the JVM are dead—RIP 🪦.

    Second approach

    If you exit out of a terminal session but ORDS is still running (which, in practice, is entirely possible), you’ll have to search for the appropriate PIDs. The easiest way I’ve found is to issue the ps command (like you saw before) and then issue the kill command plus the relevant PIDs.

    I’m unsure if you need to “kill” the Java process and ORDS. I assume that you do. The only basis I have for this assumption is that when you use the first approach (see above), both the JVM and ORDS are killed. So, I’m attempting to mimic what automatically happens when using the Control + C option.

    Issuing the kill command results in the following message (which you’ll see, assuming the original ORDS terminal window is still viewable):

    And if you reissue the ps command, you’ll see both the ORDS and JVM PIDs have disappeared:

    Summary

    Since I’ve only tested this in ORDS Standalone mode, I can’t comment on Apache Tomcat or Weblogic. I’m assuming they both function very similarly. But if you are using ORDS as both an admin (to get it up and running) and a developer, then some of these commands (that we take for granted) may not be obvious to you. So, as rudimentary and fundamental as this article appears, I hope it helps at least one person early on in their database or ORDS journey.

    Call to Action

    And if you haven’t seen it by now (and why should you? I haven’t plugged it yet), we’ve made a YouTube playlist to accompany one of our more popular ORDS LiveLabs. You can find the LiveLab here.

    And here’s the complete playlist:

    Enjoy 🤗

    That’s all for now, folks!

    Follow

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