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.
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:
- 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).
- 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 1999And 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.
- 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. ↩︎
