Follow the Coffee

Category: VS Code

  • 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. ↩︎
  • Random Access Memories: ORDS and JWTs

    Random Access Memories: ORDS and JWTs

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

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

    ORDS JWT OAUTH parameters

    You’ll notice two new procedures in that package: OAUTH.CREATE_JWT_PROFILE and OAUTH.DELETE_JWT_PROFILE. After getting acquainted with them, I wanted to highlight three parameters of the OAUTH.CREATE_JWT_PROFILE procedure: 

    • p_issuer
    • p_audience
    • p_jwk_url

    Your JWT issuer (e.g., Microsoft Entra or Oracle Identity Cloud Service) will provide you with these three values required for the OAUTH.CREATE_JWT_PROFILE procedure. 

    However, they might be referred to by slightly different names. I first noticed this as I set up Microsoft Entra to work with ORDS (below are images taken from a slide deck I’m working on). 

    Learn more about these parameters. 
    In fact, I could use some help finding [what we refer to as] the p_jwk_url in the Microsoft Entra dashboard. I'm sure it's there, but I couldn't find it. In case you need it, this is the link that I used.

    So, the names are all slightly different. But if I can figure it out, you definitely can.

    Decoding JWTs

    Once you acquire your JWT, you’ll need a way to decode it so you can use it for testing or development, and you’ll need some of the information for the ORDS profile procedure! Several resources exist, but here is a [non-exhaustive] list I put together: 

    If you choose to use a web-based decoder, it’ll look like this (paste your Token):

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

    Homegrown

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

    I created a JavaScript function that expects a JWT and “splits” on the periods. From there, Base64 decodes the necessary stuff for you and returns it: 

    function decodeJwt(newjwt) {
    var headerJwt = newjwt.split(".")[0];
    var headerJwtdecoded = atob(headerJwt);
    
    var payloadJwt = newjwt.split(".")[1];
    var payloadJwtdecoded = atob(payloadJwt);

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

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

    console.log(headerJwt, payloadJwt, signatureJwt);

    console.log(headerJwtdecoded, payloadJwtdecoded);

    return(headerJwt, payloadJwt);
  };


decodeJwt("Your JWT goes here.");

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

    Thank you for your time 🙇🏻‍♂️

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

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

    Follow

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