I said on Twitter yesterday that this was one of my favorite features…

So I thought I’d share a few concrete examples here, and clarify something mentioned in the Docs.

3.2.5.2 Filtering in Queries

(Docs) – ” This section describes and provides examples of filtering in queries against REST-enabled tables and views.”

This is true, but it’s ALSO true that this feature is available for SQL based GET Handlers.

Given this REST Service Handler definition:

Maybe we want to add some more WHERE clauses or an ORDER BY?

I can use the same features for AUTO REST enabled TABLES for filtering/ordering my collection items to get JUST the data I want on this RESTful Service. Let’s walk through some examples!

WHERE BEER_TYPE LIKE ‘%Triple%’

This translates to this query string –

?q={"beer_type":{"$like":"%25Tripel%25"}}

The %25 is to URL encode the % character, and making this request, gives me what I’m looking for.

A quadruple, tripel play!

WHERE BEER_TYPE LIKE ‘%TRIPLE%’ AND BEER_ABV > 9.5

?q={"beer_type":{"$like":"%25Tripel%25"},"beer_abv":{"$gt":9.5}}

WHERE BEER_TYPE LIKE ‘%TRIPLE%’ AND BEER_ABV > 9.5
ORDER BY BEER_NAME DESC

?q={"beer_type":{"$like":"%25Tripel%25"},"beer_abv":{"$gt":9.5},"$orderby":{"beer_name":"desc"}}
Still my Tripels and 9.5+% items, but now also sorted.

I’m not that good with {JSON}

I don’t want you to think I can just type out these query strings in my browser URL bar…I mean, I can, but when they get nested/complex, I go to something like JSONLint and validate my requests there, especially when ORDS starts giving me 400 BAD REQUEST type errors.

You can also specify the data for a given SCN or Timestamp (Flashback!)

Fearful your data is changing as you page through a particularly long record set? Throw on a “$asof” with a SCN# or a DATE!

Note that DATES in ORDS are always treated in Zulu Time!
thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

Write A Comment