Updating VARCHAR2 fields in Oracle Database Actions
Skip this and just take me to the Abridged Walk-Through
I’ve run into more problems with this python/flask/ords/adb app idea of mine. Turns out I’m too [developer] dumb to do some of the things I had originally envisioned. Now that I’ve become more familiar with python, flask, the Oracle Autonomous Database, and our Database Tools (like ORDS and Database Actions), I’m realizing that I need to rethink my approach.
First off, I’ve updated the (not trying to be pretentious here) User Journey for this project.
SIDE NOTE: The User Journey is something that I really should pay more attention to (especially considering it was one of my concentrations in my MBA program, and it was a huge initiative in my previous role at Big Blue) and you should too!
I’m leaning towards (after a recent discussion with that jeff smith) more of a drop-down style of search. I’d like to present a user with all the available cities (having taken these from my “Restaurants” table) in Wake County.
I think I can populate a list of cities to choose from with the help of REST APIs (via ORDS). But I need to do some clean-up first.
In about a minute, you’ll probably come to the realization that my approach is less than elegant. Remember, I’m not a developer. I’m kind of stumbling through this, and I’ll need to make some sacrifices along the way (we call those “trade-offs” in business-speak). Unfortunately, there are simply some techniques/skills that I do not know yet. For a visual representation of these deficits, I draw your attention to:
Back to your regularly scheduled program
On to clean-up. The cities in this table are a bit all over the place. Let’s take a look.
From Database Actions, I can navigate to a SQL Worksheet. From there I can inspect my tables (Right-click > Edit), or jump straight into the SQL. Since I know my “CITIES” column is the problem, I’ll run this:
SELECT DISTINCT CITY
ORDER BY 1
Some screenshots of what this looks like in Database Actions:
NOTE: The "DISTINCT" parameter drops any duplicates, "ORDER BY" returns results in ascending order.
I suspect that the program used to input health inspections allows for some level of free text.
Tangent / Story
I’ve used an application similar to this in a previous life, but it was a Computer Aided Dispatch (CAD) application. You could enter in free-text, or select from users’ previous entries. There was a lot of flexibility, but because of that, there were many variations on things like street names, cities, locations, parking lots, person names, etc. For the user, awesome, for the developer or database administrator, less so.
At this point, it is pretty clear the amount of normalization I’ll have to do to all these city names. Now I just need to figure out:
- case to use (Upper, Sentence, Lower, SaRCaSTic, etc.)
- city name variations to use
I randomly chose a city with the following SQL command:
UPDATE Restaurants SET CITY = 'Holly Springs'
WHERE UPPER(CITY) = 'HOLLY SPRING'
In this example, I searched for all instances of “Holly Spring” and replaced them with the correct “Holly Springs”. I can then repeat the process for all other cities. Again, the walk-through is here.
I actually know this data well, since I grew up in the area. So deciding on the correct city names shouldn’t be too challenging. But familiarizing yourself with your own data might not be such a bad idea. For instance, in this table, one of the cities displays as “Research Triangle Park”. I don’t think it’s common to refer to it like this; we usually just call it “RTP”. I think little details like that really help to elevate your application.
I’ll keep this brief. But what I think I’m going to have to do next is create a REST API based off of this Restaurant table. I should have a better idea in a few days. But the idea is, that once a user selects a city, it will pass that city as a parameter to a GET request (again, courtesy of ORDS) and then return additional data. A user would then be able to further filter on that returned data, eventually ending on their target restaurant.
Okay, stay tuned…as I feel like I’m on the precipice of something exciting!
Catch me if you can
From Database Actions, navigate to the “SQL” feature. You’ll see this screen appear.
NOTE: I had a previous SQL Worksheet saved, but you can enter SQL statements directly into the default worksheet.
*If you expand, you can see the statement that I used.
SELECT DISTINCT CITY
ORDER BY 1
This will produce a list of all the cities in their current form. Notice the four different variations of “Fuquay-Varina”.
I’ll comment out lines 1–3 and enter in the following SQL statement to update Fuquay-Varina:
UPDATE Admin.Restaurants SET CITY = 'Fuquay-Varina'
WHERE UPPER(CITY) = 'FUQUAY VARINA'
I use the “DISTINCT” parameter to drop any duplicates in the results. “ORDER BY” returns results in ascending order.
You’ll see that 77 rows have been updated.
I’ll un-comment lines 1–3, and comment lines 5–6.
Based on the results, we’ve reduced the variations of “Fuquay-Varina”. I’ll continue this work until I have a smaller set of more consistent cities.