In an internal Slack thread today, a user was trying to diagnose browser latency while attempting to connect to the ORDS landing page.
Peter suggested what I thought was a pretty neat heuristic for checking connections to ORDS and your database, as well as latency.
Methodology
Let’s say your symptoms are either slow loading or intermittent loss of service when attempting to reach an ORDS landing page. Your immediate thought might be to refresh your browser or bounce the ORDS server (if running in Standalone mode). But this isn’t practical in a production environment, nor is it practical if you have ORDS deployed on a Weblogic Server or Apache Tomcat (you can deploy using any of those three servers).
Quick Heuristic
Use cURL to test two different endpoints, compare their response times to each other, and compare to the response time you’ve observed historically1.
URL one
This first URL points to where your ORDS instance “lives.”
https://[myhost]/ords
“Lives,” what does that mean? If you’re deploying to Weblogic Server ORDS would “live” in the $WEBLOGIC_HOME/application directory. In Apache Tomcat it is the $CATALINA/webapps directory, and on Standalone (using the ORDS embedded Jetty server) that might be $HOME/[your ords product folder].
So if you execute a cURL command to that URL, you’re issuing a request to your application server. With that single command, you can determine:
if it’s even up
what the response is, and
how fast that response is
Honestly, this sounds silly and obvious at first. But it costs you nothing to just rule out the application server as a culprit. The command2 I tested (with the response included):
choina@MacBook-Pro-2~%curl-v-s-w"\nTotal time: %{time_total}\n"http://localhost:8080/ords* Host localhost:8080 was resolved.* IPv6: ::1* IPv4: 127.0.0.1* Trying [::1]:8080...* Connected to localhost (::1) port 8080> GET /ords HTTP/1.1> Host: localhost:8080> User-Agent: curl/8.7.1> Accept: */*> * Request completely sent off< HTTP/1.1 301 Moved Permanently< Location: /ords/< Content-Length: 0< * Connection #0 to host localhost left intactTotaltime:0.001882
NOTE: Make sure you issue this cURL command WITHOUT a trailing slash after /ords. So like this: http://localhost:8080/ords, and NOT like this: http://localhost:8080/ords/. If you include the trailing slash, you'll force a redirect to the http://localhost:8080/ords/_/landing page (this just adds unnecessary time to your result).
URL two
The second cURL command you can issue is to the Metadata catalog for your/a target schema. In this example, I’m working with my ordsdemo user. He’s been REST-enabled, and I’ve already set up some Resource Modules (aka ORDS APIs). So I know there is “stuff” at that endpoint.
Here is the cURL command I used (with the response included):
choina@MacBook-Pro-2~%curl-v-s-w"\n\nTotal time: %{time_total}\n"http://localhost:8080/ords/ordsdemo/metadata-catalog/* Host localhost:8080 was resolved.* IPv6: ::1* IPv4: 127.0.0.1* Trying [::1]:8080...* Connected to localhost (::1) port 8080> GET /ords/ordsdemo/metadata-catalog/ HTTP/1.1> Host: localhost:8080> User-Agent: curl/8.7.1> Accept: */*> * Request completely sent off< HTTP/1.1 200 OK< Content-Type: application/json< X-ORDS_DEBUG: true< X-Frame-Options: SAMEORIGIN< Transfer-Encoding: chunked< * Connection #0 to host localhost left intact{"items":[{"name":"MOVIE","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/movie/"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/movie/","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/movie/","mediaType":"application/openapi+json"}]},{"name":"moviestream","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/mymovies/movie-all"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/mymovies/movie-all","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/mymovies/","mediaType":"application/openapi+json"}]},{"name":"moviestream","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/mymovies/movie-genre"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/mymovies/movie-genre","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/mymovies/","mediaType":"application/openapi+json"}]},{"name":"test_endpoints","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/no_objects/hello_auth"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/no_objects/hello_auth","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/no_objects/","mediaType":"application/openapi+json"}]},{"name":"test_endpoints","links":[{"rel":"describes","href":"http://localhost:8080/ords/ordsdemo/no_objects/hello_client_cred"},{"rel":"canonical","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/no_objects/hello_client_cred","mediaType":"application/json"},{"rel":"alternate","href":"http://localhost:8080/ords/ordsdemo/open-api-catalog/no_objects/","mediaType":"application/openapi+json"}]}],"hasMore":false,"limit":25,"offset":0,"count":5,"links":[{"rel":"self","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/"},{"rel":"first","href":"http://localhost:8080/ords/ordsdemo/metadata-catalog/"}]}Total time: 0.030173
In the above example, we are hitting the database server (because we are grabbing the metadata catalog for the target schema). Consequently, the Total time for the round trip is slightly higher. Now, I can compare the two times against each other and against what I’ve seen historically. Are they reasonable? Do they pass the “sniff test”? Or do the results merit a deeper investigation?
Fin
I cannot stress this enough; this is just a simple heuristic to get you started. It takes 20 seconds to execute these commands. You’re really just “slicing the pie” at this stage. I honestly don’t think that simile applies here (unless you’ve ever cleared a room before), but you get the idea – thin slicing. Inching your way ever closer to the solution. This is just a part of the route-cause analysis.
That’s all for now. Keep this in your back pocket. If you have your own heuristics or troubleshooting tips, leave them in a comment below. I’d love to hear how you diagnose issues related to networking, connectivity, latency, etc.
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
I was hesitant to include this last part, “you’ve observed historically,” because I don’t give a metric. Honestly, it depends on your workload. But if you are doing any sort of performance testing you probably have at least a good sense of what response times should be like for your application (I’m talking directly to developers here). Even if you don’t know these expected response times, you can still compare the individual results against each other: the first URL vs the second URL, in this example. ↩︎
What do the optional -v-w-s variables mean? Good question -v is shorthand for “Verbose;” which means print out as much info as is available. The -s is shorthand for “Silent;” in other words, don’t show any progress bars. And the -w means “Write Out,” in this case, “explicitly write out the Total time for me please.” ↩︎
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:
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).
👈🏼 click these to reveal the contents
No! Not this one, dummy. This one is just an example…duh 🤕!
response: json() method
const myList = document.querySelector("ul");
const myRequest = new Request("products.json");
fetch(myRequest)
.then((response) => response.json())
.then((data) => {
for (const product of data.products) {
const listItem = document.createElement("li");
listItem.appendChild(document.createElement("strong")).textContent =
product.Name;
listItem.append(` can be found in ${product.Location}. Cost: `);
listItem.appendChild(document.createElement("strong")).textContent =
`£${product.Price}`;
myList.appendChild(listItem);
}
})
.catch(console.error);
Check out my remixed JavaScript code, DDL and ORDS module definitions (for this example) on my GitHub blog repo. I'll also include select items below.
Here are a few things to point out:
In line 16 of myindex.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).
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:
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).
const ordsApi = "http://localhost:8080/ords/ordstest/api/example/api/";
// This next one is just an example using query parameters. I just chose a random employee number:
// const filteredOrdsApi = 'http://localhost:8080/ords/ordstest/api/example/api/?q={"empno":"7876"}';
const myList = document.querySelector("ul");
fetch(ordsApi).then((response) => {
if (!response.ok) {
throw new Error(`HTTP error, status = ${response.status}`);
}
return response.json();
}).then((data) => {
for (const item of data.items) {``
const listItem = document.createElement("p");
const empnoElement = document.createElement("strong");
empnoElement.textContent = item.empno;
const enameElement = document.createElement("strong");
enameElement.textContent = `${item.ename}`;
const dnameElement = document.createElement("strong");
dnameElement.textContent = `${item.dname}`;
const jobElement = document.createElement("strong");
jobElement.textContent = `${item.job}`;
listItem.append(`Employee number `, empnoElement, ` was hired on ${item.hiredate}.`, ` Their last name is `, enameElement, ` and they work as a `, jobElement, ` in the `, dnameElement, ` department.`
);
myList.appendChild(listItem);
}
})
.catch((error) => {
const p = document.createElement("p");
p.appendChild(document.createTextNode(`Error: ${error.message}`));
document.body.insertBefore(p, myList);
});
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.
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
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 moreobjects {}. And each of those objects has its ownkey: 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.
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:
const ordsAPI on Line 1 (as opposed to referencing a JSON file).
Naming conventions in lines 14-27.
listItem.append(); on line 29 is heavily remixed (I did this so I could create individual lines for each entry).
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:
You can see those key: value pairs in a different presentation.
These images help connect what is coming through in that GET request and what you see in the JavaScript code.
The items key with its value (an array).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.
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.
Live Server starts up, sees the index.html file, and “serves” it up.
In that HTML file is a reference to script.js; the JavaScript is run.
The JavaScript composes a list and then appends all the data you see here (on screen):
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!
I promise this post will connect back to an overarching theme. But for now, I want to show how you can take a SQL query and use that in combination with the ORDS REST-Enabled SQL Service to request data from a database table.
The SQL query
Here is the SQL query I’m using:
select * from (
select noc, sport
from olympic_medal_winners
)
pivot (min('X') for sport in (
'Archery' as arc, 'Athletics' as ath, 'Hockey' as hoc,
'Judo' as jud, 'Sailing' as sai, 'Wrestling' as wre
)
)
order by noc
fetch first 7 rows only
The SQL Script
Please feel free to cheat like me and steal this same script from the Live SQL site (direct link here). And if you can’t be bothered to do that, the script in its entirety, can be found at the bottom of the post.
PAUSE: Shout out to Chris Saxon for conceiving this. And putting in the real work. I'm both too lazy and too dumb to come up with this on my own.
The demo
Let’s assume you’ve created the table and inserted all the same data. Now, you can take a SQL query (use the same one as me, or don’t, I don’t care 😑) and run it in an SQL Worksheet (like I did here):
Coooool dude...you did a thing I already know how to do!
That’s what you’ll end up with. It’s actually a pretty neat printout; I didn’t even know you could do this! But, I want to take that SQL query and demonstrate how you can do this with the ORDS REST-Enabled SQL service.
Disclaimer
I’m performing this demo locally. I have a Podman container running with an Oracle database therein (one I grabbed from our Oracle Container Registry). I’ve also installed ORDS in this database and used my ORDSTEST user (the same setup as in my original Podman/ORDS how-to post).
About the REST-Enabled SQL Service
How do I set this service up? When you first install ORDS, if you enable Database Actions, you are also enabling this REST-Enabled SQL Service. You’ll see it in this step in the ORDS Interactive Installer:
Enter a number to select additional feature(s) to enable:
[1] Database Actions (Enables all features)
[2] REST Enabled SQL and Database API
[3] REST Enabled SQL
[4] Database API
[5] None
Choose [1]:
See it? If you select [1], then you are also enabling features [2], [3], and [4].
PRO TIP: Basically, if you can sign into Database Actions, then you're good. You're all set.
Long story short. You are taking that whacky SQL from the above example (something that would take me a week to come up with on my own) and passing it as a payload in a POST request to your REST-Enabled SQL Service 🤖 endpoint.
Since I am doing this locally, my REST-Enabled SQL Service endpoint looks like this:
You should see something similar; your REST-enabled schema alias will differ however
Next, with my Terminal open (and ORDS running, duh!) I’ll issue the following command:
Ah-ha! You probably noticed that the --data-binaryoption references a separate sportCountryMatrix.sql file. That’s because our docs recommend using an SQL file for multi-line SQL statements (like my example). I bet you could pass in this multi-line statement via the command line, but that seems unnecessarily challenging. Plus, I KNOW this works.
A quick review of the directory setup
I want to quickly review how I created this file, mostly remaining in Terminal. I first created a new ordsSqlService directory on my desktop. Then, I made an empty sportCountryMatrix.sql file.
Using VIM, I opened that file, pasted my choice SQL statement, saved it, and exited. I wanted to mention this because when I executed that cURL command, it worked because Iwas in the same directory as the SQL file!
Here are some screenshots of me going through those steps. You’ll see me creating the file but then also using the cat command so that I can double-check the contents of the .sql file.
And now, back to the cURL command. After issuing the command, here is the response to the POST request:
I know the response isn’t the most readable, but I can figure that out another time. (I have something else planned as a follow-up to this post). But it’s all there, trust me (I’m a doctor)!
Also, this blog post was about 50% me messing around and 50% reminding YOU that ORDS is capable of this (right out of the box, with the correct switches turned on). So, hopefully, you get the gist 😄.
Takeaways
Let me close this out with some final thoughts…
If you can sign into Database Actions, then you can take advantage of the REST-Enabled SQL Service
I haven’t explored how to pretty print the JSON response so it is more readable (and yes, I tried piping in | json_pp; it didn’t work)
Update on #2…I actually did figure this out. Read about that here.
You have to use your database username and password; this isn’t ideal for two reasons:
Security
Resource consumption (Basic Authentication can become costly, quick)
You can take pretty much any SQL query and turn it into a “Resource”
For instance, if you are an analyst, you can take that SQL query, save it as a file, and pass it in your cURL command to get precisely what you want.
Follow
And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!
-- REM Script: Pivot and unpivot examples using Olympic data
-- REM Examples of pivoting and unpivoting data. Uses a subset of -- the results from the Rio Olympics as a data source.
-- For further explanation of the scripts, read the following blog -- post:
-- https://blogs.oracle.com/sql/entry/how_to_convert_rows_to
create table olympic_medal_winners (
olympic_year int,
sport varchar2( 30 ),
gender varchar2( 1 ),
event varchar2( 128 ),
medal varchar2( 10 ),
noc varchar2( 3 ),
athlete varchar2( 128 )
);
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Individual','Gold','KOR','KU Bonchan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Individual','Silver','FRA','VALLADONT Jean-Charles');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Individual','Bronze','USA','ELLISON Brady');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Team','Gold','KOR','Republic of Korea');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Team','Bronze','AUS','Australia');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Archery','M','Men''s Team','Silver','USA','United States');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Floor Exercise','Gold','GBR','WHITLOCK Max');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Floor Exercise','Bronze','BRA','MARIANO Arthur');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Floor Exercise','Silver','BRA','HYPOLITO Diego');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Horizontal Bar','Gold','GER','HAMBUECHEN Fabian');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Horizontal Bar','Bronze','GBR','WILSON Nile');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Artistic Gymnastics','M','Men''s Horizontal Bar','Silver','USA','LEYVA Danell');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 10,000m','Gold','GBR','FARAH Mohamed');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 10,000m','Bronze','ETH','TOLA Tamirat');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 10,000m','Silver','KEN','TANUI Paul Kipngetich');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 100m','Gold','JAM','BOLT Usain');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 100m','Silver','USA','GATLIN Justin');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Athletics','M','Men''s 100m','Bronze','CAN','DE GRASSE Andre');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Gold','CHN','Zhang');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Bronze','GBR','Langridge');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Bronze','GBR','Ellis');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Silver','MAS','Tan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Silver','MAS','Goh');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Badminton','M','Men''s Doubles','Gold','CHN','Fu');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Gold','BRA','Cerutti');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Gold','BRA','Oscar Schmidt');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Silver','ITA','Nicolai');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Silver','ITA','Lupo');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Bronze','NED','Meeuwsen');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Beach Volleyball','M','Men','Bronze','NED','Brouwer');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Gold','CUB','RAMIREZ Robeisy');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Bronze','UZB','AKHMADALIEV Murodjon');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Bronze','RUS','NIKITIN Vladimir');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Bantam (56kg)','Silver','USA','STEVENSON Shakur');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Fly (52kg)','Gold','UZB','ZOIROV Shakhobidin');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Boxing','M','Men''s Fly (52kg)','Bronze','CHN','HU Jianguan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Gold','SVK','PETER Skantar');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Bronze','FRA','GAUTHIER Klauss');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Bronze','FRA','MATTHIEU Peche');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Silver','GBR','RICHARD Hounslow');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Silver','GBR','DAVID Florence');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Slalom','M','Canoe Double (C2) Men','Gold','SVK','LADISLAV Skantar');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Gold','GER','Brendel');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Bronze','UKR','Mishchuk');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Bronze','UKR','Ianchuk');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Silver','BRA','Queiroz dos Santos');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Silver','BRA','de Souza Silva');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Canoe Sprint','M','Men''s Canoe Double 1000m','Gold','GER','Vandrey');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Individual Time Trial','Gold','SUI','CANCELLARA Fabian');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Individual Time Trial','Bronze','GBR','FROOME Christopher');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Individual Time Trial','Silver','NED','DUMOULIN Tom');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Road Race','Gold','BEL','VAN AVERMAET Greg');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Road Race','Silver','DEN','FUGLSANG Jakob');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Road','M','Men''s Road Race','Bronze','POL','MAJKA Rafal');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Keirin','Gold','GBR','KENNY Jason');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Keirin','Bronze','MAS','AWANG Azizulhasni');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Keirin','Silver','NED','BUCHLI Matthijs');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Omnium','Gold','ITA','VIVIANI Elia');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Omnium','Bronze','DEN','HANSEN Lasse Norman');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Cycling Track','M','Men''s Omnium','Silver','GBR','CAVENDISH Mark');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 10m Platform','Gold','CHN','CHEN Aisen');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 10m Platform','Bronze','USA','BOUDIA David');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 10m Platform','Silver','MEX','SANCHEZ German');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 3m Springboard','Gold','CHN','CAO Yuan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 3m Springboard','Silver','GBR','LAUGHER Jack');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Diving','M','Men''s 3m Springboard','Bronze','GER','HAUSDING Patrick');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Individual','Gold','GBR','DUJARDIN Charlotte');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Individual','Bronze','GER','BRORING-SPREHE Kristina');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Individual','Silver','GER','WERTH Isabell');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Team','Gold','GER','Germany');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Team','Bronze','USA','United States');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Equestrian','X','Dressage Team','Silver','GBR','Great Britain');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Individual','Gold','ITA','GAROZZO Daniele');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Individual','Silver','USA','MASSIALAS Alexander');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Individual','Bronze','RUS','SAFIN Timur');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Team','Gold','RUS','Russian Federation');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Team','Bronze','USA','United States');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Fencing','M','Men''s Foil Team','Silver','FRA','France');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','M','Men','Gold','DEN','Denmark');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','M','Men','Silver','FRA','France');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','M','Men','Bronze','GER','Germany');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','W','Women','Gold','RUS','Russian Federation');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','W','Women','Silver','FRA','France');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Handball','W','Women','Bronze','NOR','Norway');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','M','Men','Gold','ARG','Argentina');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','M','Men','Silver','BEL','Belgium');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','M','Men','Bronze','GER','Germany');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','W','Women','Gold','GBR','Great Britain');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','W','Women','Silver','NED','Netherlands');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Hockey','W','Women','Bronze','GER','Germany');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Gold','FRA','RINER Teddy');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Bronze','BRA','SILVA Rafael');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Bronze','ISR','SASSON Or');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men +100 kg','Silver','JPN','HARASAWA Hisayoshi');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men -100 kg','Gold','CZE','KRPALEK Lukas');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Judo','M','Men -100 kg','Bronze','FRA','MARET Cyrille');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','M','Men''s Individual','Gold','RUS','LESUN Alexander');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','M','Men''s Individual','Silver','UKR','TYMOSHCHENKO Pavlo');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','M','Men''s Individual','Bronze','MEX','HERNANDEZ USCANGA Ismael Marcelo');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','W','Women''s Individual','Gold','AUS','ESPOSITO Chloe');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','W','Women''s Individual','Silver','FRA','CLOUVEL Elodie');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Modern Pentathlon','W','Women''s Individual','Bronze','POL','NOWACKA Oktawia');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Group All-Around','Gold','RUS','Russian Federation');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Group All-Around','Bronze','BUL','Bulgaria');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Group All-Around','Silver','ESP','Spain');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Individual All-Around','Gold','RUS','MAMUN Margarita');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Individual All-Around','Silver','RUS','KUDRYAVTSEVA Yana');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rhythmic Gymnastics','W','Individual All-Around','Bronze','UKR','RIZATDINOVA Ganna');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Gold','FRA','Azou');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Bronze','NOR','Brun');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Bronze','NOR','Strandli');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Silver','IRL','O''Donovan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Silver','IRL','O''Donovan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Rowing','M','Lightweight Men''s Double Sculls','Gold','FRA','Houin');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Gold','CRO','Fantela');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Bronze','GRE','Kagialis');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Bronze','GRE','Mantis');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Silver','AUS','Ryan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Silver','AUS','Belcher');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Sailing','M','470 Men','Gold','CRO','Marenic');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Backstroke','Gold','USA','MURPHY Ryan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Backstroke','Bronze','USA','PLUMMER David');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Backstroke','Silver','CHN','XU Jiayu');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Breaststroke','Gold','GBR','PEATY Adam');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Breaststroke','Bronze','USA','MILLER Cody');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Swimming','M','Men''s 100m Breaststroke','Silver','RSA','VAN DER BURGH Cameron');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Singles','Gold','CHN','MA Long');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Singles','Bronze','JPN','MIZUTANI Jun');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Singles','Silver','CHN','ZHANG Jike');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Team','Gold','CHN','China');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Team','Bronze','GER','Germany');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Table Tennis','M','Men''s Team','Silver','JPN','Japan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Gold','AZE','ISAEV Radik');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Bronze','KOR','CHA Dongmin');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Bronze','BRA','SIQUEIRA Maicon');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men +80kg','Silver','NIG','ISSOUFOU ALFAGA Abdoulrazak');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men -58kg','Gold','CHN','ZHAO Shuai');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Taekwondo','M','Men -58kg','Silver','THA','HANPRAB Tawin');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Gold','ESP','Lopez');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Bronze','USA','Johnson');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Bronze','USA','Sock');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Silver','ROU','Tecau');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Silver','ROU','Mergea');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Tennis','M','Men''s Doubles','Gold','ESP','Nadal');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','M','Men','Gold','BLR','HANCHAROU Uladzislau');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','M','Men','Silver','CHN','DONG Dong');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','M','Men','Bronze','CHN','GAO Lei');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','W','Women','Gold','CAN','MACLENNAN Rosannagh');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','W','Women','Silver','GBR','PAGE Bryony');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Trampoline Gymnastics','W','Women','Bronze','CHN','LI Dan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','M','Men','Gold','GBR','BROWNLEE Alistair');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','M','Men','Silver','GBR','BROWNLEE Jonathan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','M','Men','Bronze','RSA','SCHOEMAN Henri');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','W','Women','Gold','USA','JORGENSEN Gwen');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','W','Women','Silver','SUI','SPIRIG HUG Nicola');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Triathlon','W','Women','Bronze','GBR','HOLLAND Vicky');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','M','Men','Gold','BRA','Brazil');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','M','Men','Silver','ITA','Italy');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','M','Men','Bronze','USA','United States');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','W','Women','Gold','CHN','China');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','W','Women','Silver','SRB','Serbia');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Volleyball','W','Women','Bronze','USA','United States');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','M','Men','Gold','SRB','Serbia');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','M','Men','Silver','CRO','Croatia');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','M','Men','Bronze','ITA','Italy');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','W','Women','Gold','USA','United States');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','W','Women','Silver','ITA','Italy');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Water Polo','W','Women','Bronze','RUS','Russian Federation');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s +105kg','Gold','GEO','TALAKHADZE Lasha');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s +105kg','Bronze','GEO','TURMANIDZE Irakli');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s +105kg','Silver','ARM','MINASYAN Gor');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s 105kg','Gold','UZB','NURUDINOV Ruslan');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s 105kg','Bronze','KAZ','ZAICHIKOV Alexandr');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Weightlifting','M','Men''s 105kg','Silver','ARM','MARTIROSYAN Simon');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Gold','TUR','AKGUL Taha');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Bronze','BLR','SAIDAU Ibrahim');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Bronze','GEO','PETRIASHVILI Geno');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 125 kg','Silver','IRI','GHASEMI Komeil Nemat');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 57 kg','Gold','GEO','KHINCHEGASHVILI Vladimer');
Insert into olympic_medal_winners (OLYMPIC_YEAR,SPORT,GENDER,EVENT,MEDAL,NOC,ATHLETE) values (2016,'Wrestling','M','Men''s Freestyle 57 kg','Bronze','AZE','ALIYEV Haji');
-- This pivots the results by medal. But the columns not listed
-- in the pivot form an implicit group by. So this gives the
-- medal total per athlete per event.
select * from olympic_medal_winners
pivot ( count(*) for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by noc
fetch first 6 rows only;
-- To overcome the problem in the previous statement,
-- his selects just the columns you need in the subquery.
-- But some events have multiple people who win the
-- same medal - e.g. doubles tennis. This pivot
-- counts rows in the table, not individual events.
select * from (
select noc, medal from olympic_medal_winners
)
pivot ( count(*) for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 3 desc, 4 desc
fetch first 5 rows only;
-- This solves the over counting problem in the
-- previous statement. It does this by finding the
-- distinct values for sport, event and gender
-- then counting the results.
select * from (
select noc, medal, sport, event, gender
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 3 desc, 4 desc
fetch first 5 rows only;
-- You can have many functions in the pivot.
-- Oracle generates a column for each function
-- per value in the in clause. This finds the
-- gold medal winning countries. For each it shows:
-- - The number of different events these were won in
-- - The number of different sports thy were won in
-- - The names of the athlete or team who won each medal
-- Finally it filters to only show those countries
-- that won at least two gold medals.
select * from (
select noc, medal, sport, event, gender, athlete
from olympic_medal_winners
)
pivot (
count( distinct sport ||'#'|| event ||'#'|| gender ) medals,
count( distinct sport ) sports,
listagg( athlete, ',') within group (order by athlete) athletes
for medal in ( 'Gold' gold )
)
where gold_medals > 1
order by gold_medals, gold_sports, noc
fetch first 5 rows only;
-- This is similar to the previous query.
-- But it finds those countries whose IOC code starts with D.
select * from (
select noc, medal, sport, event, gender, athlete
from olympic_medal_winners
)
pivot (
count( distinct sport ||'#'|| event ||'#'|| gender ) medals,
count( distinct sport ) sports,
listagg( athlete, ',') within group (order by athlete) athletes
for medal in ( 'Gold' gold )
)
where noc like 'D%'
order by gold_medals;
-- This produces a matrix, sports across the
-- top countries down the side. There's an X
-- for each sport that country has a row in the table for.
select * from (
select noc, sport
from olympic_medal_winners
)
pivot (min('X') for sport in (
'Archery' as arc, 'Athletics' as ath, 'Hockey' as hoc,
'Judo' as jud, 'Sailing' as sai, 'Wrestling' as wre
)
)
order by noc
fetch first 7 rows only;
-- This is the old school, pre Oracle Database
-- 11g method for pivoting data.
select noc,
count ( case when medal = 'Gold' then 1 end ) gold_medals,
count ( case when medal = 'Silver' then 1 end ) silver_medals,
count ( case when medal = 'Bronze' then 1 end ) bronze_medals
from olympic_medal_winners
group by noc
order by 2 desc, 3 desc, 4 desc
fetch first 5 rows only;
-- An example of how to build the pivot clause
-- values dynamically.
-- Note that when you do this the number of
-- columns can change between runs. So the
-- execute and fetch routine will be far
-- more complex in a real world scenario!
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || sport || ''' as "' || sport || '"', ',') within group (order by sport)
into pivot_clause
from (select distinct sport from olympic_medal_winners);
sql_stmt := 'select * from (select noc, sport from olympic_medal_winners)
pivot (count(*) for sport in (' || pivot_clause || '))';
dbms_output.put_line( sql_stmt );
execute immediate sql_stmt;
end;
/
-- The XML keyword dynamically builds the
-- list of values to pivot. But you get the
-- results in XML! Each "column" is an
-- element in this document.
select * from (
select noc, sport
from olympic_medal_winners
)
pivot xml (count(*) medal_winners for sport in (
select sport
from olympic_medal_winners
where sport like 'A%')
)
where rownum = 1;
-- This previous example gave every country
-- at least one medal in every sport! To
-- avoid this, you need to count a column
-- which will be null if the country
-- didn't win in a particular event.
select * from (
select noc, sport, athlete
from olympic_medal_winners
)
pivot xml (count(athlete) medal_winners for sport in (
select sport
from olympic_medal_winners
where sport like 'A%')
)
where rownum = 1;
-- This creates the final medal table
-- for the unpivot example below.
create table olympic_medal_tables as
select * from (
select noc, medal, sport, event, gender
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) for medal in (
'Gold' gold_medals, 'Silver' silver_medals, 'Bronze' bronze_medals
))
order by 2 desc, 3 desc, 4 desc;
-- Unpivot takes the columns and
-- converts them back to rows.
select * from olympic_medal_tables
unpivot (medal_count for medal_colour in (
gold_medals as 'GOLD',
silver_medals as 'SILVER',
bronze_medals as 'BRONZE'
))
order by noc
fetch first 6 rows only;
drop table olympic_medal_tables purge;
create table olympic_medal_tables as
select * from (
select noc, medal, sport, event, gender
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) medals,
count(distinct sport) sports
for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 4 desc, 6 desc;
-- You can unpivot two or more columns
-- to a single row. To do this, provide a
-- list of the columns you want to combine.
-- You then get a column for each in the results.
select * from olympic_medal_tables
unpivot ((medal_count, sport_count) for medal_colour in (
(gold_medals, gold_sports) as 'GOLD',
(silver_medals, silver_sports) as 'SILVER',
(bronze_medals, bronze_sports) as 'BRONZE'
))
fetch first 9 rows only;
drop table olympic_medal_tables purge;
create table olympic_medal_tables as
select * from (
select noc, medal, sport, event, gender, athlete
from olympic_medal_winners
)
pivot ( count(distinct sport ||'#'|| event ||'#'||gender ) medals,
listagg(athlete, ',') within group (order by athlete) athletes
for medal in (
'Gold' gold, 'Silver' silver, 'Bronze' bronze
))
order by 2 desc, 4 desc, 6 desc;
-- Another example of unpivoting multiple columns.
-- This time with a list of athletes.
select * from olympic_medal_tables
unpivot ((medal_count, athletes) for medal_colour in (
(gold_medals, gold_athletes) as 'GOLD',
(silver_medals, silver_athletes) as 'SILVER',
(bronze_medals, bronze_athletes) as 'BRONZE'
))
where medal_colour = 'GOLD'
and medal_count = 2
order by noc
fetch first 3 rows only;
-- This first unpivots the results to get the
-- list of athletes won two gold medals. It
-- then uses XML tokenization to split
-- the list into a row per person.
with rws as (
select * from olympic_medal_tables
unpivot ((medal_count, athletes) for medal_colour in (
(gold_medals, gold_athletes) as 'GOLD',
(silver_medals, silver_athletes) as 'SILVER',
(bronze_medals, bronze_athletes) as 'BRONZE'
))
where medal_colour = 'GOLD'
and medal_count = 2
)
select noc, athlete
from rws, xmltable (
'if (contains($X,",")) then ora:tokenize($X,"\,") else $X'
passing athletes as X
columns athlete varchar2(4000) path '.'
)
order by 1, 2
fetch first 6 rows only;
-- This creates the table of medals won by
-- each country per sport for use in the
-- examples below.
create table olympic_country_sport_medals as
select * from (
select noc, sport
from olympic_medal_winners
)
pivot (count(sport) for sport in (
'Athletics' as ath, 'Artistic Gymnastics' as gym, 'Cycling Track' as cyc,
'Boxing' as box, 'Sailing' as sai
)
)
order by 1;
-- This switches the rows and columns over
-- aka a transpose. It does so by chaining
-- a pivot followed by an unpivot.
select * from olympic_country_sport_medals
pivot (
sum(ath) ath, sum(box) box, sum(gym) gym, sum(sai) sai, sum(cyc) cyc
for noc in ('BRA' BRA, 'CHN' CHN, 'DEN' DEN, 'ESP' ESP, 'ETH' ETH, 'GRE' GRE )
)
unpivot (
(BRA, CHN, DEN, ESP, ETH, GRE ) for sport in (
(BRA_ATH, CHN_ATH, DEN_ATH, ESP_ATH, ETH_ATH, GRE_ATH) as 'Athletics',
(BRA_GYM, CHN_GYM, DEN_GYM, ESP_GYM, ETH_GYM, GRE_GYM) as 'Artistic Gym',
(BRA_BOX, CHN_BOX, DEN_BOX, ESP_BOX, ETH_BOX, GRE_BOX) as 'Boxing',
(BRA_SAI, CHN_SAI, DEN_SAI, ESP_SAI, ETH_SAI, GRE_SAI) as 'Sailing',
(BRA_CYC, CHN_CYC, DEN_CYC, ESP_CYC, ETH_CYC, GRE_CYC) as 'Track Cycling'
)
);
-- Tranposing data using unpivot and pivot.
-- Much easier to write than the other way around!
select * from olympic_country_sport_medals
unpivot (
(medals) for sport in ( ath, box, gym, sai, cyc )
)
pivot (
sum(medals) for noc in (
'BRA' BRA, 'CHN' CHN, 'DEN' DEN, 'ESP' ESP, 'ETH' ETH, 'GRE' GRE
)
);
I explore ETags and how they can be used in cURL commands when interacting with Oracle REST APIs. I also discuss some of the performance benefits of using ETags. This is not exhaustive, but I hope it introduces you to ETags or reminds you of their existence! But first…
LATE-BREAKING NEWS!!
A related video
FYI: I reference a CSV_DATA table throughout this post. We use it pretty extensively in this LiveLab. And we just recently presented a webinar based on that same LiveLab. You can check that out below!
Don’t know what ETags are? No worries, here is a definition:
The ETag (or entity tag) HTTP response header is an identifier for a specific version of a resource. It lets caches be more efficient and save bandwidth, as a web server does not need to resend a full response if the content was not changed. Additionally, etags help to prevent simultaneous updates of a resource from overwriting each other (“mid-air collisions”).
If the resource at a given URL changes, a new Etag value must be generated. A comparison of them can determine whether two representations of a resource are the same.
ETags can help to guarantee the provenance of your resources (like the auto-REST enabled table you’ll see shortly) but they can also ensure your applications consume fewer server/database resources, and load comparatively faster.
To illustrate how ETags work, I did some tinkering with cURL commands, ORDS, and a Podman container. Read on if ye dare…to see what I discovered!
Oracle REST APIs and ETags
A couple of weeks ago, I noticed in the cURL documentation there was support for ETags. And the cURL docs have options for both --etag-save and --etag-compare (practical examples to follow). When you use these options in your cURL commands, you’ll either:
save an eTag to a separate text file (locally, like on your desktop in my example below), or
compare the ETag (in that existing file) to an ETag that belongs to your REST-enabled resource (the CSV_DATA table, which you’ll see in a second)
Oh, that’s a lot of words! So read it again and then continue with my walkthrough. Meanwhile, I’ll spin up this Podman container.
We are back in Podman.
INFO: Want to learn more about using Podman and Oracle database tools? Check out my other two Podman-related posts here and here!
ORDS in Standalone mode
I need ORDS up and running for this demonstration, so I issued the ords serve command in my Terminal. This will launch ORDS in standalone mode (using a Jetty server, as seen in the image). Once it’s initialized, I can log into SQL Developer Web to interact with my database (remember, in this example, it lives in a Podman container).
Here, I’ve logged into SQL Developer Web as a non-ADMIN user (ORDSTEST in this case).
From the Database Actions Launchpad, I navigated to the SQL Worksheet.
And to keep this quick, I reused a table I created for that webinar we just did. I also auto-REST enabled it (so I could play with the cURL commands). Below, you’ll see it’s just a quick right-click with the mouse.
FYI: As a reminder, if you want to learn more about cURL commands, check out the LiveLabs workshop that this is based on. You can find that here.
Getting the cURL Command
Once I auto-REST enabled the CSV_DATA table, I selected the GET ALL REST cURL command.
This is the cURL command I’ll use for this experiment.
At this point, I still wasn’t sure that an ETag was sent from the server for those auto-REST-enabled resources (in this case, the CSV_DATA table). I know they are present when you build your own REST Modules with ORDS; (at the time) I was just less confident about the auto-REST resources.
SPOILER ALERT: ETags are present for auto-REST-enabled resources too (I'm dumb, and this is pretty widely known)!
–etag cURL options
Once I knew ETags were accessible for auto-REST-enabled resources, I experimented with cURL‘s --etag options (you’ll see how I implemented these in the upcoming cURL command).
The --etag-save [filename] and --etag-compare [filename] options work such that when you issue the --etag-save in addition to that initialcURL command, a single-line file will be saved to the directory you are currently in (you’ll see that file shortly).
This differs from how an application might work, but the concept is the same. You’re storing the ETag’s value somewhere accessible to the application. For my purposes, I need to keep this ETag somewhere the cURL command line utility can find it.
The initial cURL command
I hopped over to my Terminal and used that [slightly modified] cURL command (the one I previously retrieved from the SQL Worksheet). You’ll see that I included additional options/arguments:
--verbose
--etag-save
| json_pp
This is the first cURL command I issued.
FYI: Apparently, the json_pp command utility is a part of Perl. I think this ships with the macOS, but I'm not 100% sure. Do you know? It worked for me and pretty printed out my JSON response (notice how I used the pipe "|" in addition to the actual command).
When you use that --etag-save option, a file with the value of the ETag will be saved locally. You can see me retrieving that file and reviewing the ETag file (note in the above cURL command, I named the file “myobjectetag.txt“).
Listing the files in the current directory.Locating the myobjectetag.txt file.Opening the file and inspecting the ETag value.
I can now use this ETag in subsequent GET requests to determine if the resource (the CSV_DATA table) I’m requesting has changed since I last interacted with it. What would constitute a change? Maybe rows have been updated or removed; perhaps an additional column was added. Or maybe the table was restructured somehow; it could be any change.
But, let me pause briefly and explain the --verbose option.
About the verbose option
The printout from the --verbose option.The remaining JSON object is nicely printed out.
I used the --verbose option to inspect the information available when interacting with this Oracle REST endpoint. I don’t need to include it now since I know the ETag is coming through, but I left it in this cURL command example so that you could have a look yourself. You’ll see loads of information, including (but not limited to):
Connection information
The cURL version used
The Status Code returned (200 or OK in this case)
ETag info
In this example, all I care about is the presence of an ETag. I can now use that ETag in a subsequent GET request to determine if the resource on the server side has changed. Here is what the cURL command looks like with the --etag-compare option:
That cURL command looks very similar, except for that --etag-compare option. In this situation, cURL first checks to see if your ETag and the resource’s (the API endpoint on your server) ETag match. If they do, the request stops. And if you use the --verbose option, you can see what comes back from the server:
A whole bunch of nothing. Not really, though. That “If-None-Match” Header is the secret sauce, though. That is a conditional Header that is passed over to the server. Essentially it says, “If this Header value doesn’t match yours, then send over the requested data; otherwise, end the request here because we already have the information we need/. It’s stored/saved (presumably) locally.“
INFO:Read up on caches, because that's essentially what your application is going to use instead of having to go through the entire GET request/response cycle.
The request is terminated, but what does this mean from a performance perspective? Well, say you have a webpage that loads and later reloads in response to a user’s interaction (I simulated this with the two cURL commands). That page will probably need some information from the server to populate that page. In a situation like this, you could first ask your application to share your copy of the ETag with the server in a subsequent GET request header (“If-None-Match“). And if nothing has changed, you could speed up page load times by just refreshing with what you have stored in a cache while freeing up resources on your server for other processes. But this is just one example.
Possibilities with ETag
I’ve given this some thought, and I bet there are quite a few use cases where referring to an ETag before executing an HTTP method (like a GET or GET ALL) might be helpful.
You may want to periodically check to see if a resource has changed since you last interacted with it. Could you incorporate ETags into your build processes or longer-running jobs (maybe something around data analysis)?
Actually, ETags play a massive role in JSON-Relational Duality Views. We have an entire section in the ORDS docs on how to use them! And suppose you want to download a containerized version of the Oracle database 23C (the one that supports JSON-Relational Duality views). You can do that via this link (I think I should do this too and highlight some of the cool ORDS + JSON Duality View features)!
Well, this brings me to the end of this post. I’m hoping you learned something and came away with some good resources. And if you found this post helpful, please pass it along! And don’t be afraid to comment too! I’d love to hear your thoughts. Maybe I’ll even include your idea in a follow-up post 🤩!
Overview and connecting with the python-oracledb library
Part II
Connecting with Oracle REST APIs unauthenticated
Part III
Custom Oracle REST APIs with OAuth2.0 Authorization
Welcome back
I finally had a break in my PM duties to share a small afternoon project [I started a few weeks ago]. I challenged myself to a brief Python coding exercise. I wanted to develop some code that allowed me to connect to my Autonomous Database using either our python-oracledb driver (library) or with Oracle REST Data Services (ORDS).
I undertook this effort as I also wanted to make some comparisons and maybe draw some conclusions from these different approaches.
NOTE: If you don't feel like reading this drivel, you can jump straight to the repository where this code lives. It's all nicely commented and has everything you need to get it to work. You can check that out here.
The test files
Reviewing the code, I’ve created three Python test files. test1.py relies on the python-oracledb library to connect to an Oracle Autonomous database while test2.py and test3.py rely on ORDS (test3.py uses OAuth2.0, but more on that later).
test1.py using the python-oracledb librarytest2.py relies on an unsecured ORDS endpointtest3.py with ORDS, secured with OAuth2
Configuration
Configuration directory
I set up this configuration directory (config_dir) to abstract sensitive information from the test files. My ewallet.pem and tnsnames.ora files live in this config_dir. These are both required for Mutual TLS (mTLS) connection to an Oracle Autonomous database (you can find additional details on mTLS in the docs here).
ewallet.pem and tnsnames.ora files
Other files
OAuth2.0, Test URLs, and Wallet Credential files
Other files include oauth2creds.py, testurls.py, and walletcredentials.py. Depending on the test case, I’ll use some or all of these files (you’ll see that shortly).
NOTE: If not obvious to you, I wouldn't put any sensitive information into a public git repository.
Connecting with python-oracledb
One approach to connecting via your Oracle database is with the python-oracledb driver (library). An Oracle team created this library (people much more experienced and wiser than me), and it makes connecting with Python possible.
FYI: I’m connecting to my Autonomous Database. If you want to try this, refer to the documentation for using this library and the Autonomous database. You can find that here.
The Python code that I came up with to make this work:
#Connecting to an Oracle Autonomous Database using the Python-OracleDB driver.
import oracledb
# A separate python file I created and later import here. It contains my credentials, so as not to show them in this script here.
from walletcredentials import uname, pwd, cdir, wltloc, wltpwd, dsn
# Requires a config directory with ewallet.pem and tnsnames.ora files.
with oracledb.connect(user=uname, password=pwd, dsn=dsn, config_dir=cdir, wallet_location=wltloc, wallet_password=wltpwd) as connection:
with connection.cursor() as cursor:
# SQL statements should not contain a trailing semicolon (“;”) or forward slash (“/”).
sql = """select * from BUSCONFIND where location='ZAF'
order by value ASC """
for r in cursor.execute(sql):
print(r)
In Line 7, you can see how I import the wallet credentials from the walletcredentials.py file. Without that information, this code wouldn’t work. I also import the database username, password, and configuration directory (which includes the ewallet.pem and tnsnames.ora files).
From there, the code is pretty straightforward. However, some library-specific syntax is required (the complete details are in the docs, found here), but aside from that, nothing is too complicated. You’ll see the SQL statement in Lines 16-17; the proper SQL format looks like this:
SELECT * FROM busconfind WHERE location='zaf'
ORDER BY value ASC;
And here is an example of this SQL output in a SQL Worksheet (in Database Actions):
Reviewing the SQL in Database Actions
FYI: This is a Business Confidence Index data-set, in case you were curious (retrieved here).
That SQL allows me to filter on a Location and then return those results in ascending orderaccording to the Value column. When I do this using the python-oracledb driver, I should expect to see the same results.
NOTE: You've probably noticed that the SQL in the python file differs from that seen in the SQL Worksheet. That is because you need to escape the single quotes surrounding ZAF, as well as remove the trailing semi-colon in the SQL statement. Its all in the python-oracledb documentation, you just have to be aware of this.
Once I have all the necessary information in my walletcredentials.py file, I can import that into the test1.py file and execute the code. I chose to run this in an Interactive Window (I’m using VS Code), but you can also do this in your Terminal. In the images (from left to right), you’ll see the test1.py file, then a summary of the output from that SQL query (contained in the test1.py code), and finally, the detailed output (in a text editor).
Executing the Python code in an Interactive WindowSummary output from test1.pyDetailed output from test1.py
Wrap-up
For those that have an existing Free Tier tenancy, this could be a good option for you. Of course, you have to do some light administration. But if you have gone through the steps to create an Autonomous database in your cloud tenancy, you probably know where to look for the tnsnames.ora and other database wallet files.
I’m not a developer, but I think it would be nice to simplify the business logic found in this Python code. Maybe better to abstract it completely. For prototyping an application (perhaps one that isn’t micro services-oriented, this could work) or for data- and business analysts, this could do the trick for you. In fact, the data is returned to you in rows of tuples; so turning this into a CSV or reading it into a data analysis library (such as pandas) should be fairly easy!
Connecting via ORDS: sans OAuth2.0
Auto-REST and cURL
I’m still using the “devuser” (although this may be unnecessary, as any unsecured REST-enabled table would do). I’m using the same table as before; the only change I’ve made is to auto-REST enable the BUSCONFIND table for the test2.py code.
In the following images, I’m retrieving the cURL command for performing a GET request on this table.
NOTE: In a recent ORDS update, we made available different shell variations (this will depend on your OS); I've selected Bash.
From there, I take the URI (learn more on URIs) portion of the cURL command and place it into my browser. Since this table is auto-REST enabled, I’ll only receive 25 rows from this table.
NOTE: The ORDS default pagination is limit = 25.
Getting the cURL command from an already ORDS REST-enabled tableSelecting the GET request for BashGET response in JSONThe raw JSON, pretty printed
The code
And the code for this test2.py looks like this:
# Auto-REST enabled with ORDS; in an Oracle Autonomous Database with query parameters.
import requests
import pprint
# Importing the base URI from this python file.
from testurls import test2_url
# An unprotected endpoint that has been "switched on" with the ORDS Auto-REST enable feature.
# Query parameters can be added/passed to the Base URI for GET-ing more discrete information.
url = (test2_url + '?q={"location":"ZAF","value":{"$gt":100},"$orderby":{"value":"asc"}}}')
# For prototyping an application, in its earlier stages, this could really work. On your front end, you
# expect the user to make certain selections, and you'll still pass those as parameters.
# But here, you do this as a query string. In later stages, you may want to streamline your application
# code by placing all this into a PL/SQL or SQL statement. Thereby separating application
# logic and business logic. You'll see this approach in the test3.py file.
# This works, but you can see how it gets verbose, quick. Its a great jumping-off point.
responsefromadb = requests.get(url)
pprint.pprint(responsefromadb.json())
Lines 8 and 13 are the two areas to focus on in this example. In Line 8 imported my URL from the testurls.py file (again, abstracting it, so it’s not in the main body of the code).
The test2.py and testurls.py files
And then, in Line 13, I appended a query string to the end of that URL. ORDS expects the query parameters to be a JSON object with the following syntax:
[ORDS Endpoint]/?q={"JSON Key": "JSON Value"}
The new, complete query string below requests the same information as was requested in the test1.py example:
This string begins with that same BASE URI for the ORDS endpoint (the auto-REST enabled BUSCONFIND table) and then applies the query string prefix “?q=” followed by the following parameters:
Filter by the location "ZAF"
Limit the search of these locations to values (in the Value column) greater than ($gt) 100
Return these results in ascending order (asc) of the Value column
NOTE: You can manipulate the offsets and limits in the python-oracledb driver too. More info found here. And filtering in queries with ORDS can be found here.
And if I run the test2.py code in the VS Code Interactive Window, I’ll see the following summary output.
Summary output from the response in test2.py
Here is a more detailed view in the VS Code text editor:
Detailed output with helpful links
Wrap-up
A slightly different approach, right? The data is all there, similar to what you saw in the test1.py example. There are a few things to note, though:
The consumer of this ORDS REST API doesn’t need access to the database (i.e. you don’t need to be an admin or have a schema); you can perform GET requests on this URI.
The response body is in JSON (ubiquitous across the web and web applications)
Also, language and framework agnostic (the JSON can be consumed/used widely, and not just with Python)
You are provided a URI for each item (i.e. entry, row, etc.)
No need for SQL; just filter with the JSON query parameters
No business logic in the application code
Needless to say, no ORMs or database modeling is required for this approach
However…security is, ahem…nonexistent. That is a problem and flies in the face of what we recommend in our ORDS Best Practices.
Connecting via ORDS: secured with OAuth2
Note: This is an abbreviated explanation, I'll be posting an expanded write-up on this example post haste!
Since this is what I’m considering “advanced” (it’s not difficult, there are just many pieces) I’m going to keep this section brief. Long story short, I’ll take those query parameters from above and place them into what is referred to as a Resource Handler.
TIME-OUT: Auto-REST enabling a database object (the BUSCONFIND table in this case) is simple in Database Actions. Its a simple left-click > REST-enable. You saw that in the previous example. You are provided an endpoint and you can use the query parameters (i.e. the JSON {key: value} pairs) to access whatever you need from that object.
However, creating a custom ORDS REST endpoint is a little different. First you create a Resource Module, next a (or many) Resource Template/s, and then a (or many) Resource Handler/s. In that Resource Handler, you'll find the related business logic code for that particular HTTP operation (the menu includes: GET, POST, PUT, and DELETE).
The Resource Module
The process of creating a custom ORDS API might be difficult to visualize, so I’ll include the steps I took along with a sample query (in that Resource Handler) to help illustrate.
Creating the Resource Module in the ORDS REST WorkshopCreating the Resource TemplateReviewing the available operations for the Resource TemplateThe newly created Resource GET HandlerPlacing the SQL directly into the Resource HandlerTesting out the code to simulate a GET request using "ZAF" as the locationReviewing the output of that SQL query, in a table format
Chances are you may be the administrator of your Always Free tenancy, so you have full control over this. Other times, you might be provided the REST endpoint. In that case, you may not ever have to worry about these steps. Either way, you can see how we’re simulating (as well as both abstracting and keeping the business logic in the database) the query with this final example (test3.py).
Security
The OAuth 2.0 authorization framework enables a third-party application to obtain limited access to an HTTP service, either on behalf of a resource owner by orchestrating an approval interaction between the resource owner and the HTTP service, or by allowing the third-party application to obtain access on its own behalf.
RFC 6749: The OAuth 2.0 Authorization Framework
I’ll keep this section brief, but I’m protecting this resource through the aid of an ORDS OAuth2.0 client. I’ve created one here:
After creating a client you can use the provided URL for requesting a new Bearer Token
And, as you’ll see shortly, I’ll rely on some Python libraries for requesting an Authorization Token to use with the related Client ID and Client Secret. If you want to nerd out on the OAuth2.0 framework, I challenge you to read this.
test3.py example
NOTE: Remember, I'm keeping this section intentionally brief. It deserves a slightly deeper dive, and class is almost over (so I'm running out of time).
The code for this example:
# Custom ORDS Module in an Oracle Autonomous Database.
import requests
from requests_oauthlib import OAuth2Session
from oauthlib.oauth2 import BackendApplicationClient
import pprint
import json
# Importing the base URI from this python file.
from testurls import test3_url
# A separate python file I created and later import here. It contains my credentials,
# so as not to show them in this script here.
from oauth2creds import token_url, client_id, client_secret
token_url = token_url
client_id = client_id
client_secret = client_secret
client = BackendApplicationClient(client_id=client_id)
oauth = OAuth2Session(client=client)
token = oauth.fetch_token(token_url, client_id=client_id, client_secret=client_secret)
bearer_token = token['access_token']
# Location can be anything from the table. Now, only the single variable needs to be passed. Business logic has been abstracted somewhat; as it now resides within
# ORDS. This could make your application more portable (to other languages and frameworks, since there are fewer idiosyncracies and dependencies):
location = "ZAF"
# print(location)
# ------------------------------------------------------------------------------ #
# In Database Actions, we:
# 1. Create an API Module
# 2. Then create a Resource Template
# 3. Finally, a GET Resource Handler that consists of the code from test1.py:
# select * from BUSCONFIND where location= :id
# order by value ASC
# ------------------------------------------------------------------------------ #
url = (test3_url + location)
# print(url)
responsefromadb = requests.get(url, headers={'Authorization': 'Bearer ' + bearer_token}).json()
# This step isn't necessary; it simply prints out the JSON response object in a more readable format.
pprint.pprint(responsefromadb)
Lines 11 and 16 deserve some attention here. The URL for Line 11 comes from the testurls.py file; seen in the previous example. And the contents from Line 16 come from the oauth2creds.py file. Here are the files, side-by-side:
The test3.py, testurls.py, and oauth2creds.py files
As you can see in the testurls.py file, I’m relying on the test3_url for this example. And the OAuth2.0 information you see comes directly from the OAuth Client I created in Database Actions:
In this image, you can see the Client ID and Client Secret
If I put that all together, I can execute the code in test3.py and “pretty print” the response in my Interactive Window. But first I need to adjust the Resource Handler’s URI (the one I copied and pasted from the “REST Workshop”). It retains the “:id” bind parameter. But the way I have this Python code set up, I need to remove it. It ends up going from this:
With that out of the way, I can run this code and review the output.
Running the test3.py code in the Interactive WindowReviewing the summary output – a JSON arrayReviewing the detailed view of the “items“Scrolling to the bottom of the GET response body to see the available links for additional items
From top-to-bottom, left-to-right you’ll see I first execute the code in the Interactive Window. From there I can review a summary of the response to my GET request. That pretty print library allows us to see the JSON array in a more readable format (one that has indentation and nesting); which you can see in the second image. The third image is a more detailed view of the first half of this response. And I include the final image to highlight the helpful URLs that are included in the response body.
Since I know my limit = 25, and the 'hasMore': True (seen in the output in that third image) exists, I know there are more items. You can adjust the limit and offset in subsequent requests, but I’ll save that for another day.
Wrap-up
You can probably tell, but this is like an expansion of the previous example. But instead of relying on the auto-REST enabling, you are in full control of the Resource Module. And while you don’t need to use OAuth2.0 it’s good practice to use it for database authentication. You can see how the response comes through a little differently, compared to the previous example, but still very similar.
In this example, I did all the work, but that might not be the case for you; much of it might be handled for you. The main thing I like about this example is that we rely on stable and popular Python libraries: requests, requests_oauthlib, and oautlib.
The fact that this is delivered as a JSON object is helpful as well (for the same reasons mentioned in the second example). And finally, I enjoy the fact that you only need to pass a single parameter from your (assumed) presentation layer to your application layer; an example might be a selection from an HTML form or drop-down menu item.
The end
We’re at the end of this fun little exercise. As I mentioned before, I will expand on this third example. There are so many steps, and I think it would be helpful for people to see a more detailed walk-through.
And be on the lookout (BOLO) for a video. There’s no way around this, but a video needs to accompany this post.
And finally, you can find all the code I review in this post in my new “blogs” repository on GitHub. I encourage you to clone, fork, spoon, ladle, knife, etc…
