Tag: Database

  • Updating VARCHAR2 fields in Oracle Database Actions

    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 that I’d 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

    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:

    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 abridged walk-through of this 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 on 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

    Select Worksheets in Database Actions Oracle Autonomous Database, Chris Hoina, Senior Product Manager, ORDS, Database Tools

    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. 
    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 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.

  • Create a user in Oracle Autonomous Database [for Dummies]

    Create a user in Oracle Autonomous Database [for Dummies]

    An OCI user does not ≠ ADB user

    Creating an additional user in your Oracle Cloud Infrastructure (OCI) is not the same thing as creating an additional user in your Autonomous Database (ADB). I spent about two days (on and off) last week, wrapping my head around this. Maybe you knew this…I did not.

    Chris Hoina – 2022

    If however, you want to create an additional OCI user; one that would be able to access various resources in OCI, then please bookmark this page. This is all you need to get started.

    However, you may not even need to create this additional user (as was the case with me). While I followed the steps in that documentation (very simple/straightforward actually), it wasn’t till after I finished, did I realize that doing it was completely unnecessary. What I was really trying to do was create a new database user, not an OCI user. The steps are different, simpler.

    Federations

    Another area that I got really hung up was with “Federations”. This was a relatively new concept for me. And I’m not sure if I should even do a deep dive on how Federations work. However, let it be known, if I receive even one (legitimate) comment on this subject, I will put together a treatise on Federations in OCI.

    It took me about a day and a half to educate myself on how Federations and Identity Providers work in OCI. So, if you are reading this, and you think you’d benefit from a standalone article, then let me know. Otherwise, this page and this page are both very helpful for learning more about Federations in OCI.

    Are you me?

    If so, then you are acting as an administrator in your Always Free OCI account, as well as the database administrator. Being both is confusing for me, and maybe it is for you too. Since I don’t consider myself a traditional user (I’m blurring the lines when it comes to the different roles), I’m tasked with managing my OCI tenancy and also setting up my development environment. All this so I can begin to work on some applications/proofs of concepts (POCs) for interacting with my Autonomous Database via ORDS (Oracle REST Database Services aka our REST APIs).

    Don’t be like me and allow yourself to get too weighed down with all the technical jargon thrown your way. Naturally, our docs read like they are geared toward the System or Database Administrator. And this makes sense; remember when you are in your tenancy you’re the de-facto admin for everything. At a larger organization/enterprise a developer would probably never do any of this setup. You’d just sign in to the database directly or connect via a command line.

    Workflow for the “Every Person”

    But for me (with my limited experience), the workflow looks something like this:

    Start here

    Sign-up for an Oracle Cloud Account. You’ll be provided a Cloud Account Name* and credentials.

    *You’ll also hear this account being referred to as your Tenancy name (you can modify the name later if you want).

    When you sign in, you can quickly tell if you are the administrator by seeing if the "Service User Console" option is available. This is the administrator for the Oracle Cloud Infrastructure. Your tenancy.

    Next, create an Autonomous Database and create the “ADMIN” credentials* for said database. You can see how to do that in Lab 1 of this workshop

    REMINDER: This administrator is different that the OCI Tenancy administrator.

    Add a new user by:

    Navigating to your Autonomous Database under "Oracle Database".  It might be in your recently viewed.

    Navigating to your Autonomous Database under “Oracle Database”. It might be in your recently viewed.

    Once you've created your Autonomous Database, navigate to Database Actions. This is where you need to go to create a new user.

    After you click the database, you’ll see this screen. Click “Database Actions”. You’ll link out to the Database Actions dashboard.

    Navigate to the Administration tile, and click it. It will take you to a dashboard of all current users, along with an option to create a new user.

    Select the Database Users option in the Administration section.

    Create a new user. Afterward, you'll be provided an URL that allows a user to directly log into the Oracle Autonomous Database Actions Launchpad.


    Select the “Create User” option.

    Toggling the Web Access switch will automatically grant the user the CONNECT and RESOURCE roles.

    At a minimum, you’ll want to enable “Web Access” for this user. This will automatically grant two roles for the user:

    1. CONNECT
    2. RESOURCE

    Both are needed for the developer though.

    Here you can see all the available roles. The CONNECT and RESOURCE roles are already checked because you will have selected "Web Access" in the previous tab.

    If you want to take a look at the different roles available, click this tab. Scrolling through, you can see the CONNECT and RESOURCE roles are checked.

    HINT: You can always go back and edit the roles if more roles need to be granted. Both are needed for the developer though.

    Finish (kind of)

    You can see the URLs in this image.  This, along with the login credentials are what should be provided to the user.  They can log in to the Database Actions console directly.

    Once complete, you’ll see a URL in that user’s newly-created tile. This URL will link you out to a login page, for accessing the Database Actions console. Since you are the only user, you’ll just want to document the URL. Otherwise, as the admin, I’m assuming you’d share this with the respective recipient.

    Next Up

    From here on out, I’ll do all my development work with this account (I called mine “appdev”), to mimic what a typical user might encounter in a practical setting.

    Right now, I’ m wrapping up a python + flask + database course on LinkedIn Learning. So far, its been pretty informative. If you are interested, you can take a look as well (to see the direction I’m headed). My goal will be to use the templates in this course as a resource for connecting to my database, but with ORDS.

    I hope to have something small in the next week or so. And I’ll be sharing here, and on my GitHub as well. So stay tuned.

    Helpful resources

    What I did here was very simplified, a distillation for creating a new user in your Autonomous Database. But I’ll include some of the resources that helped get me to this level of understanding:

    Find me

    And that’s it for now. But if you want to follow along then check me out at these places…