Follow the Coffee

Author: Chris

  • From one API to another: Using ORDS Auto-REST to update a table

    From one API to another: Using ORDS Auto-REST to update a table

    It turns out the National Highway Traffic Safety Administration (NHTSA; pronounced ‘nit-sə) has a Vehicle Identification Number (VIN) decoder (it’s freely available to the public). But even cooler are the set of APIs they have. The one I found most interesting, and useful (for my use case) was the /vehicles/DecodeVinValues/[your VIN]?format=[format]l&modelyear=[year] endpoint. Here is an example, using my vehicle:

    And as a cURL command (using my 1999 Chevy Silverado 🤠), with the subsequent server response:

    curl --request GET \
  --url 'https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/1GCEK14V0XZ138493?format=json&modelyear=1999' \

{
	"Count": 1,
	"Message": "Results returned successfully. NOTE: Any missing decoded values should be interpreted as NHTSA does not have data on the specific variable. Missing value should NOT be interpreted as an indication that a feature or technology is unavailable for a vehicle.",
	"SearchCriteria": "VIN(s): 1GCEK14V0XZ138493",
	"Results": [
		{
			"ABS": "",
			"ActiveSafetySysNote": "",
			"AdaptiveCruiseControl": "",
			"AdaptiveDrivingBeam": "",
			"AdaptiveHeadlights": "",
			"AdditionalErrorText": "",
			"AirBagLocCurtain": "",
			"AirBagLocFront": "",
			"AirBagLocKnee": "",
			"AirBagLocSeatCushion": "",
			"AirBagLocSide": "",
			"AutoReverseSystem": "",
			"AutomaticPedestrianAlertingSound": "",
			"AxleConfiguration": "",
			"Axles": "",
			"BasePrice": "",
			"BatteryA": "",
			"BatteryA_to": "",
			"BatteryCells": "",
			"BatteryInfo": "",
			"BatteryKWh": "",
			"BatteryKWh_to": "",
			"BatteryModules": "",
			"BatteryPacks": "",
			"BatteryType": "",
			"BatteryV": "",
			"BatteryV_to": "",
			"BedLengthIN": "",
			"BedType": "",
			"BlindSpotIntervention": "",
			"BlindSpotMon": "",
			"BodyCabType": "",
			"BodyClass": "Pickup",
			"BrakeSystemDesc": "",
			"BrakeSystemType": "Hydraulic",
			"BusFloorConfigType": "Not Applicable",
			"BusLength": "",
			"BusType": "Not Applicable",
			"CAN_AACN": "",
			"CIB": "",
			"CashForClunkers": "",
			"ChargerLevel": "",
			"ChargerPowerKW": "",
			"CombinedBrakingSystem": "",
			"CoolingType": "",
			"CurbWeightLB": "",
			"CustomMotorcycleType": "Not Applicable",
			"DaytimeRunningLight": "",
			"DestinationMarket": "",
			"DisplacementCC": "4800.0",
			"DisplacementCI": "292.91397165471",
			"DisplacementL": "4.8",
			"Doors": "2",
			"DriveType": "4WD/4-Wheel Drive/4x4",
			"DriverAssist": "",
			"DynamicBrakeSupport": "",
			"EDR": "",
			"ESC": "",
			"EVDriveUnit": "",
			"ElectrificationLevel": "",
			"EngineConfiguration": "V-Shaped",
			"EngineCycles": "",
			"EngineCylinders": "8",
			"EngineHP": "",
			"EngineHP_to": "",
			"EngineKW": "",
			"EngineManufacturer": "",
			"EngineModel": "",
			"EntertainmentSystem": "",
			"ErrorCode": "0",
			"ErrorText": "0 - VIN decoded clean. Check Digit (9th position) is correct",
			"ForwardCollisionWarning": "",
			"FuelInjectionType": "Multipoint Fuel Injection (MPFI)",
			"FuelTankMaterial": "",
			"FuelTankType": "",
			"FuelTypePrimary": "",
			"FuelTypeSecondary": "",
			"GCWR": "",
			"GCWR_to": "",
			"GVWR": "Class 2E: 6,001 - 7,000 lb (2,722 - 3,175 kg)",
			"GVWR_to": "",
			"KeylessIgnition": "",
			"LaneCenteringAssistance": "",
			"LaneDepartureWarning": "",
			"LaneKeepSystem": "",
			"LowerBeamHeadlampLightSource": "",
			"Make": "CHEVROLET",
			"MakeID": "467",
			"Manufacturer": "GENERAL MOTORS LLC",
			"ManufacturerId": "984",
			"Model": "Silverado",
			"ModelID": "1850",
			"ModelYear": "1999",
			"MotorcycleChassisType": "Not Applicable",
			"MotorcycleSuspensionType": "Not Applicable",
			"NCSABodyType": "",
			"NCSAMake": "",
			"NCSAMapExcApprovedBy": "",
			"NCSAMapExcApprovedOn": "",
			"NCSAMappingException": "",
			"NCSAModel": "",
			"NCSANote": "",
			"NonLandUse": "",
			"Note": "",
			"OtherBusInfo": "",
			"OtherEngineInfo": "Iron",
			"OtherMotorcycleInfo": "",
			"OtherRestraintSystemInfo": "",
			"OtherTrailerInfo": "",
			"ParkAssist": "",
			"PedestrianAutomaticEmergencyBraking": "",
			"PlantCity": "ROANOKE",
			"PlantCompanyName": "Fort Wayne Assembly",
			"PlantCountry": "UNITED STATES (USA)",
			"PlantState": "INDIANA",
			"PossibleValues": "",
			"Pretensioner": "",
			"RearAutomaticEmergencyBraking": "",
			"RearCrossTrafficAlert": "",
			"RearVisibilitySystem": "",
			"SAEAutomationLevel": "",
			"SAEAutomationLevel_to": "",
			"SeatBeltsAll": "",
			"SeatRows": "",
			"Seats": "",
			"SemiautomaticHeadlampBeamSwitching": "",
			"Series": "1500 (1/2 Ton)",
			"Series2": "Conventional Cab",
			"SteeringLocation": "",
			"SuggestedVIN": "",
			"TPMS": "",
			"TopSpeedMPH": "",
			"TrackWidth": "",
			"TractionControl": "",
			"TrailerBodyType": "Not Applicable",
			"TrailerLength": "",
			"TrailerType": "Not Applicable",
			"TransmissionSpeeds": "",
			"TransmissionStyle": "",
			"Trim": "",
			"Trim2": "",
			"Turbo": "",
			"VIN": "1GCEK14V0XZ138493",
			"ValveTrainDesign": "",
			"VehicleDescriptor": "1GCEK14V*XZ",
			"VehicleType": "TRUCK",
			"WheelBaseLong": "",
			"WheelBaseShort": "",
			"WheelBaseType": "",
			"WheelSizeFront": "",
			"WheelSizeRear": "",
			"WheelieMitigation": "",
			"Wheels": "",
			"Windows": ""
		}
	]
}

    On its own, this is pretty cool. But I wanted to do something more with this information. I wanted to store it, so I could use it later, for a Cloud World-related project.

    A GET and POST Python script

    So, with the help of one of the many popular LLMs I settled on a Python script that helps me take what you see above and get it “database ready.” Here is what that Python code looks like: 

    import argparse
import requests
import json
import re

def sanitize_key(key: str) -> str:
    # Convert keys to lowercase, replace non-alphanumeric characters with underscores.
    key = key.strip()
    key = re.sub(r'[^a-zA-Z0-9]+', '_', key)
    return key.lower()

def decode_vin(vin: str, model_year: int):
    url = f"https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/{vin}?format=json&modelyear={model_year}"
    response = requests.get(url)
    response.raise_for_status()

    data = response.json()

    if "Results" not in data or not data["Results"]:
        raise ValueError("Invalid API response: No Results found.")

    raw_result = data["Results"][0]

    # Sanitize keys and skip empty values
    result = {
        sanitize_key(k): v
        for k, v in raw_result.items()
        if v not in (None, "", "Not Applicable")
    }

    return result

def post_to_endpoint(payload: dict, endpoint: str):
    headers = {'Content-Type': 'application/json'}
    response = requests.post(endpoint, json=payload, headers=headers)
    response.raise_for_status()
    return response.json()

def main():
    parser = argparse.ArgumentParser(description="A tool to decode VINs and send them to an Oracle REST endpoint")
    parser.add_argument("vin", type=str, help="Vehicle Identification Number")
    parser.add_argument("model_year", type=int, help="Model year of the vehicle")
    parser.add_argument("--pretty", action="store_true", help="Pretty-print output JSON")
    args = parser.parse_args()

    # Decode and sanitize
    result = decode_vin(args.vin, args.model_year)

    # Print to console
    if args.pretty:
        print(json.dumps(result, indent=2))
    else:
        print(result)

    # POST to local Oracle ORDS endpoint
    endpoint_url = "http://localhost:8080/ords/ordsdemo/vin_table/"
    try:
        post_response = post_to_endpoint(result, endpoint_url)
        print("\nPOST response from server:")
        print(json.dumps(post_response, indent=2))
    except requests.HTTPError as e:
        print(f"Error posting to endpoint: {e}")

if __name__ == "__main__":
    main()

    👈🏼 Click here to learn what all different parts of this script do.

    I’ll explain briefly according to the code blocks:

    • Lines 6-10 take the results, or rather the “keys” (which will later become the columns in a database table), and standardize them. The keys end up being all lower case, no whitespaces.
    • Lines 12-31 are the bulk of the script. That part issues a GET request to the VIN decoder endpoint. If you can picture in your head, what I’m doing effectively is creating a table of keys and values. Those will then be sent over to my ORDS endpoint (a table I separately created and Auto-REST enabled with ORDS).
    • Lines 33-37 and 56-62 kind of work in tandem. That first block, lines 33-57, basically expects all the “stuff” you see in line 58. Line 58 sends the results (after we’ve processed it), using the ORDS endpoint, to the function in lines 33-57.
    • Lines 39-53 are my entry and exit points for this script. So on the command line I’d execute the following command (I’m using the same VIN and model year): python3 vindecode.py 1GCEK14V0XZ138493 1999

    Target 23ai Database

    So this whole exercise doesn’t work unless I have a target, in my database. And for this project I’m working on, a simple table that has been Auto-REST enabled is fine. I just need it to store these values. And I can use the VIN as a Primary Key (VINs are unique, and no two are alike).

    And if I’m thinking ahead, I’ll probably never update any single row. I just need to be able to insert a new row with a POST request, and then later issue a GET request by VIN. So an Auto-REST enabled table, with the VIN as a Primary Key will work just fine. As things scale, this might change, but with minimal technical debt incurred, this approach works for now.

    As an aside, when I’m working through projects like these, where I’m building out a new object, I usually have a general idea of what I want to achieve, but I will go through tons of iterations1. This time I ended up creating and dropping like four table versions before I ended on this one (four is on the low-end actually). The final DDL for this table: 

    CREATE TABLE ORDSDEMO.VIN_TABLE 
    ( 
     VIN                         VARCHAR2 (17) , 
     MAKE                        VARCHAR2 (50) , 
     MAKEID                      NUMBER (5) , 
     MANUFACTURER                VARCHAR2 (100) , 
     MANUFACTURERID              NUMBER (10) , 
     MODEL                       VARCHAR2 (100) , 
     MODELID                     NUMBER (10) , 
     MODELYEAR                   NUMBER (4) , 
     SERIES                      VARCHAR2 (100) , 
     SERIES2                     VARCHAR2 (100) , 
     TRIM                        VARCHAR2 (100) , 
     TRIM2                       VARCHAR2 (100) , 
     VEHICLEDESCRIPTOR           VARCHAR2 (20) , 
     VEHICLETYPE                 VARCHAR2 (50) , 
     BODYCLASS                   VARCHAR2 (50) , 
     BODYCABTYPE                 VARCHAR2 (50) , 
     DRIVETYPE                   VARCHAR2 (50) , 
     BRAKESYSTEMTYPE             VARCHAR2 (50) , 
     BRAKESYSTEMDESC             VARCHAR2 (100) , 
     FUELINJECTIONTYPE           VARCHAR2 (100) , 
     FUELTYPEPRIMARY             VARCHAR2 (50) , 
     FUELTYPESECONDARY           VARCHAR2 (50) , 
     ENGINECONFIGURATION         VARCHAR2 (50) , 
     ENGINECYLINDERS             NUMBER (2) , 
     ENGINEHP                    NUMBER , 
     ENGINEKW                    NUMBER , 
     ENGINEMANUFACTURER          VARCHAR2 (100) , 
     ENGINEMODEL                 VARCHAR2 (100) , 
     DISPLACEMENTCC              NUMBER (6,1) , 
     DISPLACEMENTCI              NUMBER (8,5) , 
     DISPLACEMENTL               NUMBER (4,1) , 
     DOORS                       NUMBER (2) , 
     GVWR                        VARCHAR2 (100) , 
     PLANTCITY                   VARCHAR2 (50) , 
     PLANTSTATE                  VARCHAR2 (50) , 
     PLANTCOUNTRY                VARCHAR2 (50) , 
     PLANTCOMPANYNAME            VARCHAR2 (100) , 
     OTHERENGINEINFO             VARCHAR2 (255) , 
     ERRORTEXT                   VARCHAR2 (255) , 
     ERRORCODE                   NUMBER (2) , 
     AIRBAGSIDE                  VARCHAR2 (50) , 
     AIRBAGFRONT                 VARCHAR2 (50) , 
     AIRBAGCURTAIN               VARCHAR2 (50) , 
     TPMS                        VARCHAR2 (100) , 
     SEATBELTSALL                VARCHAR2 (50) , 
     ENTERTAINMENTSYSTEM         VARCHAR2 (100) , 
     CUSTOMMOTORCYCLETYPE        VARCHAR2 (100) , 
     TRAILERTYPE                 VARCHAR2 (100) , 
     TRAILERBODYTYPE             VARCHAR2 (100) , 
     TRANSMISSIONSTYLE           VARCHAR2 (100) , 
     TRANSMISSIONSPEEDS          VARCHAR2 (50) , 
     WINDOWS                     VARCHAR2 (50) , 
     WHEELS                      VARCHAR2 (50) , 
     WHEELBASESHORT              VARCHAR2 (50) , 
     WHEELBASELONG               VARCHAR2 (50) , 
     WHEELBASETYPE               VARCHAR2 (100) , 
     NOTE                        VARCHAR2 (255) , 
     ABS                         VARCHAR2 (10) , 
     ADAPTIVECRUISECONTROL       VARCHAR2 (50) , 
     ADAPTIVEHEADLIGHTS          VARCHAR2 (50) , 
     FORWARDCOLLISIONWARNING     VARCHAR2 (50) , 
     PEDESTRIANAUTOMATICEBRAKING VARCHAR2 (50) , 
     REARCROSSTRAFFICALERT       VARCHAR2 (50) , 
     REARAUTOMATICEBRAKING       VARCHAR2 (50) , 
     PARKASSIST                  VARCHAR2 (50) , 
     TRACTIONCONTROL             VARCHAR2 (50) , 
     ESC                         VARCHAR2 (50) , 
     DYNAMICBRAKESUPPORT         VARCHAR2 (50) , 
     LANEDEPARTUREWARNING        VARCHAR2 (50) , 
     LANEKEEPSYSTEM              VARCHAR2 (50) , 
     ACTIVESAFETYSYSNOTE         VARCHAR2 (255) , 
     ADDITIONALERRORTEXT         VARCHAR2 (255) 
    ) 
    TABLESPACE USERS 
    LOGGING 
;

ALTER TABLE ORDSDEMO.VIN_TABLE 
    ADD PRIMARY KEY ( VIN ) 
    USING INDEX LOGGING ;

    Some background

    Oh, please know that I used an LLM for this DDL too. I could have reduced the number of columns since this particular vehicle has a lot of null or “n/a” fields. But other, future vehicles might actually have values for some of those columns. They can stay for now. You might not like the stylistic choice of condensing all my values, but I think this is inconsequential. You’d probably transform them again, in your application layer, when you pull them down from the database. Also, I’m familiar with these values. I could have abbreviated them even further, but for readability, I think this is good (for future me, and others too).

    ORDS Auto-REST

    The last thing I needed to do was Auto-REST enable my table. Which, if you’ve used the SQL Developer Web UI, you know how easy this is. Two clicks and you’re done. From there I have access to several HTTP methods; I only care about GET [all], GET{by id}, and POST.

    Oh, and if you look at the URI that is provided, it’s the same URI that you saw in line 56 of my Python script. So that sort of brings us full circle.

    Right-click on the object, and select Enable.
    Once it is enabled, right-click again and select cURL command.

    If you recall, I used the VIN as a Primary Key. This image shows how that VIN can now be used in the URI (instead of an arbitrary Row ID).

    Review/Checkpoint

    So, at this point, I have two target endpoints:

    1. One where I retrieve a vehicle’s details by its VIN and model year (I don’t own this one – this is the one provided by NHTSA).
    2. Another (that I own) where I am updating and retrieving vehicle records.

    I’ve created a VIN_TABLE table, with the VIN as the Primary Key, and I’ve Auto-REST enabled it with ORDS. I’ve then taken the endpoint for issuing a POST request and added it to the Python script (saved as vindecode.py). Now I can issue the following command to add a record to my table: 

    python3 vindecode.py 1GCEK14V0XZ138493 1999

    And just so you know I’m being honest, I’ll drop everything from my target table, and run through this workflow again.

    Back in VS Code, I’ll issue the command, and receive the response from my database (via ORDS).

    And finally, I’ll review the results (I already did this in VS Code, but I’ll do it in the browser and SQL Worksheet too):

    Review

    And that’s it! One of countless examples of how you might work through a pretty common pattern such as this (using LLMs too). The nice thing here is I don’t have to worry about any ORMs (never have, never will). You have to understand, my reality is such that ORMs do not exist. All I’ve ever known is ORDS; a bit of middleware that sends/receives HTTP/HTTPS requests on your behalf, without ever having to map a thing.

    And we’ve made it much easier to take ORDS for a test drive. We’ve been working behind the scenes to improve our Docker/Podman documentation; we just released the latest refresh to our docs here. Hopefully the docs are much more approachable for the container newbie.

    OH! And if you ever have any issues, open a ticket here. Our team now “owns” the ORDS content on that GitHub repository; so, we’ll be a lot more responsive too!

    That is all for now. If you haven’t downloaded the latest ORDS, check it out here. Bye 👋🏼.

    P.S. Yes, I realize this post’s image is of a GMT-400, and not a GMT-800. But it was a cool pic, and I really liked it. Credit to The Autopian.

    1. I’m including a link to the prompting I used for creating most of what you see here. I think in this new age of AI/LLMs it is important to understand prompting. Eventually, whether we like it or not, we’ll all need to become “prompt engineers.” And as you’ll see in that thread, you actually have to know what you are doing to be able to ask the right questions and to challenge an LLM’s assumptions.

      A lot of times, these things are just flat out wrong, and overly verbose too. As you’ll see, at one point the LLM added a mapping function in one of the iterations, which in my mind was completely unnecessary. Additionally, I wouldn’t have constructed this script as a pseudo-command line interface. BUT…I learned a new library (argparse), and I think I’ll explore that more in the future. ↩︎
  • ORDS 25.2 Release Highlights

    ORDS 25.2 Release Highlights

    As always, the complete, official list of enhancements and fixes can be found here. Contained in this brief post are some of the highlights from this latest release.

    My top enhancements

    Of the new features the team has published, I’d say these next two are probably going to be the “most sought after” for folks.

    Managed service connections

    In short, you can now use either Instance Principal and OCI Profile credentials for the db.connectionType in your ORDS configuration settings. Details can be found here.

    My takeaways from this:

    • It is going to make credential storage a LOT easier
    • Use Instance Principal for when you are using a VM to host ORDS in OCI, and OCI Profile for all else (easy way to look at this without getting too complicated)

    I am working on a tutorial for this, using the OCI Profile method. Once I’m back from traveling, I’ll publish on the official Oracle blog site.

    JWT Profile at the pool level

    The elevator pitch for this: “You know how you could only set a JWT profile for a schema? Well this makes it possible to set this profile at the pool level, for all consumers (schemas) of that pool.” Meaning, this makes it optional, should you want to “share” a JWT Profile across schemas. Details can be found here. Expect an official tutorial soon.

    Pre-Authenticated Requests (PARs)

    ORDS recently introduced support for Pre-Authenticated Requests (PARs). Well, we now have updates in the SQL Developer Web UI. You can create PARs from within the Resource Handler Dashboard. You can revoke PARs too.

    SQL Developer Web UI

    Creating a new PAR

    Creating a new PAR from an existing Resource Handler is fairly effortless now. After creating the PAR, you’ll be provided a Token, Alias, and complete PAR URI. You’ll see how the URI is a concatenation of the existing URI and the PAR token. But what if you want to revoke access?

    Revoking a PAR

    PARs are automatically revoked when they expire. You can alternatively revoke the PAR:

    1. In the PAR dashboard, or
    2. From within the same Resource Handler dashboard

    PAR Expiration by Alias function

    We have introduced a new function that will ingest the PAR alias, and return the time left till expiration. You can find this new function under the ORDS_PAR package of the ORDS_METADATA schema.

    I’d like to know, how would you use this though? Would you use this in some sort of automation, or would you somehow display the remaining time on screen (if we are talking about a client application)?

    MLE/JS

    Now, when a Resource Handler has anything to do with MLE and you encounter an MLE error, you’ll receive a message with a callout to MLE.

    Exception handling

    In the example below, I’ve created a simple MLE/JS Resource Handler that should display some details about a specified employee (identified by their employee number). However, since this test schema has zero objects in it, I’d expect to receive an error.

    In the example below, I use a random employee number, for a non-existent “employees” table. And to truly see the benefits, you’ll want to have the ORDS printDebugToScreen configuration setting set to TRUE.

    ORDS CLI

    Verify command

    I cannot tell you how many times people ask questions that can be answered with this one command.

    On its face, it seems trivial, but this is such a nice shortcut to determining if an ORDS installation is valid. I can see this being used in your shell scripts too, for validation. Something like:

    sed 'ords config --db-pool default verify'

    Or perhaps, add it to the compose.yaml file that we include in our ORDS container?

    Other/Misc

    Want to share your story?

    If you’ve stopped by the ORDS product page recently, then you may have seen our latest customer testimonial. Sphere is a heavy consumer of ORDS, and they love the product. If you have an ORDS story to share email me. Let’s collab on a similar write-up.

    GitHub

    Apparently the oracle/docker-images/ords repo has been “dormant” for quite sometime. Well no more. We (Adrian and I) are now in full control of the ORDS content. So we are updating the Dockerfiles and README. They should be ready in a week or so (end of July 2025), we are finalizing the drafts now. And if you have an outstanding/pending issue you can expect to see some movement on your ticket.

    Oracle Container Registry

    We have taken your feedback and updated the README for the ORDS official container. You can see the latest here. However, we have another update planned, so if you don’t see your suggestions yet, give us another week! We are updating the README to make it more approachable for a container newbie, and we are adding in some additional, helpful comments in the compose.yaml file too!

    Oracle Cloud World 2025

    We are in full Cloud World mode now, till the end October. View the details and register here. Kris, Jeff, and I will be there. We’ll be presenting on several topics. But if travel isn’t in your future, never fear, we’ll have recordings of the presentations once Cloud World has wrapped up.

    Official Release Notes

    And finally, the official release notes can be found here.

    That’s all for now, thanks for stopping by 😊.

  • ORDS and GraphQL: using Maven to build the polyglot and js dependencies for GraalVM for JDK 21

    ORDS and GraphQL: using Maven to build the polyglot and js dependencies for GraalVM for JDK 21

    Disclaimer

    This article is an expansion from a previous, separate Oracle forums thread. The question posed was (paraphrasing):

    Can I use ORDS with Oracle GraalVM for JDK 21 (despite the ORDS team only officially claiming support for Oracle JDK 17, Oracle JDK 21, Oracle GraalVM Enterprise Edition for Java version 17, and Oracle GraalVM Enterprise Edition for Java version 21)?

    And, if I can, how do I install the Oracle GraalVM for JDK 21 graaljs and polyglot dependencies required for ORDS’ GraphQL UI and querying capabilities?

    I’m also reiterating, that what follows is experimental only. We still need to investigate the possibility of supporting Oracle GraalVM for JDK 17 or 21 (the non-Enterprise Editions) in future ORDS releases. So, stay tuned. With all the disclaimers and release of liability out of the way, read on if you’d like learn how to build these dependencies for Oracle GraalVM for JDK 21.

    You’ll need Maven first though.

    Install Maven

    Install the Java project build tool Maven. Installation instructions for your OS can be found here.

    Create a new Maven project

    Next, create a new Maven project1 (you can use the maven-archetype-quickstart archetype for your project) by executing this command in your terminal:

    mvn archetype:generate -DgroupId=com.mycompany.app -DartifactId=my-app -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.5 -DinteractiveMode=false

    Next cd into the project folder (e.g., the artifactId, in this case “my-app”):

    cd my-app

    Your project folder will resemble the following:

    .
├── pom.xml
└── src
    ├── main
    │   └── java
    │       └── com
    │           └── mycompany
    │               └── app
    │                   └── App.java
    └── test
        └── java
            └── com
                └── mycompany
                    └── app
                        └── AppTest.java

    You can disregard /src/test subfolder and its descendants.

    Modify the pom.xml file

    You will need to modify the quickstart project’s pom.xml file. Add the following dependencies2 in the <dependencies></dependencies> parameter section:

    <dependency>
    <groupId>org.graalvm.polyglot</groupId>
    <artifactId>polyglot</artifactId>
    <version>[TBD]</version>
</dependency>
<dependency>
    <groupId>org.graalvm.polyglot</groupId>
    <artifactId>js</artifactId>
    <version>[TBD]</version>
    <type>pom</type>
</dependency>

    How do I know which version to choose?

    Check the Release Notes for the JDK you intend to use. In this case, I’m configuring Oracle GraalVM for JDK 21.0.7, which is based on Oracle JDK 21. The Release Notes state:

    Truffle languages and other components version 23.1.7 are designed for use with GraalVM for JDK 21.0.7.” So, I’m using the following polyglot and js versions.

    Updated pom.xml file

    Your updated POM.xml file should end up looking like this:

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.mycompany.app</groupId>
  <artifactId>my-app</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>my-app</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.release>17</maven.compiler.release>
  </properties>

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.junit</groupId>
        <artifactId>junit-bom</artifactId>
        <version>5.11.0</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

  <dependencies>
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter-api</artifactId>
      <scope>test</scope>
    </dependency>
    <!-- Optionally: parameterized tests support -->
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter-params</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.graalvm.polyglot</groupId>
      <artifactId>polyglot</artifactId>
      <version>23.1.7</version>
    </dependency>
    <dependency>
      <groupId>org.graalvm.polyglot</groupId>
      <artifactId>js</artifactId>
      <version>23.1.7</version>
      <type>pom</type>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.4.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.3.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.13.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>3.3.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.4.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>3.1.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>3.1.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.12.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.6.1</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

    Copy the Maven dependencies

    Save your changes, and then from your terminal (while still in the my-app directory; or whatever you decided to call it) execute the following command:

    mvn dependency:copy-dependencies

    You’ll notice a new /target/dependency subfolder in your project, along with new .jar and .pom files:

    .
├── pom.xml
├── src
│   ├── main
│   │   └── java
│   │       └── com
│   │           └── mycompany
│   │               └── app
│   │                   └── App.java
│   └── test
│       └── java
│           └── com
│               └── mycompany
│                   └── app
│                       └── AppTest.java
└── target
    ├── classes
    │   └── com
    │       └── mycompany
    │           └── app
    │               └── App.class
    ├── dependency
    │   ├── apiguardian-api-1.1.2.jar
    │   ├── collections-23.1.7.jar
    │   ├── icu4j-23.1.7.jar
    │   ├── jniutils-23.1.7.jar
    │   ├── js-23.1.7.pom
    │   ├── js-community-23.1.7.pom
    │   ├── js-language-23.1.7.jar
    │   ├── junit-jupiter-api-5.11.0.jar
    │   ├── junit-jupiter-params-5.11.0.jar
    │   ├── junit-platform-commons-1.11.0.jar
    │   ├── nativebridge-23.1.7.jar
    │   ├── nativeimage-23.1.7.jar
    │   ├── opentest4j-1.3.0.jar
    │   ├── polyglot-23.1.7.jar
    │   ├── regex-23.1.7.jar
    │   ├── truffle-api-23.1.7.jar
    │   ├── truffle-compiler-23.1.7.jar
    │   ├── truffle-enterprise-23.1.7.jar
    │   ├── truffle-runtime-23.1.7.jar
    │   └── word-23.1.7.jar
    └── test-classes
        └── com
            └── mycompany
                └── app
                    └── AppTest.class

    Relocate the new .pom and .jar files

    You’ll need to move all of the files from this temporary app project, and place them into your [ORDS product folder]/lib/ext folder. If you recall, when you first configure and install ORDS, the best practice is to set the ORDS/bin3 and ORDS_CONFIG environment variables.

    If I issue the ENV command in a terminal, I’ll see something similar to the following:

    TMPDIR=/var/folders/wm/t9qmbgnn3f577b51xzb5yrkc0000gp/T/
__CFBundleIdentifier=com.apple.Terminal
XPC_FLAGS=0x0
TERM=xterm-256color
SSH_AUTH_SOCK=/private/tmp/com.apple.launchd.sFYLlexTu3/Listeners
XPC_SERVICE_NAME=0
TERM_PROGRAM=Apple_Terminal
TERM_PROGRAM_VERSION=455.1
TERM_SESSION_ID=*********-****-****-****-**********
SHELL=/bin/zsh
HOME=/Users/choina
LOGNAME=choina
USER=choina
PATH=/opt/homebrew/Caskroom/sqlcl/25.1.1.113.2054/sqlcl/bin:/Library/Frameworks/Python.framework/Versions/3.13/bin:/opt/homebrew/bin:/opt/homebrew/sbin:/usr/local/bin:/System/Cryptexes/App/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/var/run/com.apple.security.cryptexd/codex.system/bootstrap/usr/local/bin:/var/run/com.apple.security.cryptexd/codex.system/bootstrap/usr/bin:/var/run/com.apple.security.cryptexd/codex.system/bootstrap/usr/appleinternal/bin:/opt/podman/bin:/Users/choina/ords/25.2.0.r1651520/bin
SHLVL=1
PWD=/Users/choina/my-app
OLDPWD=/Users/choina
HOMEBREW_PREFIX=/opt/homebrew
HOMEBREW_CELLAR=/opt/homebrew/Cellar
HOMEBREW_REPOSITORY=/opt/homebrew
INFOPATH=/opt/homebrew/share/info:
ORDS_CONFIG=/Users/choina/ords/ords_config
LANG=en_US.UTF-8
_=/usr/bin/env
```

    Notice in the PATH variable the location of my “ORDS Product Folder”: /Users/choina/ords/25.2.0.r1651520/bin. You can use that “root” /Users/choina/ords/25.2.0.r1651520/ and cd directly to the /lib/ext folder. Like this:

    cd /Users/choina/ords/25.2.0.r1651520/lib/ext

    Drag and drop (or use the mv command) all of these new .jar and .pom files from the /target/dependency folder into the ORDS /lib/ext folder. Your ORDS product folder will now look something like this:

    .
├── bin
│   ├── ords
│   ├── ords-metrics
│   └── ords.exe
├── docs
│   └── javadoc
│       └── plugin-api
├── examples
│   ├── application-container
│   │   └── README.md
│   ├── db_auth
│   │   ├── index.html
│   │   └── sql
│   ├── ords-metrics
│   │   ├── create_alarms.sh
│   │   └── ords-metrics.service
│   ├── plugins
│   │   ├── lib
│   │   ├── plugin-demo
│   │   └── plugin-javascript
│   ├── pre_hook
│   │   ├── index.html
│   │   ├── README.md
│   │   └── sql
│   └── soda
│       └── getting-started
├── icons
│   └── ords_logo2.ico
├── lib
│   └── ext
│       ├── apiguardian-api-1.1.2.jar
│       ├── collections-23.1.7.jar
│       ├── icu4j-23.1.7.jar
│       ├── jniutils-23.1.7.jar
│       ├── js-language-23.1.7.jar
│       ├── junit-jupiter-api-5.11.0.jar
│       ├── junit-jupiter-params-5.11.0.jar
│       ├── junit-platform-commons-1.11.0.jar
│       ├── nativebridge-23.1.7.jar
│       ├── nativeimage-23.1.7.jar
│       ├── opentest4j-1.3.0.jar
│       ├── polyglot-23.1.7.jar
│       ├── README
│       ├── regex-23.1.7.jar
│       ├── truffle-api-23.1.7.jar
│       ├── truffle-compiler-23.1.7.jar
│       ├── truffle-enterprise-23.1.7.jar
│       ├── truffle-runtime-23.1.7.jar
│       └── word-23.1.7.jar
├── LICENSE.txt
├── linux-support
│   ├── man
│   │   ├── ords.1
│   │   ├── ords.conf.5
│   │   └── ords.service.8
│   ├── ords.conf
│   ├── ords.service
│   ├── ords.sh
│   └── README.md
├── NOTICE.txt
├── ords.war
├── scripts
│   ├── installer
│   │   ├── apex_ords_app_con_grants.sql
│   │   ├── ords_installer_privileges.sql
│   │   └── ords_set_editioning.sql
│   └── migrate
│       ├── ords_manual_migrate_workspace.sql
│       ├── ords_manual_migrate.sql
│       ├── ords_migrate_apex_rest.sql
│       ├── ords_migrate_report.sql
│       └── ords_migrate_workspace_rest.sql
└── THIRD-PARTY-LICENSES.txt

    Close out all of your terminal sessions. And make sure you are using the correct version of Java; you can check with the java --version command.

    Restart ORDS and test

    Restart ORDS, the ords/_/graphiql endpoint should be available. You can access it one of three ways:

    1. Directly by navigating to http://host:port/ords/[your schema]/_/graphiql
    2. From:
      • the SQL Developer Web LaunchPad
      • the SQL Developer (Hamburger) menu

    The end

    That’s it. You should now have a test/experimental version of GraalVM for JDK 21 with the required Graal js and polyglot dependencies installed (much like you would with the now-deprecated gu installer).

    1. Refer to the quick Maven start guide if you run into trouble. And read up on the structure of the quickstart archetype here. ↩︎
    2. Examples are taken from here. ↩︎
    3. ORDS/bin is a reference to one of the subfolders in your (parent) “ORDS Product folder.” ↩︎

  • Adding X-Powered-By, Version, and Date Headers to ORDS Standalone

    Additional functionality with Jetty XML

    Here is an example of a Jetty Context XML file that adds additional headers to your ORDS HTTP responses. 

    <?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure_10_0.dtd">
<Configure id="httpConfig" class="org.eclipse.jetty.server.HttpConfiguration">
    <Set name="sendXPoweredBy">
      <Property name= "jetty.send.xpoweredBy" default="true" />
    </Set>
    <Set name="sendServerVersion">
      <Property name="jetty.send.server.version" default="true" />
    </Set>
    <Set name="sendDateHeader">
      <Property name="jetty.send.date.header" default="true" />
    </Set>
</Configure>

    Note

    This example is designed to show how you might structure a Jetty Content XML file to add additional functionality to ORDS’ embedded Jetty server. I’m using ORDS in standalone mode, so if you are using Apache Tomcat or Weblogic, this will not work.

    To test this out, first, create a Jetty XML file and populate the body with the contents in the above example. In this example, I’ve named the file jetty_headers.xml.

    Next, place the file in your /etc folder1. Once there, start ORDS back up (i.e., ords serve). I have installed and deployed ORDS locally (i.e., localhost), with my configuration folder named configuration. With the full path being:

    /Users/choina/ords/configuration/global/standalone/etc/jetty_headers.xml

    For reference, here is a view of how my ORDS configuration folder is structured:

    .
├── databases
│   └── default
│       ├── pool.xml
│       └── wallet
│           └── cwallet.sso
└── global
    ├── settings.xml
    └── standalone
        ├── access_logs
        ├── etc
        │   └── jetty_headers.xml
        ├── self-signed.key
        └── self-signed.pem

    With everything in place, I can issue a request to one of my ORDS endpoints. In this case, I’ve created a simple GET Handler that queries the EMP table. The actual response isn’t the focus, but rather the response headers.

    I used cURL for this:

    curl http://localhost:8080/ords/hr/employees/

    In the response, I now see three new response headers displayed in a console output2:

    – Server: Jetty(12.0.18)

    – X-Powered-By: Jetty(12.0.18)

    – Date: Sat, 24 2025 11:46:12 GMT

    Below is a console log printout showing the full details of the request and response (excluding the response body):

    * Preparing request to http://localhost:8080/ords/hr/employees/
* Current time is 2025-05-24T11:46:12.857Z
* Enable automatic URL encoding
* Using default HTTP version
* Enable timeout of 30000ms
* Enable SSL validation
* Connection 9 seems to be dead
* Closing connection 9
* Hostname in DNS cache was stale, zapped
*   Trying 127.0.0.1:8080...
* Connected to localhost (127.0.0.1) port 8080 (#10)

> GET /ords/hr/employees/ HTTP/1.1
> Host: localhost:8080
> User-Agent: insomnia/11.1.0
> Accept: */*

* Mark bundle as not supporting multiuse

< HTTP/1.1 200 OK
< Server: Jetty(12.0.18)
< X-Powered-By: Jetty(12.0.18)
< Date: Sat, 24 May 2025 11:46:12 GMT
< Content-Type: application/json
< X-ORDS_DEBUG: true
< ETag: "yGRRTja1S+tUjM5cGRS/a6WnUiHoqAczoKJ0J5Ty9sAGg1QeeXN3G+BtUukHu9DWgxrpOAOaapoBAd++rVdl6g=="
< Transfer-Encoding: chunked


* Received 8.2 KB chunk
* Received 7 B chunk
* Connection #10 to host localhost left intact

    Conclusion

    Arguably, the Server, X-Powered-By, and Date response headers could be considered functionally useless. However, for logging purposes, especially when it comes to huge applications or microservices more data can aid in quicker root-cause analyses.

    In any case, pretty neat how you can easily add enhanced functionality to ORDS standalone with a few lines of XML.

    References

    Footnotes

    1. You need to create this `/etc` folder manually. As of ORDS 25.1, this folder isn’t something that already exists. You might have a path similar to the one I shared above. ↩︎
    2. You may notice the Date header is returned in GMT/Zulu. This will always be the case with ORDS. ORDS will always return data and times in GMT/Zulu format. For reference, if you independently alter the `user.timezone` JVM Option, this would only change how ORDS interprets dates and times where no timezone is attached. The Oracle Database `Date` and `Timestamp` datatypes are two examples where the timezone is not indicated. ↩︎
  • ORDS 25.1 Release Highlights

    ORDS 25.1 Release Highlights

    ORDS 25.1 is now available, here are the highlights 😀

    JWT roles-based scopes

    You are probably well aware of our current JWTs authentication and authorization support. But shortly after releasing this functionality, one of our long-time customers asked us to enhance ORDS JWT Profiles so they could also support roles “claims” (and scopes). So now, when creating your ORDS JWT Profile, you can set your p_role_claim_name => '/roles'. This setting would “point” to the roles you have configured within your identity provider (like IAM, IDCS, Auth0, etc.).1

    🛎️ Come back to my blog in about two days, and you’ll see a new updated tutorial illustrating this new functionality (with JSON Pointers for the roles-based claim).

    In the meantime, be sure to check out my current JWT-related blog posts:

    ORDS_EXPORT and ORDS_EXPORT_ADMIN

    The most significant changes are available options for users you’ve granted the ORDS_ADMINISTRATOR_ROLE.2 Now, you can export another user’s entire schema, including the details for their JWT Profile. In the screenshots below, you’ll see examples of the ORDS_EXPORT_ADMIN.EXPORT_SCHEMA procedure, using various optional parameters.

    You have a lot of flexibility here; you can choose which optional parameters to include. Can you spot the differences?

    An example showing the ENABLE_SCHEMA and RUNNABLE_AS_ADMIN parameters.
    An example that consists of the SCHEMA parameter but turns off the RUNNABLE_AS_ADMIN parameter.
    An example showing the new INCLUDE_JWT_PROFILES parameter.

    ORDS_SECURITY updates

    The next time you create an ORDS OAuth client, you might notice some changes to the UI. Under the covers, this action is made possible by the ORDS_SECURITY PL/SQL package. You can still use the older, now deprecated OAUTH packages, but we now default to these newer ORDS_SECURITY procedures and functions.

    One of the most notable changes is that these procedures now follow the standard convention (you’ve probably seen elsewhere) of showing a Client’s Secret once and only once. The procedure is now more succinct, organized, and secure.

    💡When you need a new Client Secret, you can “rotate” it with the new ROTATE_CLIENT_SECRET functions (using the Client Name, Client Id, ORDS/internal Id).

    Dark mode

    Dark mode is activated. You can set SQL Developer Web (aka Database Actions) to Light, Dark, or Same as browser. I quite like the third option, as it makes shifting from Apple’s Light to Dark Mode seamless.

    Including this here to remind you of the other available configurable settings.

    DBA_ORDS views

    These views aren’t new for this release, but I don’t think we’ve mentioned them recently. Any of your REST-enabled schemas can access these DBA_ORDS_[View Name] views (for their respective schemas), and they are really helpful when you need to quickly view your most important configurations.

    Like always, you can drag and drop “objects” into the SQL Worksheet. After dropping, a modal will appear with different options (depending on the object type), allowing you to choose an action.

    Important links

    And finally, the important links:

    That’s all for now. I am working on a JWT-using-roles tutorial, which should be out by Friday this week. I’ll update this post when it is live.

    And I have another new ORDS plug-in tutorial that I’d like to share; this one is Java-based. My friend Reydan from the Oracle Health (via Cerner) side is integrating the heck out of their stuff with ORDS, and this example is something he came up with as an exploratory exercise. It’s nothing fancy, but I thought it would be great for the beginner.

    And that’s all for now!

    References

    1. This is known as a JavaScript Object Notation Pointer (JSON Pointer). An upcoming JWT tutorial using role-based claims will provide more details. The technical specifications for the JSON Pointer can be found here. ↩︎
    2. If you are using the Autonomous Database (ADB, ATP, JSON), then you’ll know this ORDS Administrator as your “ADMIN” user. ↩︎

  • Troubleshooting: reviewing ORDS connections, your application server, and response times

    Troubleshooting: reviewing ORDS connections, your application server, and response times

    Symptom/Issue

    In an internal Slack thread today, a user was trying to diagnose browser latency while attempting to connect to the ORDS landing page.

    Peter suggested what I thought was a pretty neat heuristic for checking connections to ORDS and your database, as well as latency.

    Methodology

    Let’s say your symptoms are either slow loading or intermittent loss of service when attempting to reach an ORDS landing page. Your immediate thought might be to refresh your browser or bounce the ORDS server (if running in Standalone mode). But this isn’t practical in a production environment, nor is it practical if you have ORDS deployed on a Weblogic Server or Apache Tomcat (you can deploy using any of those three servers).

    Quick Heuristic

    Use cURL to test two different endpoints, compare their response times to each other, and compare to the response time you’ve observed historically1.

    URL one

    This first URL points to where your ORDS instance “lives.” 

    https://[my host]/ords

    “Lives,” what does that mean? If you’re deploying to Weblogic Server ORDS would “live” in the $WEBLOGIC_HOME/application directory. In Apache Tomcat it is the $CATALINA/webapps directory, and on Standalone (using the ORDS embedded Jetty server) that might be $HOME/[your ords product folder].

    So if you execute a cURL command to that URL, you’re issuing a request to your application server. With that single command, you can determine:

    1. if it’s even up
    2. what the response is, and
    3. how fast that response is

    Honestly, this sounds silly and obvious at first. But it costs you nothing to just rule out the application server as a culprit. The command2 I tested (with the response included): 

    choina@MacBook-Pro-2 ~ % curl -v -s -w "\nTotal time: %{time_total}\n" http://localhost:8080/ords                               
* Host localhost:8080 was resolved.
* IPv6: ::1
* IPv4: 127.0.0.1
*   Trying [::1]:8080...
* Connected to localhost (::1) port 8080
> GET /ords HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/8.7.1
> Accept: */*
> 
* Request completely sent off
< HTTP/1.1 301 Moved Permanently
< Location: /ords/
< Content-Length: 0
< 
* Connection #0 to host localhost left intact

Total time: 0.001882

    NOTE: Make sure you issue this cURL command WITHOUT a trailing slash after /ords. So like this: http://localhost:8080/ords, and NOT like this: http://localhost:8080/ords/. If you include the trailing slash, you'll force a redirect to the http://localhost:8080/ords/_/landing page (this just adds unnecessary time to your result).

    URL two

    The second cURL command you can issue is to the Metadata catalog for your/a target schema. In this example, I’m working with my ordsdemo user. He’s been REST-enabled, and I’ve already set up some Resource Modules (aka ORDS APIs). So I know there is “stuff” at that endpoint.

    https://[my host]/ords/[target schema]/metadata-catalog/

    Here is the cURL command I used (with the response included):

    choina@MacBook-Pro-2 ~ % curl -v -s -w "\n\nTotal time: %{time_total}\n" http://localhost:8080/ords/ordsdemo/metadata-catalog/

* Host localhost:8080 was resolved.
* IPv6: ::1
* IPv4: 127.0.0.1
*   Trying [::1]:8080...
* Connected to localhost (::1) port 8080
> GET /ords/ordsdemo/metadata-catalog/ HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/8.7.1
> Accept: */*
> 
* Request completely sent off
< HTTP/1.1 200 OK
< Content-Type: application/json
< X-ORDS_DEBUG: true
< X-Frame-Options: SAMEORIGIN
< Transfer-Encoding: chunked
< 
* Connection #0 to host localhost left intact
{"items":[{"name":"MOVIE","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/movie/"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/movie/","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/movie/","mediaType":"application/openapi+json"}]},{"name":"moviestream","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/mymovies/movie-all"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/mymovies/movie-all","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/mymovies/","mediaType":"application/openapi+json"}]},{"name":"moviestream","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/mymovies/movie-genre"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/mymovies/movie-genre","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/mymovies/","mediaType":"application/openapi+json"}]},{"name":"test_endpoints","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/no_objects/hello_auth"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/no_objects/hello_auth","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/no_objects/","mediaType":"application/openapi+json"}]},{"name":"test_endpoints","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/no_objects/hello_client_cred"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/no_objects/hello_client_cred","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/no_objects/","mediaType":"application/openapi+json"}]}],"hasMore":false,"limit":25,"offset":0,"count":5,"links":[{"rel":"self","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/"},{"rel":"first","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/"}]}

Total time: 0.030173

    In the above example, we are hitting the database server (because we are grabbing the metadata catalog for the target schema). Consequently, the Total time for the round trip is slightly higher. Now, I can compare the two times against each other and against what I’ve seen historically. Are they reasonable? Do they pass the “sniff test”? Or do the results merit a deeper investigation?

    Fin

    I cannot stress this enough; this is just a simple heuristic to get you started. It takes 20 seconds to execute these commands. You’re really just “slicing the pie” at this stage. I honestly don’t think that simile applies here (unless you’ve ever cleared a room before), but you get the idea – thin slicing. Inching your way ever closer to the solution. This is just a part of the route-cause analysis.

    That’s all for now. Keep this in your back pocket. If you have your own heuristics or troubleshooting tips, leave them in a comment below. I’d love to hear how you diagnose issues related to networking, connectivity, latency, etc.

    Follow

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

    1. I was hesitant to include this last part, “you’ve observed historically,” because I don’t give a metric. Honestly, it depends on your workload. But if you are doing any sort of performance testing you probably have at least a good sense of what response times should be like for your application (I’m talking directly to developers here). Even if you don’t know these expected response times, you can still compare the individual results against each other: the first URL vs the second URL, in this example. ↩︎
    2. What do the optional -v -w -s variables mean? Good question -v is shorthand for “Verbose;” which means print out as much info as is available. The -s is shorthand for “Silent;” in other words, don’t show any progress bars. And the -w means “Write Out,” in this case, “explicitly write out the Total time for me please.” ↩︎
  • 401 Unauthorized invalid_token – troubleshooting Oracle Cloud IAM JWTs with ORDS

    401 Unauthorized invalid_token – troubleshooting Oracle Cloud IAM JWTs with ORDS

    Symptoms

    You are able to request an access token from OCI IAM. Yet, when you issue the subsequent request to your target resource (an ORDS endpoint), you receive the following message (error="invalid_token"): 

    WWW-Authenticate: Bearer realm="Oracle REST Data Services", error="invalid_token"

    Actions you’ve taken

    1. You’ve done the following in OCI:
      • Registered an Integrated Application with Oracle Identity and Access Management (IAM)
      • Created a Primary Audience & Scope
      • Obtained your Client ID and Client Secret
      • Configured your networking correctly (or at least have high confidence it’s configured correctly)
      • Acquired all of the correct, relevant URIs:
        • Authorization
        • Access Token
        • JWK
    2. You’ve configured the following in ORDS:
      • Created your JWT Profile according to the docs (or this tutorial)
      • Created the JWT Role and Privilege (which should be the same as the OCI Scope name)
      • And protected your target resource (aka ORDS API)
    3. You’ve placed everything where it should be in your choice of API testing tool (cURL, Postman, Insomnia, etc.).

    YET…you still receive this error="invalid_token" message, it is quite possible that you have not made the JWK URL publically accessible in OCI IAM. 

    Solution

    Here is how you can verify and resolve this issue. First, navigate to your domain, then select Settings.

    If this Configure client access box is unchecked, it is likely the culprit. Check it, then select Save Changes (the button at the bottom of the screen).

    This box must be checked so your application can automatically access a JWK URL (to be used for decoding a JWT) without having to sign in to the OCI tenancy.

    Then, once you re-attempt your HTTP request, ORDS will be able to:

    1. Access the JWK URL (which you’ve included when you created your JWT Profile)
    2. Verify the authenticity of your JWT, and 
    3. Respond with the results from your resource (ORDS endpoint)

    Et voilà! And that’s it, you’re back in business! 

    To-do list

    I think we have some action items, too: 

    • Investigate this error message and see if we can improve the message to the user (we’ve already filed an enhancement request on this)
    • Update the docs to be more specific on this setting and where to find it (a documentation bug has already been filed for this)
    • Determine if this is a good candidate for adding to the troubleshooting section of our guide
    • Update my JWT tutorial

    And that’s all for now. ORDS version 25.1 is right around the corner, so look out for the official release notes and my accompanying blog too!

    Follow

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

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

    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:

    1. Verify you are who you say you are, and
    2. 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:

    1. You have a lot of flexibility with these ORDS APIs, and
    2. 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!

  • A simple ORDS GET request using the Go language

    A simple ORDS GET request using the Go language

    Venkata this one’s for you 😀

    It took me the rest of the afternoon, but here is a pretty simple GET request example, using a user-defined ORDS API. This API is derived from one of our LiveLabs, a Python + JavaScript application. But this particular scenario pretty well exemplifies what we discussed in our presentation yesterday—that the RESTful framework makes your backend code much more language-agnostic. With a few tweaks, I was able to “port” this over to Go.

    ORDS example API

    The ORDS API I’m working with looks like this on the back end (this is our REST Workshop; where you build ORDS APIs):

    More formally, it looks like this:

    SELECT DISTINCT
    GENRE
FROM
    MOVIE,
    JSON_TABLE ( GENRES, '$[*]'
            COLUMNS (
                GENRE PATH '$'
            )
        )
ORDER BY
    1 ASC

    The MOVIE table I’m calling upon consists of various data types, but the target GENRE In this case, the column is a JSON object that actually contains an array. So, all I’m doing is returning the unique genres (i.e., removing duplicates) from a table of thousands of movies. What is nice about this approach is that all my SQL and PL/SQL stay in the database. All my application’s code needs now is an endpoint.

    If I don’t want to keep pinging that endpoint, I can always just use a JSON document as a proxy for the endpoint. And if I just point my browser to that URI (i.e. ORDS API), here is what the results look like (I had to take two screenshots to capture everything):

    Top of the page.
    Bottom of the page.

    When you are looking at these results, it’s best to focus on the “root” key:value pairs of the ORDS response. For a fairly standard ORDS GET request, the response body will consist of:

    • items
    • hasMore
    • limit
    • offset
    • count, and
    • links

    When I collapse my example, I am left with the following:

    {
    "items": [
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 24,
    "links": [
    ]
}
    Note: The items will be an array, as will the links.

    A caveat to this is when/if you choose to Auto-REST enable an object, the result will look mostly the same. However, ORDS adds a self-referential link to each result. But those links are an array, too.

    Regardless, it shouldn’t affect how you approach this in Go. The struct of those types are the same: Items []Items json:"items" and Links []Links json:"links", respectively. Compare this auto-REST-enabled result to the one above:

    Worth noting: The image below is using a different endpoint. Its basically doing a "select * from movies;" on the MOVIES table. Hence, why it looks a little different. But you should still be able to see what I am talking about. If you want to learn more about ORDS Auto-REST, then check this out.
    If you squint, you can see how each result has its own “rel” link. That is how Auto-REST would look, this is expected.

    Go time

    Coming from Python and PL/SQL, along with some JavaScript, I found Go to be extremely enjoyable. I get the gist of how you define the types and structures of “things.” I haven’t even bothered to look at the documentation yet; I didn’t even need to. It was easy to follow along using the two tutorials I found (here and here). After a few minutes of debugging (which is quite intuitive in Go, actually), I had my code working.

    Actually, once I figured out the syntax and Go idiosyncracies, creating this simple example wasn’t much of an effort.

    Here is the Go code that I came up with:

    package main

import (
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"os"
)

type Response struct {
	Items   []Items `json:"items"`
	Hasmore bool    `json:"hasMore"`
	Limit   int     `json:"limit"`
	Offset  int     `json:"offset"`
	Count   int     `json:"count"`
	Links   []Links `json:"links"`
}

type Items struct {
	Genre string `json:"genre"`
}

type Links struct {
	Rel  string `json:"rel"`
	Href string `json:"href"`
}

func main() {

	response, err := http.Get("http://localhost:8081/ords/ordsdemo/mymovies/movie-genre")

	if err != nil {
		fmt.Print(err.Error())
		os.Exit(1)
	}

	responseData, err := ioutil.ReadAll(response.Body)
	if err != nil {
		log.Fatal(err)
	}

	var responseObject Response
	json.Unmarshal(responseData, &responseObject)

	for i := 0; i < len(responseObject.Items); i++ {
		fmt.Println(responseObject.Items[i].Genre)
	}

	fmt.Println(responseObject.Hasmore)
	fmt.Println(responseObject.Limit)
	fmt.Println(responseObject.Offset)
	fmt.Println(responseObject.Count)

	for i := 0; i < len(responseObject.Links); i++ {
		fmt.Println(responseObject.Links[i].Rel + responseObject.Links[i].Href)
	}

}

    If you know Go, then I’m not teaching you anything that you don’t already know. You probably know the libraries I’m using. However, I think it is worth pointing out a few ORDS-specific things.

    When it comes to the items and the links they are always going to be an array. So you’ll need to account for that when you are defining the structures (aka struct) for your user-defined types (let me know if this terminology is correct, it just seems so intuitive, so I’m going with it).

    Here are some other things to consider:

    • hasMore is a Boolean; or bool in Go. It’s also going to be either true or false.
      • True means there are more results (so you can page forward if you want).
      • False means there are no more results.
    • limit just means how many results you should expect. In my case, 25 results (that is our default, but you can adjust this when you build your APIs).
      • Another way to look at this is that my pagination is set to 25 results per page.
    • offset and limit are kind of interdependent. If you are on the first page then your offset will be = 0. But if you wanted to view your second “page” or set of results, then you’d set your offset = 25.
      • And because your limit = 25 then you’d see results 26-50.
    • The count is just the total results per page, or 25 in this case (hypothetically, if you were to make it to the end of your results set, it would probably not be exactly 25, something to keep in mind).

    Here is a complete JSON object of my first results (if you want to play around with it until you get ORDS up and running):

    {
    "items": [
        {
            "genre": "Action"
        },
        {
            "genre": "Adventure"
        },
        {
            "genre": "Animation"
        },
        {
            "genre": "Biography"
        },
        {
            "genre": "Comedy"
        },
        {
            "genre": "Crime"
        },
        {
            "genre": "Documentary"
        },
        {
            "genre": "Drama"
        },
        {
            "genre": "Family"
        },
        {
            "genre": "Fantasy"
        },
        {
            "genre": "Film-Noir"
        },
        {
            "genre": "History"
        },
        {
            "genre": "Horror"
        },
        {
            "genre": "Lifestyle"
        },
        {
            "genre": "Musical"
        },
        {
            "genre": "Mystery"
        },
        {
            "genre": "Reality-TV"
        },
        {
            "genre": "Romance"
        },
        {
            "genre": "Sci-Fi"
        },
        {
            "genre": "Sport"
        },
        {
            "genre": "Thriller"
        },
        {
            "genre": "Unknown"
        },
        {
            "genre": "War"
        },
        {
            "genre": "Western"
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 24,
    "links": [
        {
            "rel": "self",
            "href": "http://localhost:8081/ords/ordsdemo/mymovies/movie-genre"
        },
        {
            "rel": "describedby",
            "href": "http://localhost:8081/ords/ordsdemo/metadata-catalog/mymovies/item"
        },
        {
            "rel": "first",
            "href": "http://localhost:8081/ords/ordsdemo/mymovies/movie-genre"
        }
    ]
}

    All the code you see here is also in my blog post code repo. There are other examples there, too!

    Also, huge thanks for asking about Go. It gave me an excuse to start learning it. I think I might be a Go fanboi now 🫣!

    And for all others reading:

    Follow

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

  • 503 Service Unavailable: ORDS unable to make a connection…all connections in the Univeral connection Pool are in use – [30,30,42….]

    Have you ever seen this?

    Have you ever seen a message like this while working with ORDS? The one where you receive a 503 Service Unavailable response code and message?

    This message has something to do with ORDS being unable to make a connection to the database.

    But if you keep reading, you’ll see even more information- something related to the Universal Connection Pool- followed by an array of numbers. Did you know that the “stuff” in that array is actually helpful information?

    Well…Kris knew.

    What does it all mean?

    But for the rest of us plebes, what does “max = 30 and current in use = 30” refer to? Well, let’s figure it out!

    Take a closer look at the above image. You’ll see an array consisting of the following: [30, 30, 42, 12, 0, 30, 1, 0, 29]. Which, according to the Universal Connection Pool documentation, translates to:

    Explore further

    Now what?

    So, what do we do with this information? Well, it depends. In this case, it looks like available connections were exhausted. And what could cause that? It’s a mystery, but it doesn’t have to be 😉. You need to do some exploring (It’s not just going to fix itself.)!

    Luckily, there are many ways to “peek under the covers” to learn what is happening in and around your database. Some things that come to mind:

    These are the ones that I’m most familiar. But if you are reading this, what works for you? Drop a comment on what tools you use, and I’ll update this post for others to see!

    When you’re ready…

    Once you’ve determined what is happening, you can return to ORDS and tweak your JDBC settings—if you need to!

    BIG DISCLAIMER: I wouldn't just set your Max connection settings to some arbitrarily high number. That might work in the short term. But until you can figure out what is actually happening across an application's execution path, that approach is just a band-aid solution.

    That’s all folks

    This post is outside the norm of what I usually post. I’m not doing a walkthrough or tutorial. I’m just…throwing some info out there. I mainly wanted to write this quick PSA before I forgot.

    I’ve seen this error come up frequently in questions. I think if more people knew what those numbers meant, it might help with self-diagnosis. I hope I’m not wrong. Catch you later 😎!

    Follow

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