Updating VARCHAR2 fields in Oracle Database Actions

Update

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!

Ch-ch-ch-ch-changes

Table relationships in my Autonomous Database, and the User Interaction, Chris Hoina, Senior Product Manager, ORDS, Database Tools
Presentation of flask, python, ADB app in my Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

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.

Side note

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:

Me the develper versus an actual developer, Chris Hoina, Senior Product Manager, ORDS, Database Tools, Oracle Autonomous Database, Database Actions

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
FROM ADMIN.RESTAURANTS
ORDER BY 1

Some screenshots of what this looks like in Database Actions:

SQL Worksheet — Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools
SQL query result in SQL Worksheet — Database Actions, Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools
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.

Next steps

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

Abridged walk-through

From Database Actions, navigate to the “SQL” feature. You’ll see this screen appear.

Select Worksheets in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools
NOTE: I had a previous SQL Worksheet saved, but you can enter SQL statements directly into the default worksheet.
Before city field has been updated in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

*If you expand, you can see the statement that I used.

SELECT DISTINCT CITY
FROM ADMIN.RESTAURANTS
ORDER BY 1

This will produce a list of all the cities in their current form. Notice the four different variations of “Fuquay-Varina”.

Updating fields in City Column Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

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.

After Fuquay Varina has been updated in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

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.

--

--

--

I’m a Senior Product Manager at Oracle. But I’m more than that. I’m not to be objectified, like some shiny toy or trinket…

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to Backup a WordPress Website?

Setting up Laravel & Nginx on Server

Creating custom terraform providers

The Architect’s Guide to Digital Banking

Exploring the fold Function

Bare-Minimum requirement for cracking the frontend interview (Internship)

Dev update Feb. 28

READ/DOWNLOAD$! C#: The Crash Course for Beginners

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Chris Hoina

Chris Hoina

I’m a Senior Product Manager at Oracle. But I’m more than that. I’m not to be objectified, like some shiny toy or trinket…

More from Medium

Automate Data Cleaning & Combining of Files

Oracle Big Data Service

Using Yelp Fusion API as Data Source in Power BI Desktop

CSV file in S3 from a Trino Query