Category: Troubleshooting

  • Troubleshooting: reviewing ORDS connections, your application server, and response times

    Troubleshooting: reviewing ORDS connections, your application server, and response times

    Symptom/Issue

    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://[my host]/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:

    1. if it’s even up
    2. what the response is, and
    3. 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 intact
    
    Total time: 0.001882

    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.

    https://[my host]/ords/[target schema]/metadata-catalog/

    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!

    1. 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. ↩︎
    2. 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.” ↩︎
  • 401 Unauthorized invalid_token – troubleshooting Oracle Cloud IAM JWTs with ORDS

    401 Unauthorized invalid_token – troubleshooting Oracle Cloud IAM JWTs with ORDS

    Symptoms

    You are able to request an access token from OCI IAM. Yet, when you issue the subsequent request to your target resource (an ORDS endpoint), you receive the following message (error="invalid_token"): 

    WWW-Authenticate: Bearer realm="Oracle REST Data Services", error="invalid_token"

    Actions you’ve taken

    1. You’ve done the following in OCI:
      • Registered an Integrated Application with Oracle Identity and Access Management (IAM)
      • Created a Primary Audience & Scope
      • Obtained your Client ID and Client Secret
      • Configured your networking correctly (or at least have high confidence it’s configured correctly)
      • Acquired all of the correct, relevant URIs:
        • Authorization
        • Access Token
        • JWK
    2. You’ve configured the following in ORDS:
      • Created your JWT Profile according to the docs (or this tutorial)
      • Created the JWT Role and Privilege (which should be the same as the OCI Scope name)
      • And protected your target resource (aka ORDS API)
    3. You’ve placed everything where it should be in your choice of API testing tool (cURL, Postman, Insomnia, etc.).

    YET…you still receive this error="invalid_token" message, it is quite possible that you have not made the JWK URL publically accessible in OCI IAM. 

    Solution

    Here is how you can verify and resolve this issue. First, navigate to your domain, then select Settings.

    If this Configure client access box is unchecked, it is likely the culprit. Check it, then select Save Changes (the button at the bottom of the screen).

    Then, once you re-attempt your HTTP request, ORDS will be able to:

    1. Access the JWK URL (which you’ve included when you created your JWT Profile)
    2. Verify the authenticity of your JWT, and 
    3. Respond with the results from your resource (ORDS endpoint)

    Et voilà! And that’s it, you’re back in business! 

    To-do list

    I think we have some action items, too: 

    • Investigate this error message and see if we can improve the message to the user (we’ve already filed an enhancement request on this)
    • Update the docs to be more specific on this setting and where to find it (a documentation bug has already been filed for this)
    • Determine if this is a good candidate for adding to the troubleshooting section of our guide
    • Update my JWT tutorial

    And that’s all for now. ORDS version 25.1 is right around the corner, so look out for the official release notes and my accompanying blog too!

    Follow

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

  • Example: an ORDS API that returns a user-configured JSON object

    Example: an ORDS API that returns a user-configured JSON object

    This actually came about from a Support Request. We were troubleshooting a JWT issue, that had ORDS in the mix. Ultimately, this is looking more and more like a networking issue (maybe something to do with routing rules between the load balancer and the backend server).

    But in the course of our troubleshooting, we wanted to rule out some variables. One of those variables was the JWK URL we were using for the ORDS_SECURITY.CREATE_JWT_PROFILE procedure. So we had this idea to kind of fool ORDS into thinking it was visiting the IDCS (the predecessor to Oracle IAM) JWK URL when, in fact, it was just visiting another ORDS endpoint.

    If you’ve seen my JWT + ORDS tutorials (one for OCI IAM, another for Microsoft Azure), then you know when you create an ORDS JWT Profile, it will look something like this:

    BEGIN
      ORDS_SECURITY.CREATE_JWT_PROFILE(
          p_issuer => 'https://identity.oraclecloud.com/',
          p_audience => 'my_primary_audience' ,
          p_jwk_url =>'https://idcs-10a10a10a10a10a10a10a10a.identity.oraclecloud.com/admin/v1/SigningCert/jwk'
      );
      COMMIT;
    END;
    /

    In the above code block, just know that p_issuer is your Identity Provider (e.g., IAM, IDCS, Microsoft Entra, etc.), p_audience is the Primary Audience (which you created when you registered your client application with your Identity Provider), and the p_jwk_url is where ORDS goes to look up the keys for decoding a JWT.

    The eventual JWT that ORDS receives would include the Issuer and Audience, too. But it would also include other values such as the Scope. That Scope name would be associated with the ORDS Privilege–the privilege you created specifically to use for protecting/associating with your ORDS Resource Module. And ORDS would use all these values (and the JWK “keys” found at that JWK URL to decode the JWT) together to:

    1. Verify you are who you say you are, and
    2. You have the proper Scope (or as ORDS interprets it…the proper privilege) to access this protected resource

    Back to the use case. So, for troubleshooting reasons, we took that JSON object located at that JWK URL and recreated it as an ORDS endpoint. Let me point out that this is cool, but what you should take away from this is that there are tons of things you can do with ORDS, and you have a lot of control over how ORDS will send back information to you, your application, or your web client.

    Here is how we structured the Handler code:

    BEGIN
        HTP.P('{"
        keys": [
            {
                "kty": "RSA",
                "x5t#S256": "Lorem ipsum dolor sit amet__pellentesque elementum",
                "e": "AQAB",
                "x5t": "xwSmccaQZDvAZPOpZPHOiQDlLgo",
                "kid": "SIGNING_KEY",
                "x5c": [
                    "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum==",
    "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla ex sed felis. Maecenas auctor risus ac nisl efficitur, sit amet euismod odio finibus. Praesent lacinia nunc id ex tempor, sed tempor nisl porta. Integer semper efficitur arcu, a dictum quam vulputate tempus. Nulla congue dapibus tortor vel volutpat. Curabitur et sollicitudin purus. Mauris quis aliquam augue. Nulla ac leo tristique, ultricies mauris sit amet, ultrices quam. Praesent placerat a lectus sit amet dapibus. Donec rhoncus felis velit, sed placerat nunc pellentesque elementum"
                ],
                "key_ops": [
                    "verify",
                    "wrapKey",
                    "encrypt"
                ],
                "alg": "RS256",
                "n": 
    "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce lorem dui, elementum et odio ut, sollicitudin gravida dui. Ut nec dapibus elit. Morbi sit amet blandit justo. Quisque accumsan turpis metus, vel sodales nisl porta vel. Nunc placerat, mi id eleifend pharetra, massa urna imperdiet arcu, vitae dignissim nulla"
            }
        ]
    }');
    END;

    With that new ORDS endpoint, we (temporarily) recreated the ORDS JWT Profile with the new ORDS API as a proxy for our true JWK URL. Like this (this JWK URL is entirely fake, BTW):

    BEGIN
      ORDS_SECURITY.CREATE_JWT_PROFILE(
          p_issuer => 'https://identity.oraclecloud.com/',
          p_audience => 'my_primary_audience' ,
          p_jwk_url => 'https://abcdefg-myadb.region-01.oraclecloudapps.com/ords/admin/jwk/jwk'
      );
      COMMIT;
    END;
    /

    Hypothetically if you were to navigate to that ORDS URI, you’d see a facsimile of the actual JWK information:

    So now, we’ve temporarily removed the JWK URL from the troubleshooting equation. However, we can still access that protected endpoint since we have a valid token and keys to decode it (courtesy of the ORDS endpoint we created)!

    And this my friends, is the type of mad scientist stuff that I live for. I had to share our experience to underscore two main points:

    1. You have a lot of flexibility with these ORDS APIs, and
    2. When it comes to troubleshooting/root cause analysis, you must break things down into individual parts!

    And that’s it for today. Class dismissed 🤓

    BTW, I took some inspiration from here too. And of course, a link to the JWT section of our docs.

    If you liked this, consider sharing. And if you have any cool or clever ORDS tricks you use, comment below!

    Follow

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

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

    Have you ever seen this?

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

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

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

    Well…Kris knew.

    What does it all mean?

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

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

    Now what?

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

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

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

    When you’re ready…

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

    That’s all folks

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

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

    Follow

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

  • ORDS as a JotForm webhook: An actual customer use case

    Recently, we (one of our Support engineers and I) worked with a customer who had integrated various ORDS POST APIs with a web-based form application called JotForm. JotForm offers integrations for numerous workflows, some official and others you can create independently. That’s where we come in.

    In this example, a user would enter information into an online JotForm form, and when the user hits Submit, that would trigger a Webhook to an ORDS endpoint. But in this customer’s scenario, some of the data he was expecting wasn’t arriving in his database (he was on a 21c, but I tested these in a 23ai Autonomous database—the same still applies to both). For our customer, some of the fields in his table showed NULL.

    A Webhook example

    We wanted to understand better what was happening, so I created an account in Jotform and set up a Webhook similar to his configuration. This Webhook essentially forwards the JotForm form data to a target endpoint (an ORDS POST handler in our case).

    Webhooks are weird

    Here is what I don’t like about Webhooks (perhaps this is just my ignorance on full display):

    1. What if the request doesn’t work or fails? Are reattempts automatic, or is that user information lost forever?
    2. In this case, we have no real idea what is being sent to ORDS (at least not without some creative interrogation).
    3. Without spending too much time with Webhooks, would an APEX front end not be more straightforward (i.e., same ecosystem and it is a direct POST as opposed to a Webhook that forwards a POST request to an POST endpoint…say that five times fast! More on this later…).

    Where our problem starts

    Here, we have an online form in JotForm. On the front end, I’ve set up three fields (these are all customizable in their web application):

    1. Full Name, which consists of First Name and Last Name
    2. Contact number
    3. And a “Radio” button selection 1

    The Workflow

    A user enters their information and clicks Submit. However, after inspecting the Console and Network tabs (in your Developer tools), you’ll notice two things:

    1. No POST requests
    2. No available request body (What does JotForm send to us?)

    The absence of information is unsurprising since I’m working with a third-party tool (it’s a business, man!). There are enumerable reasons why you’d obscure information. Regardless of the cause, that obfuscation complicates matters when you want to ingest the form data into your database. You can follow the debugging advice to view the PHP output sent internally to JotForm. But I don’t know PHP, and I wouldn’t even know how to map PHP to JSON or SQL datatypes, for that matter.

    ORDS Implicit Parameters to the Rescue

    Here’s where you have to get creative. To better understand what was in that POST request, I relied on ORDS’ Implicit Bind parameters to understand what data was sent to us by the Webhook. Figuring this out required some trial and error on my part. But you can start with the :content_type bind parameter to see what kind of POST it is and go from there. In my example, I kept it simple and relied on the :content_type parameter and whatever was included in the POST body (up to this point, I wasn’t sure what to expect). I named that second value incoming_payload (which maps to the “PAYLOAD” column in the JOTFORM_DATA table)

    INFO: I used the :body_json bind parameter, for the sole fact that neither the :body nor the :body_text parameters seemed to work for me. 

    Resource Handler code

    A look at the ORDS Resource Handler code:

    -- The :content_type implicit bind parameter would "map" to the CONTENT_TYPE column in the 
    -- JOTFORM_DATA table, while incoming_payload would map to the PAYLOAD column. 
    
    DECLARE
        incoming_payload CLOB;
    BEGIN
        incoming_payload := :body_json;
        INSERT INTO JOTFORM_DATA VALUES ( :content_type,
                                          incoming_payload );
    
    END;

    Table DDL

    You’ll notice a table in the handler code called JOTFORM_DATA. Well, I ended up creating a table of two columns, as seen here:

    1. Content_type
    2. Payload
    CREATE TABLE JOTFORM_DATA 
        ( 
         CONTENT_TYPE VARCHAR2 (4000) , 
         PAYLOAD      VARCHAR2 (4000) 
        ) 
    ;
    Why VARCHAR2(4000)?  Two reasons. First, its just that it seems like VARCHAR2 is catch-all/the most flexible data type for testing like this. Secondly, since the PAYLOAD might be JSON or possibly malformed JSON, if I decide, I can always use an "IS JSON" check constraint. But that can come later.2

    Moving through the workflow

    With the Resource Handler code in place and the table defined, we can test this Webhook and see what it sends across HTTP.

    Next, a look at what my Autonomous database received:

    Okay! So now we have something to work with. And we know at least two things:

    1. The POST request is a multipart/form-data Content-Type, and3
    2. The payload we received appears to be JSON

    Next, we can copy the contents from the PAYLOAD column. And throw it into any number of online JSON Formatting tools, to see what we are working with:

    Final thoughts

    Well, I can already see why somebody might struggle with API integrations (in general, not just here). There is a lot to learn and A LOT of unknowns. Take this actual use case, for instance. Of course, you’d assume this payload to be multipart/form-data. You’d also expect the payload contents to be the JotForm form’s data. However, in this case, the form is pre-processed before it even reaches ORDS or the Autonomous database. So it is a good thing we did some investigation because there is way more “stuff” than we originally anticipated!

    Next, we’ll have to parse through the payload within the payload. For that, we can also use the ORDS Implicit Bind parameters, Automatic Binding, and JSON_VALUE functions. But we’ll save that for the next post. Stay tuned!

    Complete code

    Click for code

    You can find the most up-to-date code here. The code used in this post:

    -- Table DDL
    
    CREATE TABLE JOTFORM_DATA 
        ( 
         CONTENT_TYPE VARCHAR2 (4000) , 
         PAYLOAD      VARCHAR2 (4000) 
        ) 
    ;
    
    -- ORDS Resource Module PL/SQL
    
    BEGIN
      ORDS.ENABLE_SCHEMA(
          p_enabled             => TRUE,
          p_schema              => '[Your username/schema]',
          p_url_mapping_type    => 'BASE_PATH',
          p_url_mapping_pattern => '[same as p_schema in all lowercase]',
          p_auto_rest_auth      => FALSE);
        
      ORDS.DEFINE_MODULE(
          p_module_name    => 'ords_and_jotform',
          p_base_path      => '/oj/',
          p_items_per_page => 25,
          p_status         => 'PUBLISHED',
          p_comments       => NULL);
    
      ORDS.DEFINE_TEMPLATE(
          p_module_name    => 'ords_and_jotform',
          p_pattern        => 'jotform_data',
          p_priority       => 0,
          p_etag_type      => 'HASH',
          p_etag_query     => NULL,
          p_comments       => NULL);
    
      ORDS.DEFINE_HANDLER(
          p_module_name    => 'ords_and_jotform',
          p_pattern        => 'jotform_data',
          p_method         => 'POST',
          p_source_type    => 'plsql/block',
          p_items_per_page => 25,
          p_mimes_allowed  => NULL,
          p_comments       => NULL,
          p_source         => 
    'DECLARE
        incoming_payload CLOB;
    BEGIN
        incoming_payload := :body_json;
        INSERT INTO JOTFORM_DATA VALUES ( :content_type,
                                          incoming_payload );
    
    END;');
    
            
    COMMIT;
    
    END;

    Follow

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

    1. What is a Radio button? ↩︎
    2. Directly from the JSON Developer’s Guide Release 23, “You can store JSON data in Oracle Database using columns whose data types are JSON, VARCHAR2, CLOB, or BLOB.” Although, the guide then continues to state that, “Oracle recommends that you use JSON data type, which stores JSON data in a native binary format.” This is an easy change to make early on though. Should I choose to, I can adjust future table columns to accommodate the JSON data type. ↩︎
    3. About the multipart/form-data Content-Type. This section discusses the multipart/form-data Content-Type, but also ExampleBoundaryString why it is used/included. You’ll see that in some of the screenshots throughout. ↩︎

  • ORDS WARNING Cannot find the real static path of

    ORDS WARNING Cannot find the real static path of

    Problem description

    You’ve just upgraded ORDS and issued the ords serve command. As ORDS is starting up, you see the following warning (or something like it):

    WARNING Cannot find the real static path of https://static.oracle.com/cdn/23.2.0/

    How to fix

    Easy! In this case, I removed the standalone.static.path property of the ORDS Global configuration settings.

    Removing ORDS configuration settings

    You can remove/add/update ORDS configuration settings in two ways:

    1. Manipulating the Global settings.xml file directly, or
    2. Using the ORDS CLI
    NOTE: Using the ORDS CLI is the preferred method. I cannot stress this enough. 

    Whenever an ORDS installation or upgrade displays errors or warnings, go straight to the ORDS upgrade logs. Review the associated log for anything out of place and anything relevant to the warning or error you observed. In this case, the error pointed to two clues:

    1. APEX
    2. “static path”
    REMEMBER: When you are troublshooting, you are on a fact-finding mission. You're looking for clues that can help you solve a mystery. 

    Here, I see that APEX isn’t even installed in my target database. Perhaps Chris was trying to do something with/in APEX in the past and got distracted.

    Since it looks like I haven’t installed APEX, I feel comfortable ruling out APEX as the culprit.

    More than likely, this is pointing toward user error as the culprit. Perhaps it was some setting that I applied incorrectly. Next, I’ll inspect the Global configuration settings for ORDS. I can do this in two ways:

    1. A visual inspection of the settings.xml file
    2. Or using the ORDS CLI

    Visual inspection

    Immediately, you can see the offending configuration property: standalone.static.path. This property “specifies the path to the folder containing static resources required by APEX.”1 Since I am reasonably sure that APEX doesn’t exist, nor does any APEX metadata exist in my database, I can test my theory by removing the property from the settings.xml file.

    I can do this pretty quickly in a text editor. However, if you are skittish about manipulating these files directly (and rightfully so), then use the ORDS CLI (this is the preferred method anyway).

    Updating settings with the ORDS CLI

    In this case, you’ll see a fictitious standalone.static.path directory, /123456/abcdef.

    You can easily remove this, and other settings with the delete option.

    The command used in this example:

    ords config delete standalone.static.path

    Once you’ve made the change, close out your Terminal session and start a new one. Closing out your session and restarting with a new one is good practice to ensure that your Terminal session recognizes any configuration changes you’ve made.

    I next issued the ords serve command and ORDS started up with no problem.

    And that is how we fixed this warning in this oddly specific scenario. I hope this helped!

    And one more thing

    You may have noticed I’m on ORDS Version 24.4. That is because I was using a pre-release version that will be available when this post is published. So, if you haven’t done so yet, download the latest ORDS here.

    Follow

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

    1. See Table C-1 in the ORDS Installation and Configuration Guide. ↩︎