Tag: ORDS

  • How to kill an ORDS process

    How to kill an ORDS process

    How do I “kill” an ORDS process?


    Here are the options I’ve found to “kill” an ORDS process:

    1. Use the kill command followed by the ORDS PID (i.e., Process ID) and the related JVM (i.e., Java Virtual Machine) PID
    2. Press the Control + C keys in the same terminal session that ORDS is running in (assuming it remains uninterrupted)

    It looks like we’ve included in our docs how to start ORDS (with the ords serve command), but we leave it up to you to figure out how to close down/kill/terminate an ORDS process.

    Some observations

    First approach

    The Control + C key option is the simplest option. But this might be the most impractical. If you start ORDS in Standalone mode (using the embedded Jetty server), then you might have ORDS running in its own terminal tab, kind of like this:

    Assuming this is the case, while ORDS is running in a separate tab, you can issue the ps command to review a list of the running processes:

    Afterward, you can return to where ORDS is running and press and hold the Control key followed by the C key. You’ll see a new shell prompt appear:

    There isn’t any real feedback (maybe we should change that). But if you issue the ps If you run the command in a new tab, you’ll see that both the ORDS and Java processes have been “killed.” This signifies that ORDS and the JVM are dead—RIP 🪦.

    Second approach

    If you exit out of a terminal session but ORDS is still running (which, in practice, is entirely possible), you’ll have to search for the appropriate PIDs. The easiest way I’ve found is to issue the ps command (like you saw before) and then issue the kill command plus the relevant PIDs.

    I’m unsure if you need to “kill” the Java process and ORDS. I assume that you do. The only basis I have for this assumption is that when you use the first approach (see above), both the JVM and ORDS are killed. So, I’m attempting to mimic what automatically happens when using the Control + C option.

    Issuing the kill command results in the following message (which you’ll see, assuming the original ORDS terminal window is still viewable):

    And if you reissue the ps command, you’ll see both the ORDS and JVM PIDs have disappeared:

    Summary

    Since I’ve only tested this in ORDS Standalone mode, I can’t comment on Apache Tomcat or Weblogic. I’m assuming they both function very similarly. But if you are using ORDS as both an admin (to get it up and running) and a developer, then some of these commands (that we take for granted) may not be obvious to you. So, as rudimentary and fundamental as this article appears, I hope it helps at least one person early on in their database or ORDS journey.

    Call to Action

    And if you haven’t seen it by now (and why should you? I haven’t plugged it yet), we’ve made a YouTube playlist to accompany one of our more popular ORDS LiveLabs. You can find the LiveLab here.

    And here’s the complete playlist:

    Enjoy 🤗

    That’s all for now, folks!

    Follow

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

  • Extending ORDS with a Java plugin

    Extending ORDS with a Java plugin

    About this article

    This article is a walkthrough and overview of setting up the example Java plugin that ships with ORDS. These plugins allow you to add functionalities and capabilities above what is possible with a standard ORDS configuration.

    In this example, the plugin allows you to use a name as a query parameter in an HTTP request. What you receive in response is a greeting from the schema plus the name you used in that HTTP request. Like this:

    I know what you are thinking…so what? And for about a day and a half, I felt the same. Eventually, it dawned on me that since this plugin relies on APIs from the Oracle REST Data Services Java API Reference, you can create a plugin for anything in that book. I’ll have to spend time in another article, stepping through the Java code. I’m realizing that you don’t need to know Java fluently to understand what is happening with this application. So, if you are a Java newbie (like me), this is a great way to get acquainted with the language.

    Chapter 11 Extending ORDS Functionality with Plugins

    Below, you’ll see the exact text from Chapter 11 Extending ORDS Functionality with Plugins of the ORDS Developer’s Guide. If you want to follow along, you won’t have to switch between the official docs and this blog post.

    Further still, I’ve broken up the steps in Section 11.1 Plugin Demonstration Example into individual sections. I’ve added some commentary and images throughout. Some directions were challenging for me, and pictures are always helpful.

    Oh! And there’s a special “How the hell do I set up Ant?!” section, too. Since it took me about 30 mins to figure out how to download, install, and configure the damn thing. It would be best if you didn’t have to suffer like I did 😘.

    The unadulterated ORDS docs

    HINT: Click the expand/collapse arrow to view the full text.
    11 Extending ORDS Functionality with Plugins

    This chapter explains and provides examples on using ORDS plugin framework.

    ORDS has a plugin framework that allows you to add your own custom functionality into the ORDS web application. Plugins can be added to the ORDS runtime by placing the jar files in the lib/ext directory. The ORDS distribution contains the source for example plugins. The plugin examples can be built using Apache ant, a software tool used for automating the build processes.

    11.1 Plugin Demonstration Example

    This section shows how you can locate and build a plugin demonstration example.

    The plugin-demonstraion example is at examples/plugins/plugin-demo location and contains the source for a HttpServlet that gets a database connection injected at runtime. The servlet uses that JDBC database connection to run a query in the database and return a response at runtime.

    Perform the following steps to build and use the demonstration example:

    1. Change the directory to examples/plugins/plugin-demo
    2. Run ant to build the examples/plugins/plugin-demo/built/plugin-demo.jar file
    3. Copy the plugin-demo.jar to the ORDS distribution lib/ext directory and start an ORDS instance.
    4. Invoke the servlet using the following URL pattern:http://server/ords/schema/demos/plugin?who=somebody
      1. For example: http://localhost:8080/ords/hr/demos/plugin?who=scott where ORDS is configured with a default pool and HR is an alias for a REST Enabled Schema in that database.

    The details of developing and deploying Java based plugins is available in the Oracle REST Data Services Java API Reference book.

    See Also:

    Route Patterns Specification

    Getting Started Guide

    Developer Guide

    Chapter 11.1 Steps, expanded

    Locate the directory

    Change the directory to examples/plugins/plugin-demo.

    This step assumes you have Ant installed and are ready to build the application. In other words:

    1. You’re in your Terminal, Shell, or Command Prompt.
    2. You have issued the cd command, so you are in the /plugin-demo directory, and
    3. You’re about to run the ant command on the command line.

    HOWEVER…If you don’t have Ant installed, this exercise won’t work. Let’s get Ant.

    Get Ant

    Introducing a special section dedicated to making Ant acquisition much easier!

    You’ll need to visit the Apache Ant main page. But don’t waste your time navigating to the Downloads page. It makes no sense.

    Just click Manual (located under the Documentation section). A new page will appear, and you’ll see a Table of Contents. Click “Installing Apache Ant.” Then, click “Getting Ant.”

    Once you are at the “Getting Apache Ant” page, you’ll see five steps. For Step 1, I’ll assume you have Java installed.

    Also, if own a computer, one that you bought and didn't build, there's like a 99% chance you have Java installed. It just might not be the latest version.

    In Step 2, we need the Binary Distribution. Of the four links there, I chose the .zip file. It seemed the most normal (I haven’t seen .tar files since working on IBM Z 😵‍💫).

    Step 3 states to unzip and then place the directory somewhere. I’ll show you what I did in a second.

    Step 4 instructs you to do some things and references the Setup page for details.

    If you don’t understand any of the instructions in Step 4, please feel free to click the Setup link. You’ll be presented with even more steps that don’t make sense.

    Let me show you how I have this all set up. If you are on a Mac, you can see how I have this bin directory and ANT_HOMEvariable set up. I open a new Terminal and issue the following command: open .zprofile

    At the bottom of the file, you’ll see how I have Apache Ant set up for both bin and ANT_HOME. Set yours up like this: save the file, then restart your Terminal.

    Wut did I just do? My idiot's take...After doing this, you can now execute the ant command in any terminal. And you've also "pointed" to all the required dependencies (i.e., ANT_HOME) for Ant to make the magic happen.

    After restarting, you can issue the ENV or env command to review your environment settings. Here, you’ll see the Ant bin is in my PATH. You can now build with Ant 👍🏻.

    And to answer the question, “Where do I put the Ant files?” I placed them in my Library on my Mac.

    The Apache Ant docs mention two commands for testing to see if Ant is working as expected. The first command ant can be issued anywhere.

    However, I assume if you issue this command in a directory where source files are waiting to be built, then you'll actually end up building a new Java app. I believe this is why they recommend issuing this command in a new shell. It assumes you don't have files there, waiting to be built.

    Secondly, you can issue the ant -version command. If you have Ant installed, you’ll see the version information.

    Okay! You now have Ant; let’s get back to the colony 🐜🐜🐜.

    Build with Ant

    Run ant to build the examples/plugins/plugin-demo/built/plugin-demo.jar file

    README: The plugin-demo directory is located in your ORDS product folder. This step assumes you have ORDS installed and configured for Standalone, Weblogic, or an Apache Tomcat server.

    Next, “cd” to the plugin-demo directory. I am lazy, so I right-click on the folder and choose to open a “New Terminal at Folder.”

    The official instructions state that the source files for this Java plugin are contained in this folder. You do not need to modify these files for this to work. They are shipped with every ORDS version, so they will always be there, even when you upgrade (have you upgraded to the latest ORDS?).

    You are not losing your mind. The build literally takes about 5 seconds. You’ll see a printout like the image below. You only need to be concerned with the dist: row at this stage. That is your plugin, and it is ready to be deployed.

    Jar configuration

    Copy the plugin-demo.jar to the ORDS distribution lib/ext directory

    Navigate back to the plugin-demo directory. While you’re there, you can expand the sub-directories. You will see the original src directory and the new built directory, with its sub-directories and files. Copy the plugin-demo.jarfile.

    Return to your ORDS product folder and place the plugin-demo.jar file in the lib/ext directory. Just so we are clear, there is another lib directory nested in examples/plugins/lib. You DO NOT want to move the jar to that folder; you must go to the ords product folder/lib/ext directory.

    Starting ORDS

    Start an ORDS instance with the ords serve command.

    Okay, we are almost there. I have a couple of ORDS configurations here. One is for working in ORDS Standalone mode with a 23ai database via a Podman container (the Podman image can be found here), and another is a Customer-managed ORDS installation (identified by the moviestream23ai_medium database pool name) for working with my Autonomous database (in OCI).

    ORDS customer-managed installation instructions can be found here. 

    Test the plugin

    Invoke the servlet using the following URL pattern: http://server/ords/schema/demos/plugin?who=somebody.

    For example: http://localhost:8080/ords/hr/demos/plugin?who=scott where ORDS is configured with a default pool and HR is an alias for a REST Enabled Schema in that database.

    Now that ORDS is up and running, we can do some testing. In this first example, I challenged my creativity by using the name “Chris” for the test. Here, I’m issuing a request to the plugin. Of which it successfully returns the following message:

    [ORDS REST-enabled schema] says hello to: [the name passed as a query parameter in the original request].
    
    OR
    
    ORDSTEST says hello to: Chris

    Testing with the Podman container:

    And with the Customer-managed ORDS with Autonomous database installation:

    Success! I am me!

    Next steps

    You’re probably asking yourself, “Now what?” Well, here is what I would do:

    1. Review the above steps again and on the docs for building this Java plugin.
    2. Look at our support for JavaScript plugins and the extra configuration step if you use GraalVM for your JVM.
    3. Start learning Java. The two resources I’ve been reading in my spare time:
    4. Check out Peter’s ORDS plugin example too
    5. Make your own plugin and share it with me so I can try it out 😬

    Oracle CloudWorld 2024 updates

    And finally, yes, we are fast approaching this year’s CloudWorld conference. If you didn’t see my last post on our teams’ activities, review it (and hopefully register for at least some of our sessions). Details are here. And that’s all for now!

    Follow

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

  • ORDS 24.2 Release Highlights

    ORDS 24.2 Release Highlights

    NOTE: This can be thought of as a "companion piece" to the official ORDS release notes 🤓.

    What is new in Oracle REST Data Services 24.2?


    New HTTPS Response Status Codes

    In response to various support requests and internal feedback, we've expanded on the existing ORDS Status Codes (the current list can be found here)!
    Why? The TL;DR is that in many cases, the error or exception caught was being "bucketed" into a category of codes, like a 400 Bad Request or a 500 Internal Service Error. While true, we can be more explicit with some of these error codes. Now, we just have to ask, at what level do we stop?!

    Ahem, this sounds like our entire dev team is lazy; it’s quite the opposite. Let me illustrate what’s actually going on with an example from one of our related tickets.

    Say a user receives a 503 Service Unavailable server error response. In their Java logs (trace files, access logs, etc.), they also observe an oracle.net.ns.NetException error. While classifying this as a 503 is accurate, we can do better. For instance, you may encounter an oracle.net.ns.NetException with any of the following ORA codes:

    ORA-12514 TNS: The listener does not currently know of the service requested in the connect descriptor
    ORA-12506 TNS: listener rejected connection based on service ACL filtering
    ORA-12529 TNS: connect request rejected based on current filtering rules
    ORA-12516, TNS: The listener could not find an available handler with a matching protocol stack

    In this example, moving forward, instead of receiving a 503, you’ll now receive a 571 server error response.

    We’ve done this for several other scenarios, too! Why keep everything generic, as a 503, when we can be more discrete and specific with the information provided? It makes no sense. So, by shifting some of these exceptions to unique response codes, we make it easier for you to identify what is happening.

    What if everything was either 200, 400, or 500? Could you imagine?! It would be nearly impossible to quickly identify what is happening in your stack.

    ORDS Central Configuration

    ORDS now supports deployment in a Central Configuration type deployment.

    This one is big. The short version is that we’ve made it so you can dynamically start up and shut down individual ORDS “nodes.” This requires three main pieces, but I’ll try to be brief.

    This all assumes you’ve already configured ORDS globally (i.e., you have a global.xml file) as well as your database pools (i.e., the default.xml, database_pool_one.xml, database_pool_two.xml, etc. files). But the idea is such that you’ll have stored in a central Vault/Secrets storage/Key Store your:

    1. ORDS global configuration – which will be in a JSON format (basically a JSON version of the global.xml file)
    2. ORDS database pool configuration/s – the individual configuration files for your ORDS “nodes” (again, just JSON versions of your database-pool.xml files)
    3. A mechanism for the ORDS_PUBLIC_USER to authenticate with the Vault or Keystore (this could also be something as simple as an ORDS webhook that has been protected with an OAuth2.0 client)

    *And a conditional fourth item, the makestore or orapki utilities (for creating SSO Wallets).

    One way to “do” this (we’ve tested this internally; we just can’t, and won’t endorse, a “one-size fits all” method) is to store your “secrets” in a vault-type service and retrieve them securely and dynamically.

    NOTE: The vault contains "secrets," including your global configuration and database pool files. Each of those secrets might be behind an OAuth 2.0-protected endpoint/s.

    Separately, and before starting up ORDS, you’d create an SSO Wallet with the credentials for the ORDS_PUBLIC_USER, its password, and additional hostname information (all can be found in our documentation). You’d also choose where to store that Wallet.

    Then, before launching ORDS, you’d include two Java options:

    1. Your wallet location (so when ORDS starts up, it is aware of the location of the Wallet and the credentials) and
    2. The REST endpoint (the URI) for your global configuration

    Authentication can take several forms here. You can use Basic Authentication (but…don’t, even though we support it, perhaps for testing, but please don’t use it for production), JWTs, or OAuth 2.0. Once the ORDS_PUBLIC_USER has authenticated with its credentials/token, ORDS can acquire its global configuration.

    When that HTTP request comes across ORDS, the database pool name can be passed as a header value (we can also read the database pool name if it is appended to the beginning of the URL) and used as a “search” value to retrieve the relevant database_pool-config.json file.

    That is ORDS Central Configuration in a nutshell. And since I lost you, I’ll have to write a follow-up blog on this. I don’t blame you; conceptually, it’s tough to envision, but it’s pretty simple in practice. The basic components are laid out in our documents.

    OCI Monitoring of ORDS

    ORDS now provides a create_alarms.sh script to create ORDS alarms using OCI Monitoring Services, which can be found in the [ords product folder]/examples/ords-metrics directory.

    How shall I explain this without getting in trouble with our legal department…? This is being rolled out globally right now for the Oracle Autonomous database. Before this ORDS release, errors or exceptions would be caught and streamed to an OCI metrics dashboard. And you might have seen an ORDS-related exception with a 404 or a 503. Unfortunately, the root-cause analysis would have already been off to a bad start. Because most of these exceptions aren’t just 404s or 503s. There had yet to be a mechanism to make some of these exceptions more discrete. And that is where this create_alarms.sh script enters the chat.

    If you review the now-included create_alarms.sh file, you can get an idea of what will now be streamed to your OCI Metrics/Monitoring Explorer. We’ve made it so the ORDS access logs and more appropriate response codes can be streamed to OCI. This makes root-cause analysis and troubleshooting far more straightforward. So if you elect to configure a customer-managed ORDS node, then be sure to take advantage of this new capability.

    A new standalone configuration option

    New standalone.access.log.retainDays configuration option for ORDS standalone. 

    Users can now customize the number of days before access log files are overwritten. The default is 90 days, but you can now select the exact number. This, along with numerous other standalone settings can be found in the Understanding Configurable Settings appendix of our ORDS Installation and Configuration Guide.

    Java options in the ORDS CLI

    Users may now include Java Options parameters when executing ORDS CLI commands. 

    For instance, a user may execute the following command:

    ords --java-options "-Djava.util.logging.config.file=logging.properties"

    Or, maybe you are testing the new ORDS Central Configuration strategy. In that case, you might want to include something like this when starting ORDS:

    ords --java-options "-Dconfig.url=//localhost.8080" serve

    In the above example, you’ve told ORDS to start up, and then, first thing, go to the target URL to retrieve ORDS’ global configuration settings.

    You may wonder about JAVA_OPTIONS and JDK_JAVA_OPTIONS; how are those settings impacted? Well, here are some essential details:

    1. ords --java-options only apply to the current ORDS execution.
    2. In order of precedence, options will be picked up like this:

    JAVA_OPTIONS are of the highest precedence >>> then >>> ords --java-options >>> then >>> JDK_JAVA_OPTIONS

    INFO: Where conflicts arise, ORDS will pick up and use the left-most option.

    Jetty 10.0.21

    ORDS standalone updates the embedded Jetty Web Sever version 10.0.21.

    There’s not much to say here, but if you’d like to learn more about Jetty, I’m including the Operations and Programming guides. When using ORDS in standalone mode, the idea is that Jetty is just there—and it just works.

    We’ve architected Standalone mode so that any ORDS configuration can be achieved via the ORDS CLI or by directly manipulating the XML configuration files (while you can do this, you shouldn’t; you might mess up one of the properties in the files). Designing ORDS Standalone mode this way makes it so you don’t really need to do anything Jetty-related. I’m just including the docs if you want something to read on your lunch break 🤪.

    Database Actions’ Data Pump

    Data Pump allows you to delete jobs and their files from within the Database Actions Data Pump UI. And the Data Pump Import Wizard features some major upgrades and visual enhancements. 

    Users who have DBMS credentials to access Oracle Cloud Services (via the DBMS_CLOUD.CREATE_CREDENTIAL PL/SQL procedure) can now perform Data Pump Imports from OCI Buckets (previously, it was Resource Principals only). And we’ve added the following abilities/changes:

    1. Buckets from any Compartment level are now accessible
    2. Auto-generated Import Patterns for DMP Files are now included
    3. Automatic mapping for Schemas and Tablespaces is now present
    4. A new “Append Timestamp to Log and Job Names” toggle option has been added

    NDJSON files

    Users can now import Newline Delimited JSON (NDJSON) or .ndjson files into the SQL Worksheet.

    This actually arose from a bug we encountered. After some investigation, the user attempted to import a “Newline Delimited” JSON document. I’m not familiar with this document type, but cursory research reveals the following:

    There is currently no standard for transporting instances of JSON text within a stream protocol apart from [Websockets], which is unnecessarily complex for non-browser applications.

    A common use case for NDJSON is delivering multiple instances of JSON text through streaming protocols like TCP or UNIX Pipes. It can also store semi-structured data.

    NDJSON spec Github

    If you’d like to learn more about this specification, I recommend visiting this site. It doesn’t seem affiliated with the project, but it is very informative compared to the official NDJSON GitHub page.

    Are you using NDJSON now? Or did you just learn of its existence? Do you think you’d start using it, or do you have any use for it? Let me know. I’m curious about the potential applications.

    New Walkthroughs (Tours) for Charts and Data Modeler

    Updates to the Database Actions Launchpad. 

    We continue to refine the Launchpad, and these are but two more examples. The Data Modeler and Charts pages have Walkthrough Tours (and documentation too):

    What are your thoughts on the new Launchpad? Do you use the “pin” feature? Is it intuitive? What else could we include or improve? Or is it perfect (it’s perfect, isn’t it? …I knew it!)

    Java

    ORDS requires Oracle Java or Oracle GraalVM Enterprise Edition 11, 17, or 21 to run ORDS 24.2.

    If you intend to do anything GraphQL-related, use GraalVM 17. Instructions for downloading can be found here. Just make sure you set your JAVA_HOME to GraalVM 17 so that when ORDS starts up, it does so with GraalVM 17! At this time, ORDS GraphQL support only works with GraalVM 17.

    That’s it for this release. Download the latest and enjoy!

    Oh, and if we missed your enhancement this time, let me know. We’ll add it to one of our sprints!

    Follow

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

  • Random Access Memories: ORDS and JWTs

    Random Access Memories: ORDS and JWTs

    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.

    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!

  • Build an ORDS API Resource Module, GET request with JavaScript fetch, display in HTML

    Recap

    This post is a continuation of a previous one, which can be found here. In this post, I’ll:

    If you are coming from the previous related post, then you’ll recall I used the following SQL query:

    My next step is to take this SQL and bring it to the REST Workshop, where I’ll turn it into an API.

    REST Workshop

    There are several ways you can navigate to the REST Workshop. Typically, I return to the Database Actions LaunchPad. From there, I select REST.

    The Handler code

    I've already created my Resource Module, Template, and Handler. I kept everything default, with no authentication enabled.

    The only thing I changed was the SQL query. I removed the final line, fetching the first 10 only. I want to be able to control the pagination of the API. If I were to keep that last line, this eventual endpoint would always only return the first 10 rows. And what if I want the next ten rows thereafter? Well, if I hard-code this, then I can’t really make that work. So, I chose to leave it open-ended.

    Technically, it is NOT open-ended because I retained the default pagination of 25. But, by removing that fetch first 10 rows condition, I can now fetch ALL rows that fit those parameters (in increments of 25).

    If I visit this new endpoint, it will appear like this:

    And if I collapse the items, you’ll see something that is EXTREMELY confusing. If I removed that fetch first 10 rows condition in the original SQL query, then why do we see a limit and offset of 10?

    The answer is because I actually set the Items Per Page equal to 10 (in the Resource Handler). This is the REST equivalent of a dirty joke. Consider yourself roasted…

    JavaScript

    With that endpoint live, I can take the API and drop it into some sample JavaScript and HTML code.

    JavaScript and HTML

    I learned a great deal about this JavaScript by reviewing this YouTube video. That is where I learned how to map through the items of my ORDS payload. And there was a refresher on JavaScript string interpolation (with template literals) too!

    PAUSE: Don't be too intimidated by string interpolation and template literals! Read the link I included, and take your time. If you are coming from Python, its similar to Jinja (when using Flask) and f-string literals 🙃.

    You can see that I’m using the map() constructor to iterate through all the data in my JSON payload. Remember, this was the payload in the items portion of my endpoint!

    I believe the item in list.map((item) is a reference to an individual item inline 4’s data.items. The reason why I think this is because if I change the items in lines 7-10 in my JavaScript to something random, like the name bobby, things start to break:

    However, if I change everything back to item, and start the live server in VS Code, I’ll be met with the following rendering:

    That’s it, though. Combining the ORDS API, the Fetch API, simple JavaScript, and HTML will allow you to create this straightforward web page.

    Reviewing Inspector, Console, Network

    I also have a few more screenshots, one each for the HTML Inspector, Console Log, and Client/Server Network. All of these show what is happening under the covers but in different contexts.

    Inspector

    In the Inspector, you can see how the JavaScript map() constructor plus the document.querySelector() in line 18 of the JavaScript code work in tandem with line 12 of the HTML script to display contents on the page:

    Console

    Here, you can see the items in the Console. This is because we added console.log(item)in line 19 of the JavaScript code.

    Network

    Finally, you can see the 200 GET request from our ORDS API. Then, on the far right of the screen, you can see the JSON payload coming from that same ORDS endpoint.

    Admittedly, the way the “Cast” is displayed is not correct. That is yet another array of cast members. And I’ve yet to learn how to structure that correctly. So, if you are reading this, and you know, let me know!

    Finally, all the code you’ve seen in this post can be found in my moviestreamjs github repository.

    That’s all for now!

    Follow

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

  • Create a view from a JSON Collection and REST-enable it with ORDS

    The DBMS_CLOUD PL/SQL Package

    You can use this PL/SQL procedure (in the DBMS_CLOUD package) along with the file_uri_list URL (seen in the code below) to create and then add JSON documents to a JSON Collection (good info on JSON Collections in the Oracle database).

    In this example, we call this collection Movie_Collection.

    -- create and load movie json collection from a public bucket on object storage

    begin
    dbms_cloud.copy_collection (
    collection_name => 'MOVIE_COLLECTION',file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/movie/movies.json',
    format => '{ignoreblanklines:true}');
    end;
    /

    Create a view

    With that JSON collection in place, I can then create (aka I’ll continue stealing this code from the same LiveLab) a View of it using the following SQL code:

    /* Create a view over the collection to make queries easy */

    create or replace view movie as
    select
    json_value(json_document, '$.movie_id' returning number) as movie_id,
    json_value(json_document, '$.title') as title,
    json_value(json_document, '$.budget' returning number) as budget,
    json_value(json_document, '$.list_price' returning number) as list_price,
    json_value(json_document, '$.gross' returning number) as gross,
    json_query(json_document, '$.genre' returning varchar2(400)) as genre,
    json_value(json_document, '$.sku' returning varchar2(30)) as sku,
    json_value(json_document, '$.year' returning number) as year,
    json_value(json_document, '$.opening_date' returning date) as opening_date,
    json_value(json_document, '$.views' returning number) as views,
    json_query(json_document, '$.cast' returning varchar2(4000)) as cast,
    json_query(json_document, '$.crew' returning varchar2(4000)) as crew,
    json_query(json_document, '$.studio' returning varchar2(4000)) as studio,
    json_value(json_document, '$.main_subject' returning varchar2(400)) as main_subject,
    json_query(json_document, '$.awards' returning varchar2(4000)) as awards,
    json_query(json_document, '$.nominations' returning varchar2(4000)) as nominations,
    json_value(json_document, '$.runtime' returning number) as runtime,
    json_value(json_document, '$.summary' returning varchar2(10000)) as summary
    from movie_collection
    ;

    Here is what the code looks like in the SQL Worksheet (a part of Database Actions).

    With that View created, you could go one step further and query with even more specific SQL. In this case, I’ll query the View but exclude any entries where a movie cast does not exist:

    Select
    title,
    year,
    gross,
    cast
    from movie
    Where cast is not null
    Order By 3 DESC nulls last
    Fetch first 10 rows only;

    Here is the SQL, with the Script Output below:

    ORDSify it®

    With ORDS, we can REST-enable pretty much any database object we want.

    I have objects, Greg. Can you REST-enable me?

    But after spending a few minutes with this collection, I found the MOVIE View to be the easiest, most sensible object to highlight. It’s a straightforward process, with primarily right-mouse clicks.

    From the Navigator Panel, select Views from the list of available database objects.

    Then, right-click on the Movie View, and select REST, then Enable.

    A slider will appear, for this example, I’ll keep everything default and click Enable (no authentication either, I’m being lazy).

    A Confirmation notification will appear in the upper right-hand corner of the browser 👍🏻.

    Navigate back to the Movie View, and select the cURL command option. Twasn’t there before, tis now!

    Select the GET ALL endpoint, and copy the URI. JUST the URI portion!

    Open a new browser tab, or window, and navigate to the URI. You’ll see everything in the Movie View now!

    Why a view?

    Yeah, good question. There is probably a performance improvement with using Views. Based on what I’m finding, I can’t definitively say, but they require zero storage, so that’s at least a savings on some resource somewhere. Additionally, I think the customization is pretty compelling, too. What do I mean by that? Well, allow me to elucidate:

    Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.

    Oracle Database Administrator’s Guide Release 23

    In this case, the Movie View returns everything found in the collection. I can subset this even further though; by taking that SQL query and REST-enabling it, too. I will, but in a future post.

    For now, I’ve left you with an easy way to REST-enable a View (In this case, based on a JSON Collection) that resides in your Autonomous Database.

    If you want to try the LiveLab (which you should, as it’s easy and VERY informative), go here. You’ll need an Always Free OCI account, too (so you can provision an Autonomous Database). You can sign up here.

    Oh, and we are entering into the season of Cloud World 2024, so mark your calendars 🤪!

    That’s all for now 😘.

    Follow

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

  • The one ORDS command you NEED to know!

    The one ORDS command you NEED to know!

    ords config list --include-defaults

    That’s it. Goodbye!

    I’m kidding, there’s more. But if you don’t feel like reading anymore, bookmark this post and return when you’re ready.

    Assuming you have ORDS installed, you too can execute the ords config list --include-defaults command to reveal almost all the configuration settings for your ORDS installation.

    Here is what my configuration looks like:

    Configuration settings: what am I seeing?

    This command is a quick way to see all the settings from your .XML configuration files (i.e., the settings.xml and pool.xml files), including other settings automatically configured for you when you first ran the ords interactive installer.

    Read on to explore further…

    Version, config folder location, and pool information

    I use this first section as an easy, convenient way to determine the ORDS version I’m running. Additionally, you can verify the location of your configuration folder (in case you forget). You can also verify the database pool (default is the default name for the pool unless you modify the name) you are using.

    Pool and global settings

    Not much here that you probably already don’t know. However, in the future I will look at the features associated with the database.api.management.services.disabled = false property (also, I think the way this is written is a referred to as a “logical negation”, and it hurts my brain to read).

    Read more about this service here. But in short (and once you’ve created the requisite user), you can explore various services such as:

    • DBCA Jobs, available methods: DELETE, GET and POST
    • DBCA Templates GET
    • Oracle Home Environment GET
    • PDB Lifecycle DELETE, GET, POST
    • Open Service Broker DELETE, GET, and PUT

    Debug and Error

    My settings are false (these are the default settings). But if I were to, for instance, set debug.printDebugToScreen = true, I would then be able to see any error messages in the browser.

    I can change the responseFormat to always display as JSON, HTML, or AUTO (i.e., Automatically determine the most appropriate format).

    Did you know you can create custom HTTP error pages in ORDS? These two error.properties appear to be associated in some way. So if you were two create custom error pages, you’d probably need to consider the format as well. Nonetheless, could you imagine the fun you could have coming up with something totally unique to your application?

    GraphQL and SQL Developer Web

    ORDS supports GraphQL now; did you know?! I just set up my local installation (it wasn’t too bad once I figured out how to properly set my Java to GraalVM 😑), so I can start learning GraphQL queries.

    Did you know ORDS ships with the GraphiQL IDE now? Learn how to set it up here.

    Cookies and ICAP

    I honestly wouldn’t have known ORDS could offload virus scanning to ICAP (Internet Content Adaptation Protocol) servers unless I looked at what was actually in the configuration settings. I’m not sure if I’ll configure this anytime soon, but maybe you will.

    Bookmark this link for future reference!

    Java Database Connectivity (JDBC)

    I am NOT going to spend much time here. I still need toggle these parameters and experiment more. However, I will point out that the default setting for maximum JDBC connections is 20 (jdbc.MaxLimit setting).

    jdbc.MaxLimit=20 is probably too low for a production environment. I’ve left it as-is because it’s just me, and I’m doing everything locally in my Podman container.

    Suppose you need to familiarize yourself with JDBC or Universal Connection Pools (UCPs)? In that case, we should both read the introduction sections of the following guides:

    MongoDB

    I have spent little time with MongoDB, but from what I understand, the Oracle Database API for MongoDB translates the MongoDB wire protocol into SQL statements executed by the Oracle.

    What I’m inferring from our docs is that once you’ve migrated your data from a MongoDB into a supported Oracle database, you (or your application) can keep talking “MongoDB speak,” and at least in this case, ORDS will be able to interpret this Mongospeak and query the database on your behalf 🤯!

    If this describes you or your use-case, you’re in luck; I found some excellent resources!

    Security

    You’ll notice, no red arrows here. I have yet to spend much time with this section. However, I want to draw your attention to the security.jwks.[etc...] and security.jwt.[etc...] properties.

    In ORDS 23.3, we introduced JSON Web Tokens (JWTs) support, so these properties very much concern that new functionality.

    Good resources to bookmark:

    Standalone (Jetty)

    The nice thing about ORDS is that you can use the embedded Jetty server as a local web server for testing. This section shows most of the essential settings for running Jetty in “Standalone mode.”

    I use the term “testing” because our docs state, “the default configuration of Jetty is optimized for the most common ORDS use cases.” I interpret this as, “This is designed to expose you to Jetty (and make it easy to get you up and running), but you’ll probably need to adjust this according to your own requirements.”

    The only things I want to point out here are the standalone.doc.root and standalone.static.context.path properties. These settings will look familiar if you’ve ever performed an APEX installation (available herefor free, BTW). 

    However, if you want to deploy custom HTML, CSS, and image files, you can configure this for ORDS. We have an overview in our docs here.

    Okay, that’s it for now. Thank you for choosing to waste your time with me.

    What’s the point of this post?

    There was no point to this post. I’m constantly wasting time researching technology and techniques I don’t need to know. However, in this case, I’ve hopefully:

    • left you with at least one helpful ORDS command-line command (ords config list --include-defaults), and
    • provided you with some helpful explanations and resources on what is contained in your ORDS installation (again, this list is NOT exhaustive)

    And if you found this post helpful, please share it!

    Follow

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

  • ORDS, JavaScript, the Fetch API, and HTML

    ORDS, JavaScript, the Fetch API, and HTML

    I found JavaScript and HTML code here and here and “remixed” it to work with one of my sample ORDS APIs. Here is the result:

    the-html-in-browser, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    ORDS + JavaScript + Fetch API + HTML

    Impressive, no? Care to try it out? Read on friend!

    References

    I’ll front load with all the necessary stuff. That way, you can bounce if you don’t feel like reading. You’ll get the gist if you follow along with what I’ve provided.

    Much of what I learned came from the MDN Web Docs site. I would get acquainted with the following pieces of code (or at least have them handy) since they heavily influenced me (a.k.a. plagiarized).

    MDN Web Docs

    I either used or referenced these files in my version of the code. They are all available in the two links I mentioned above, but I’m adding them here for convenience (in case you need to leave or want to review while on this page).

    ORDS code

    Here are a few things to point out:

    1. In line 16 of my index.html code, I referenced the JavaScript code (script.js) separately. This approach achieves the same effect as embedding the JavaScript directly into the HTML file (as seen in the MDN’s version of the index.html file).
    2. The script.js contains the Fetch API and the JavaScript concept of “promises.” The following were super helpful for me. Maybe the will be for you too:
    3. The JSON file contains an example of what an ORDS GET request response looks like (if viewing in the browser). The structure is nearly identical if you compare it to the MDN JSON file.
      • This means you can take their HTML and JavaScript code and populate it with an ORDS endpoint and [subsequent] response data (i.e., the stuff you see in this localhost.json file).

    Live Server

    I’m also using the Live Server extension for VS Code. If you don’t have it, you’ll need it to run the code I’ve provided. You can download it from the VS Code Marketplace here.

    mentioning-live-server-for-this-exercise, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    You’ll want Live Server for this one!

    How I met your Mothra 👾

    Where to start? From the beginning, right? What you see below are two JSON files. On the left, from ORDS. On the right, from the MDN Web Docs sample code (direct link to that file).

    Comparing JSÒN

    comparing-ords-and-mdn-json-files, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    ORDS on the left, MDN on the right.

    They are nearly identical. They are both a JSON object {} comprised of key: value pairs, where the first key’s value is an array []. In both files, this array has more objects {}. And each of those objects has its own key: value pairs…marone 🤌🏼!

    I mention all this because this makes the existing code easy to work with. Which you’ll see shortly.

    Comparing JavaScript

    Next is the JavaScript code; I’ll compare both my version and the MDN Web Docs version.

    comparing-javascript-versions-with-ords-apis, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    ORDS on the left; can you spot the differences?

    You’ll notice that a lot of the code is quite similar. I kept it this way, so I wouldn’t unintentionally break anything. The main differences in my code are the:

    1. const ordsAPI on Line 1 (as opposed to referencing a JSON file).
    2. Naming conventions in lines 14-27.
    3. listItem.append(); on line 29 is heavily remixed (I did this so I could create individual lines for each entry).
    4. Templating in my code (i.e., wherever you see the little ``` marks; they allow you to embed text directly into the HTML) I use A LOT more of it!

    About the ORDS JSON Object

    If you were to navigate to your ORDS endpoint, it would look like the images below. I’m including them for a couple of reasons:

    1. You can see those key: value pairs in a different presentation.
    2. These images help connect what is coming through in that GET request and what you see in the JavaScript code.
    an-example-of-ords-response-in-browser, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    The items key with its value (an array).
    the-raw-ords-response-in-browser, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    Remember the other key: value pairs, too!

    Reviewing the HTML

    Assuming you’ve started up Live Server (along with setting up your environment to mimic my own), you’ll immediately see this beauty of a web page. This image alone doesn’t tell a complete story, though.

    the-html-in-browser, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    Review line 29 in the JavaScript code; it’ll help to “connect the dots.”

    However, when you open up the developer tools in your browser, you’ll see what is happening under the covers.

    1. Live Server starts up, sees the index.html file, and “serves” it up.
    2. In that HTML file is a reference to script.js; the JavaScript is run.
    3. The JavaScript composes a list and then appends all the data you see here (on screen):
    html-with-developer-tools-to-inspect-html-and-javascript, chris hoina, db tools, ords, oracle database, javascript, html, ords api, oracle rest api
    With developer tools open, you can see the HTML. This HTML should look similar to lines 12-27 of the JavaScript code.

    Summary

    After writing this up, I’m realizing this clearly needs to be a video. But if you get it, great! Otherwise, stay tuned!

    There isn’t anything ground-breaking here. I’m highlighting an example of manipulating existing ORDS JSON objects (with the Fetch API) because I hadn’t seen anything quite like what I am presenting here.

    Also, the web page that I’m showing is very, very basic. I’m neither a UX nor UI designer, so this is what you get, folks!

    The main point is that the ORDS APIs are effortless to work with if you have a fundamental understanding of manipulating JSON objects using JavaScript. They are no different than what you see out in the wild.

    Some follow-up

    I want to take this and add some React to it. And I’d also like to add authentication (Basic, OAuth 2.0, and Java Web Tokens). But baby steps.

    Okay, that’s all for now, folks, Sayonara!

    Follow

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

  • Tinkering: a SQL script for the ORDS_ADMIN.ENABLE_SCHEMA procedure

    Tinkering: a SQL script for the ORDS_ADMIN.ENABLE_SCHEMA procedure

    Post-ORDS installation

    Once you’ve installed ORDS, you need to REST-enable your schema before taking advantage of ORDS (I used to forget this step, but now it’s like second nature).

    RESOURCES: I've discussed ORDS installation here and here. I'd check both pages if you're unfamiliar with it or want a refresher. 

    ORDS.ENABLE_SCHEMA / ADMIN_ORDS.ENABLE_SCHEMA

    While logged into your database with SQLcl, you can issue the following commands to not only create a new user but grant them the required Roles (and the underlying Privileges) and REST-enable their schema (aka “ORDS-ifying” a schema):

    /* Remember to remove the brackets when you run this code */ 
    
    Create User [username] Identified By [password];
    Grant Connect to [username];
    Grant Resource to [username];
    
    Grant Unlimited Tablespace to [newuser];
    
    /* This PL/SQL procedure assumes you are logged in as the SYS.
    If you are logged in as that new user, AND HAVE BEEN GRANTED
    THE DBA ROLE, then you can execute the ORDS.ENABLE_SCHEMA 
    procedure */
    
    Begin
        ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
    End;
    /
    Create User [username] Identified By [password];
    Grant Connect to [username];
    Grant Resource to [username];
    
    Grant Unlimited Tablespace to [newuser];
    
    Begin
        ORDS_ADMIN.ENABLE_SCHEMA(p_schema = > '[username]');
        Commit;
    End;
    /

    Automate because lazy

    But even that is too much work for me, so I took a stab at automating this via an SQL script. As you can see, the above commands are simple and repeatable (a perfect candidate for automation). And since I’m constantly adding and dropping users for various reasons, copying and pasting code from an old blog or writing everything by hand gets annoying. Additional reasons for automating:

    1. laziness
    2. a desire to improve SQL and PL/SQL skills
    3. an interest in scripting
    4. I get easily distracted

    The script

    After about a day and a half, I have a working prototype script to call upon when I’m on the SQLcl command line. Here is what I came up with:

    NOTE: If you just came here to remix the code, I have this in the scripts folder in my GitHub blog repo as well. Please feel free to sample it and/or roast it/me 🔥. But if you keep scrolling, I'll go into more detail section-by-section.
    18-OCT-2023 UPDATE: I've slightly changed this code to include the Commit; command in the PL/SQL portion of the script. You'll see that reflected in line 44. Thanks René 🙌🏻!
    INPUT
    PROMPT Choose a new database username:
    ACCEPT NEWUSER CHAR PROMPT 'Enter new user name hurrr:'
    PROMPT Choose a temporary password for &&NEWUSER: 
    ACCEPT NEWPASS CHAR PROMPT 'Make it super secret:'
    
    /*
    I wish I could figure out a way to ONLY ask for username > check 
    that against existing database users > AND THEN allow a user to 
    continue with the 'choose password' step. I was only able to figure 
    out how to ask for the username and password AND THEN checks 
    against the database. I stole the code from this thread: 
    https://stackoverflow.com/questions/30710990/creating-an-oracle-user-if-it-doesnt-already-exist 
    
    Currently, its just extra steps for the user; kind of annoying. If you're 
    reading this and can figure out a way to get this working, let me know! 
    I'll make the change and attribute you in the comments :) 
    */
    
    Set Verify On 
    
    /*
    You can refer to section 6.3.10.11 for more details on this 
    SET VERIFY OFF command 
    https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-3ACD41F3-A5A2-48D5-8E81-C29F9C14C865
    */
    
    /*
    The difference between using single and double ampersands: 
    https://docs.oracle.com/en/database/oracle/oracle-database/23/sqpug/using-substitution-variables-sqlplus.html#GUID-C6BE6E41-821F-413E-B4B1-56AAE4A46298
    */
    
    Declare
    check_if_user_exists Integer;
    plsql_block VARCHAR2(500);
    NEWUSER VARCHAR2(20) := '&&NEWUSER';
    Begin
      Select count(*) Into check_if_user_exists From dba_users Where username=NEWUSER;
      If (check_if_user_exists = 0) Then
      Execute Immediate 'Create User &&NEWUSER Identified By &&NEWPASS';
      Execute Immediate 'Grant Connect To &&NEWUSER';
      Execute Immediate 'Grant Resource To &&NEWUSER';
      Execute Immediate 'Grant Unlimited Tablespace To &&NEWUSER';
      plsql_block := 'Begin ORDS_ADMIN.ENABLE_SCHEMA(p_schema => :1); Commit; End;';
      Execute Immediate plsql_block using NEWUSER;
      End If;
    End;
    /
    
    /*
    The p_schema parameter is mandatory, that's why I'm including it. 
    If you omit the other parameters, the procedure will use the default 
    parameter values. 
    
    Learn more about this procedure here: 
    https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/orddg/oracle-rest-data-services-administration-pl-sql-package-reference.html#GUID-459B8B6F-16EC-4FEC-9969-E8231668AD85
    
    I was able to get this entire thing to work through trial-and-error, 
    while also using this for reference: 
    https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems017.htm
    */
    
    PROMPT
    PROMPT
    PROMPT Congrats 🎉 the user: &&NEWUSER, with the password: &&NEWPASS is now a bona fide database user 🙌🏻! 
    PROMPT Not only that, &&NEWUSER can log into Database Actions and REST-Enable their database objects too 😍!
    PROMPT
    PROMPT
    PROMPT Click RETURN to return to the SQLcl prompt. And NEVER forget:
    PAUSE "You're good enough, you're smart enough, and doggone it, people like you!"
    PROPS: I owe much credit to this StackOverflow post and Jon Heller's and Mark Bobak's comments.

    Breaking it down

    Starting up podman

    I’ll first start up podman and cd in the correct directory. “Correct” insomuch that this is where my ordsuserl.sql script lives.

    starting-up-podman-machine-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools
    ordsuser.sql, the script I’ll be working with.

    Once my container status displays healthy, I’ll execute the ords serve command. This will launch ORDS in standalone mode (using the embedded Jetty server). I’ll use ORDS in a few minutes, so I should prepare it now.

    Prompting the user

    I’ll then log into my podman container using the following string:

    sql sys/password1234@//localhost:41465/ORCLPDB1 as sysdba
    sys-connection-string--chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    But why not the podman exec command?
    NOTE: I'm logging in as SYS (I've since been informed you shouldn't really be doing this as SYS; in fact, it looks like you shouldn't be doing this as SYSTEM. Tom has some good comments in this post here. 

    Why am I not using the podman exec command here?

    If you’ve seen my latest post on podman ports and networking, this command contradicts that entire article. There are actually two ways (maybe more, if I’m unaware) you can connect to your Oracle database in a podman container. The first way is to simultaneously hop on over to the container and sign in from inside that container. Your connection string would look like this:

    podman exec -it 21entdb sql sys/password1234@//localhost:1521/ORCLPDB1 as sysdba

    The second option is to sign in remotely like I’m doing in this current example:

    sql system/password1234@//localhost:41465/ORCLPDB1

    This is analogous to when you SSH into a machine remotely. I should have mentioned it in this recent YUM/Oracle Linux post. Still, when you create a Compute Instance, you can later SSH into that Instance and perform actions like you usually would in the Terminal or the Command Prompt. But instead of being on your own machine, you’re on a remote machine. Ports are very confusing (for me, at least), so please read that podman ports post.

    When you use the exec command, consider yourself on another computer on the Linux operating system. Once there, you must log in using the 1521 port because that is where the database’s TNS Listener (deep dive on Oracle database connection) is. However, when you are outside that container (i.e., that machine’s Linux OS), you need to use your local port (in this case, 41465) because it essentially acts as a proxy or a pass-through to the container’s 1521 port. Savvy 🏴‍☠️?

    DISCLAIMER: This my best-effort attempt at explaining this confusing concept. It is subject to change. But I really want people to take advantage of our tools in the Oracle Container Registry, so I hope this helps! 

    Hath connected to the database

    Once I’m in, I can call upon my script to quickly create a new user and REST-enable their schema (recall, I “cd” into the correct directory in an earlier step). The syntax:

    @ordsuser.sql
    MORE SQLcl: You can read the different SQL, SQLcl, and PL/SQL commands here.

    After pressing Return/Enter, a prompt will appear; this is what it looks like on the “front end”:

    reviewing-top-of-output-in-terminal-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    Notice the prompts in those first four lines.
    HINT: I would also spend some time here, learning about the PROMPT, ACCEPT, and INPUT commands. Yes, this comes from the SQL*Plus documentation, but this works as you'd expect in SQLcl.

    Meanwhile, here is the corresponding section in the script:

    initial-input-section-of-script-vs-code-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    The first section of the SQL script.

    Once I enter the password and press Enter/Return on my keyboard, the rest of the script is automatically executed using the provided username and password as substitution variables for the rest of the script! There is an in-depth explanation in the docs here, but you should grab the code and tinker with it to see how everything interacts and works together.

    NOTE: The Set Verify On command displays the changes made from the original PL/SQL procedure and the updated PL/SQL procedure (with the updated username and password). It isn't necessary, but I wanted to provide some feedback to a user. 

    PL/SQL procedure

    Assuming the user (i.e., the one you selected) doesn’t exist, the PL/SQL procedure should execute without issues. In the following image, you can see what is happening in real time:

    1. A new user is created with the assigned username and password
    2.  That user is granted the Connect and Resource roles
    3.  The schema is then REST-enabled using the ORDS_ADMIN.ENABLE_SCHEMA PL/SQL procedure
    reviewing-the-verify-on-plsql-procedure-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman

    Why NEWUSER in the Declare block?

    Yeah, good question. At a minimum, you need to include the p_schema in the ORDS_ADMIN.ENABLE_SCHEMA procedure. Read up on that hereBut, I wanted the username to remain in lowercase since that will later be used for my schema’s URIs (e.g., http://localhost:8080/ords/ordstest/emp/).

    So I quickly taught myself/attempted a crash course on define variables and bind arguments; most of what I learned came from this EXECUTE IMMEDIATE documentation. And that’s why you see this in lines 25 and 34 of the code:

    NEWUSER VARCHAR2(20) := '&&NEWUSER'; 
    
    /* as well as */
    
    ORDS_ADMIN.ENABLE_SCHEMA(p.schema => :1);
    plsql-section-of-script-vs-code-chris-hoina-senior-product-manager-ords-oracle-rest-apis-database-tools-podman
    You can see the interaction between lines 25 and 34.

    And on the front end, courtesy of the Set Verify On command, you’ll see that updated block of code:

    The username and password fields are updated.

    Feedback

    I’ll then provide myself (or the user) with helpful feedback (along with words of encouragement). Once satisfied, I can click the Return/Enter key to exit the script and sign in to Database Actions as that new user.

    The corresponding section in the script looks like this:

    I’m cheating by adding PROMPT to give line breaks.

    Sign-in to Database Actions

    Now, I can navigate to the Database Actions sign-in page at localhost:8080/ords/sql-developer. If I wanted to, I could also navigate to the newly introduced landing page at http://localhost:8080/ords/_/landing (obviously, depending on your deployment, this address will differ).

    SQL Worksheet then the proof

    I’ll head to a SQL Worksheet, select All Objects in the Navigator tab, and a clean schema ready to take on the world!

    Summary

    And this marks the end of today’s lesson. So what did we learn?

    1. You can execute SQL scripts directly from the SQLcl command line.
    2. My script is cool, but I wish I could verify if a user exists sooner (I end up forcing the user to go through that password step).
    3. The script has no exception handling (I’m giving myself a pass on this since I’m just now getting into PL/SQL).
    4. Spend some time with the documentation on one screen and your script on another. After some time, you can actually understand how everything interacts.

    One final thought. My process required a lot of trial and error, but seeing how everything flows and works is entertaining.

    Please sample/remix my code; it’s located in my GitHub blog repo. Make it better, and let me know what you come up with! And be sure to check out ORDS and SQLcl 😍!

    Follow

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

  • ORDS install considerations: choosing the correct host, port, service name, and pluggable database when the database is in a podman container

    The other day, I wrote about how I had to start from scratch on my podman containers 😢. I’m now at the step where I need to reinstall ORDS in these two new database containers (21c and 23c). And since I’m doing this install yet again, I figured I would point out some things I’ve learned while doing this with podman containers. This post isn’t meant to be all-inclusive; I’m simply highlighting the areas that gave me the most trouble.

    Lettuce begin

    My assumptions are that you’ve downloaded the ORDS zip file or from a Yum repository (how-to article here). You’ve also set the ORDS configuration folder path and the ORDS product folder path (both are necessary steps for ORDS pre-installation). You can read up on that step here.

    The ORDS Interactive Installer

    Here, I’m installing ORDS with the Interactive Installer.

    ords-interactive-installer-screenshot-chris-hoina-senior-product-manager-oracle-rest-data-services-database-tools
    Do this with the ords install command

    The fine print

    NOTE: For a vanilla installation, most of the default prompts are correct. But for working with a podman container, I do not believe all the default settings will work (at least, this has been my experience). 

    The ORDS Interactive Installer will prompt you with the default settings, where appropriate. You’ll notice the Choose [value]: convention. These settings are okay to use in many steps, but if you mindlessly follow them in specific steps, you might end up with the incorrect ORDS installation for your particular use case.

    Select the type of installation

    For instance, in the “Enter a number to select the type of installation” step, I’m prompted with the [1] option. For me, that is incorrect; I need to choose [2].

    type-of-installation-ords-interactive-installer-chris-hoina-senior-product-manager-oracle-rest-data-services-database-tools
    Option [2] I choose you!

    Database pool to update or create

    Things can get tricky here, too. In this step, I WILL choose option [1], but in the next step, I WILL NOT select the default settings (read on about host names, ports, and service names).

    basic-connection-ords-interactive-installer-chris-hoina-senior-product-manager-oracle-rest-data-services-database-tools
    This default string is simply an example; with podman you may not be using 1521 as the port.

    Selecting the database connection type

    And here’s why I won’t use the default settings. It’s because I have mapped the ports to/from my podman containers like this:

    podman-container-configurations-chris-hoina-senior-product-manager-oracle-rest-data-services-database-tools
    If you don’t map a port, podman will pick one for you!

    My 21entdb container is set up such that my computer sends and receives podman container traffic on port 41465. Meanwhile, my podman container is set up so that it will send and receive data on port 1521 (which is the default port for Oracle’s TNS Listener).

    Another way of looking at this is to imagine port 41465 is sort of spoofing port 1521. Ehh..maybe it’s better to think of it like a pass-through, a proxy, a go-between if you will…but more on this in a second.

    Demystifying the connection string

    Here, I’ll test both ports, the Container (ORCLCDB) and Pluggable (ORCLPDB1) databases, with various connection strings.

    QUESTION: How do I even know my options are ORCLCDB or ORCLPDB1? Well, I learned about them in the container registry documentation.

    Using port 1521

    First, let’s see what happens when I try to log into my database with SQLcl, using 1521 as the port:

    Nothing! Initially, for me, this made no sense! And that’s because, in my mind 1521 is the port that you would expect to connect with! This whole network business was confounding! That was until I realized that you have to use your computer’s port to connect to the podman container (which is listening on port 1521).

    Using the port podman assigned to you

    Ah-ha! Now, if you make that slight change to the ports, you can connect to your Container (ORCLCDB) and Pluggable (ORCLPDB1) databases.

    Does this help? Do you have a better understanding of why your port might not be 1521?

    Host, port, and service names

    You can probably keep the localhost default selection. When it comes to the listen port selection, I must choose 41465 and not 1521. And for the database service name, you could choose ORCLCDB (i.e. Oracle Container Database), but we recommend installing ORDS into a Pluggable Database (read up on this in our ORDS Best Practices). Here you see me do just that; ORCLPDB1 is the ORDS default, but I wanted to highlight why this is the default.

    host-port-service-name-ords-interactive-installer-chris-hoina-senior-product-manager-oracle-rest-data-services-database-tools

    Provide database user

    In this step, I’m supplying the username and password of a user with the necessary privileges to log into the database to complete the ORDS installation. In this case, unsurprisingly, it is the SYS user (the default). We also have an ORDS Installer Privileges script you can execute if you’d rather grant another user privileges to install, upgrade, repair, and uninstall ORDS. You can find more details on that script here.

    A great example showing how the JDBC driver is using the host, port, and service name values you provided.

    Enabling features

    This isn’t the final ORDS install step; this is just the last step I wanted to point out. The default here is also [1]. And I think you should keep it like that, here’s why. When you select [1], you’ll also give users access to Database Actions – the Graphical User Interface (which shares much in common with the SQL Developer desktop client). Once you start using Database Actions, it’s hard to stop.

    enable-additional-features-ords-interactive-installer-chris-hoina-senior-product-manager-oracle-rest-data-services-database-tools
    Don’t ask any questions; select option [1].
    NOTE: You'll also be enabling the REST-enabled SQL service (as well as the Database API). I recently wrote about the ORDS REST-Enabled SQL Service; it's very cool, and you should check out that article here.  

    The end

    Aaanndd, that’s my time. They’re flashing the lights, so I have to get off the stage 🙁. Hopefully, this note will be helpful when you create some containers from images on the Oracle Container Registry. And if you think you might like to tinker with ORDS, bookmark this post so you can refer to it later!

    Follow

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