Tag: json

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

    The DBMS_CLOUD PL/SQL Package

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

    In this example, we call this collection Movie_Collection.

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

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

    Create a view

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

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

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

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

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

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

    Here is the SQL, with the Script Output below:

    ORDSify itยฎ

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

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

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

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

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

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

    A Confirmation notification will appear in the upper right-hand corner of the browser ๐Ÿ‘๐Ÿป.

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

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

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

    Why a view?

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

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

    Oracle Database Administrator’s Guide Release 23

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

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

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

    Oh, and we are entering into the season of Cloud World 2024, so mark your calendars ๐Ÿคช!

    That’s all for now ๐Ÿ˜˜.

    Follow

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

  • HELP!! parse error: Invalid numeric literal at line x, column x?! It’s not your Oracle REST API!!

    HELP!! parse error: Invalid numeric literal at line x, column x?! It’s not your Oracle REST API!!

    A while back (yesterday), I penned a blog post highlighting the ORDS REST-Enabled SQL Service. And in that blog, I displayed the output of a cURL command. A cURL command I issued to an ORDS REST-Enabled SQL Service endpoint. Unfortunately, it was very messy and very unreadable. I mentioned that I would fix it later. Well…it’s now…later (temporal paradox, anybody ๐Ÿคจ?).

    Recap

    If you recall, the output of my POST request looked like this:

    crap-response-from-curl-command-post-request-to-rest-enabled-sql-endpoint-chris-hoina-senior-product-manager-database-actions-oracle
    Yikes, you kiss your mother with that mouth?!

    JSON is not displaying correctly

    Well, the reason why I didn’t originally pipe in the json_pp command is because this is what happened when I attempted it:

    attempting-to-use-the-json-pp-command-for-post-request-curl-chris-hoina-senior-product-manager-database-actions-oracle

    Jefe to the rescue

    After reading my newly published article, Jefe suggested I try the jq command.

    jeff-sage-advice-on-slack-ords-post-chris-hoina-senior-product-manager-database-actions-oracle
    The Yoda to my Padawan

    Which, of course, I did. Still no luck:

    jeffs-suggestion-for-jq-command-for-post-request-curl-chris-hoina-senior-product-manager-database-actions-oracle
    Different issue though

    Andiamo a googliare!

    Online search to the rescue

    Search online using the keywords “parse error: Invalid numeric literal at,” and you’ll quickly discover that you’re not the only one with this problem.

    Five minutes of research revealed a potential culprit. What I was experiencing seemed to be a known issue. For example, a long-standing jq bug on GitHub details this exact scenario. This doesn’t seem to be a jq or json_pp issue. Instead, the problem is somehow related to the -i cURL command option and JSON parsing.

    After another few minutes, as luck would have it, I found a Stack Overflow thread discussing the same issue I encountered! After scrolling to the bottom of the thread, I found this golden nugget:

    removing-header-information-from-curl-request-for-ords-post-chris-hoina-senior-product-manager-database-actions-oracle
    Thank you Mattias and nhs503 ๐Ÿฅฐ

    Testing without -i

    So, I did just what Mattias and nhs503 suggested. I removed the -i option (-i, or –include) from my cURL command, and wouldn’t you know? The damn thing works as expected! I tested while piping jq and json_pp. I also concede that jq is the prettier of the two; I appreciate the colors (although, admittedly, this would NOT pass any accessibility testing).

    The modified commands used:

    curl -X POST --user ORDSTEST:password1234 --data-binary "@sportCountryMatrix.sql" -H "Content-Type: application/sql" -k http://localhost:8080/ords/ordstest/_/sql | jq
    curl -X POST --user ORDSTEST:password1234 --data-binary "@sportCountryMatrix.sql" -H "Content-Type: application/sql" -k http://localhost:8080/ords/ordstest/_/sql | json_pp

    And the results:

    Final thoughts

    And for some final thoughts…

    1. It turns out it’s NOT ORDS – it’s something to do with an underlying JSON parser not liking the header info that is coming through
    2. json_pp and jq both work; they output the information in different order
    3. The ORDS REST-Enabled SQL Service returns to you not only your results, but the SQL statement initially used (that is cool and I didn’t originally realize or mention this)

    And that’s it for this one! I really hope you find this useful. I hope this saves you some time from having to troubleshoot and/or hunt for a fix for this tricky problem. That’s all for now!

    Follow

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