Tag: learning

  • A simple ORDS GET request using the Go language

    A simple ORDS GET request using the Go language

    Venkata this one’s for you 😀

    It took me the rest of the afternoon, but here is a pretty simple GET request example, using a user-defined ORDS API. This API is derived from one of our LiveLabs, a Python + JavaScript application. But this particular scenario pretty well exemplifies what we discussed in our presentation yesterday—that the RESTful framework makes your backend code much more language-agnostic. With a few tweaks, I was able to “port” this over to Go.

    ORDS example API

    The ORDS API I’m working with looks like this on the back end (this is our REST Workshop; where you build ORDS APIs):

    More formally, it looks like this:

    SELECT DISTINCT
        GENRE
    FROM
        MOVIE,
        JSON_TABLE ( GENRES, '$[*]'
                COLUMNS (
                    GENRE PATH '$'
                )
            )
    ORDER BY
        1 ASC

    The MOVIE table I’m calling upon consists of various data types, but the target GENRE In this case, the column is a JSON object that actually contains an array. So, all I’m doing is returning the unique genres (i.e., removing duplicates) from a table of thousands of movies. What is nice about this approach is that all my SQL and PL/SQL stay in the database. All my application’s code needs now is an endpoint.

    If I don’t want to keep pinging that endpoint, I can always just use a JSON document as a proxy for the endpoint. And if I just point my browser to that URI (i.e. ORDS API), here is what the results look like (I had to take two screenshots to capture everything):

    When you are looking at these results, it’s best to focus on the “root” key:value pairs of the ORDS response. For a fairly standard ORDS GET request, the response body will consist of:

    • items
    • hasMore
    • limit
    • offset
    • count, and
    • links

    When I collapse my example, I am left with the following:

    {
        "items": [
        ],
        "hasMore": false,
        "limit": 25,
        "offset": 0,
        "count": 24,
        "links": [
        ]
    }
    Note: The items will be an array, as will the links.

    A caveat to this is when/if you choose to Auto-REST enable an object, the result will look mostly the same. However, ORDS adds a self-referential link to each result. But those links are an array, too.

    Regardless, it shouldn’t affect how you approach this in Go. The struct of those types are the same: Items []Items json:"items" and Links []Links json:"links", respectively. Compare this auto-REST-enabled result to the one above:

    Worth noting: The image below is using a different endpoint. Its basically doing a "select * from movies;" on the MOVIES table. Hence, why it looks a little different. But you should still be able to see what I am talking about. If you want to learn more about ORDS Auto-REST, then check this out. 
    If you squint, you can see how each result has its own “rel” link. That is how Auto-REST would look, this is expected.

    Go time

    Coming from Python and PL/SQL, along with some JavaScript, I found Go to be extremely enjoyable. I get the gist of how you define the types and structures of “things.” I haven’t even bothered to look at the documentation yet; I didn’t even need to. It was easy to follow along using the two tutorials I found (here and here). After a few minutes of debugging (which is quite intuitive in Go, actually), I had my code working.

    Actually, once I figured out the syntax and Go idiosyncracies, creating this simple example wasn’t much of an effort.

    Here is the Go code that I came up with:

    package main
    
    import (
    	"encoding/json"
    	"fmt"
    	"io/ioutil"
    	"log"
    	"net/http"
    	"os"
    )
    
    type Response struct {
    	Items   []Items `json:"items"`
    	Hasmore bool    `json:"hasMore"`
    	Limit   int     `json:"limit"`
    	Offset  int     `json:"offset"`
    	Count   int     `json:"count"`
    	Links   []Links `json:"links"`
    }
    
    type Items struct {
    	Genre string `json:"genre"`
    }
    
    type Links struct {
    	Rel  string `json:"rel"`
    	Href string `json:"href"`
    }
    
    func main() {
    
    	response, err := http.Get("http://localhost:8081/ords/ordsdemo/mymovies/movie-genre")
    
    	if err != nil {
    		fmt.Print(err.Error())
    		os.Exit(1)
    	}
    
    	responseData, err := ioutil.ReadAll(response.Body)
    	if err != nil {
    		log.Fatal(err)
    	}
    
    	var responseObject Response
    	json.Unmarshal(responseData, &responseObject)
    
    	for i := 0; i < len(responseObject.Items); i++ {
    		fmt.Println(responseObject.Items[i].Genre)
    	}
    
    	fmt.Println(responseObject.Hasmore)
    	fmt.Println(responseObject.Limit)
    	fmt.Println(responseObject.Offset)
    	fmt.Println(responseObject.Count)
    
    	for i := 0; i < len(responseObject.Links); i++ {
    		fmt.Println(responseObject.Links[i].Rel + responseObject.Links[i].Href)
    	}
    
    }

    If you know Go, then I’m not teaching you anything that you don’t already know. You probably know the libraries I’m using. However, I think it is worth pointing out a few ORDS-specific things.

    When it comes to the items and the links they are always going to be an array. So you’ll need to account for that when you are defining the structures (aka struct) for your user-defined types (let me know if this terminology is correct, it just seems so intuitive, so I’m going with it).

    Here are some other things to consider:

    • hasMore is a Boolean; or bool in Go. It’s also going to be either true or false.
      • True means there are more results (so you can page forward if you want).
      • False means there are no more results.
    • limit just means how many results you should expect. In my case, 25 results (that is our default, but you can adjust this when you build your APIs).
      • Another way to look at this is that my pagination is set to 25 results per page.
    • offset and limit are kind of interdependent. If you are on the first page then your offset will be = 0. But if you wanted to view your second “page” or set of results, then you’d set your offset = 25.
      • And because your limit = 25 then you’d see results 26-50.
    • The count is just the total results per page, or 25 in this case (hypothetically, if you were to make it to the end of your results set, it would probably not be exactly 25, something to keep in mind).

    Here is a complete JSON object of my first results (if you want to play around with it until you get ORDS up and running):

    {
        "items": [
            {
                "genre": "Action"
            },
            {
                "genre": "Adventure"
            },
            {
                "genre": "Animation"
            },
            {
                "genre": "Biography"
            },
            {
                "genre": "Comedy"
            },
            {
                "genre": "Crime"
            },
            {
                "genre": "Documentary"
            },
            {
                "genre": "Drama"
            },
            {
                "genre": "Family"
            },
            {
                "genre": "Fantasy"
            },
            {
                "genre": "Film-Noir"
            },
            {
                "genre": "History"
            },
            {
                "genre": "Horror"
            },
            {
                "genre": "Lifestyle"
            },
            {
                "genre": "Musical"
            },
            {
                "genre": "Mystery"
            },
            {
                "genre": "Reality-TV"
            },
            {
                "genre": "Romance"
            },
            {
                "genre": "Sci-Fi"
            },
            {
                "genre": "Sport"
            },
            {
                "genre": "Thriller"
            },
            {
                "genre": "Unknown"
            },
            {
                "genre": "War"
            },
            {
                "genre": "Western"
            }
        ],
        "hasMore": false,
        "limit": 25,
        "offset": 0,
        "count": 24,
        "links": [
            {
                "rel": "self",
                "href": "http://localhost:8081/ords/ordsdemo/mymovies/movie-genre"
            },
            {
                "rel": "describedby",
                "href": "http://localhost:8081/ords/ordsdemo/metadata-catalog/mymovies/item"
            },
            {
                "rel": "first",
                "href": "http://localhost:8081/ords/ordsdemo/mymovies/movie-genre"
            }
        ]
    }

    All the code you see here is also in my blog post code repo. There are other examples there, too!

    Also, huge thanks for asking about Go. It gave me an excuse to start learning it. I think I might be a Go fanboi now 🫣!

    And for all others reading:

    Follow

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

  • 503 Service Unavailable: ORDS unable to make a connection…all connections in the Univeral connection Pool are in use – [30,30,42….]

    Have you ever seen this?

    Have you ever seen a message like this while working with ORDS? The one where you receive a 503 Service Unavailable response code and message?

    This message has something to do with ORDS being unable to make a connection to the database.

    But if you keep reading, you’ll see even more information- something related to the Universal Connection Pool- followed by an array of numbers. Did you know that the “stuff” in that array is actually helpful information?

    Well…Kris knew.

    What does it all mean?

    But for the rest of us plebes, what does “max = 30 and current in use = 30” refer to? Well, let’s figure it out!

    Take a closer look at the above image. You’ll see an array consisting of the following: [30, 30, 42, 12, 0, 30, 1, 0, 29]. Which, according to the Universal Connection Pool documentation, translates to:

    Now what?

    So, what do we do with this information? Well, it depends. In this case, it looks like available connections were exhausted. And what could cause that? It’s a mystery, but it doesn’t have to be 😉. You need to do some exploring (It’s not just going to fix itself.)!

    Luckily, there are many ways to “peek under the covers” to learn what is happening in and around your database. Some things that come to mind:

    These are the ones that I’m most familiar. But if you are reading this, what works for you? Drop a comment on what tools you use, and I’ll update this post for others to see!

    When you’re ready…

    Once you’ve determined what is happening, you can return to ORDS and tweak your JDBC settings—if you need to!

    That’s all folks

    This post is outside the norm of what I usually post. I’m not doing a walkthrough or tutorial. I’m just…throwing some info out there. I mainly wanted to write this quick PSA before I forgot.

    I’ve seen this error come up frequently in questions. I think if more people knew what those numbers meant, it might help with self-diagnosis. I hope I’m not wrong. Catch you later 😎!

    Follow

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

  • 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!