I was helping a colleague this morning, his name rhymes with Harold, and he was trying to get his Docker template for ORDS in order.

He had a series of questions, and while I was able to help him understand what was happening fairly quickly, I realized HIS questions looked an awful lot like some of YOUR questions.

So let’s go through them, and maybe this can help, and MAYBE we can try to incorporate some of this into the future ORDS Docs.

ORA-06598: insufficient INHERIT PRIVILEGES privilege

You are trying to REST Enable SYS a la

ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'SYS'
...

This won’t work. It’s not possible to grant proxy connect to any Oracle account for the SYS schema. That’s one of the main things this call does, it allows the ORDS_PUBLIC_USER to do database work (your REST service call) AS the USER where the REST Service is defined.

Also, it would be extremely hazardous to REST Enable the SYS account. Your REST Service, if not coded and secured PERFECTLY, could be used to do very bad things, like…DROP a database.

404…The request could not be mapped to any database

The complete error you would see in the ORDS stack is

 The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured.

So when ORDS gets routed a request, it needs to ‘unwind’ the URL to see what exactly is being asked of it.

My friend was doing something similar to this –

/ords/orcl/hr/metadata-catalog — where ‘orcl’ is the DB Name.

The problem here is ORDS is saying, I can’t unwind that request on ‘/ords/orcl/hr/metadata-catalog’ to anything I can understand.

Here is one possible way to look at the URI on a HTTP request to ORDS:

This ONE possible scenario.

If you have ORDS installed into your PDB (recommended), and that ORDS install is only servicing a single database, then the very first thing expected after the ords/ will be an alias for a REST enabled schema.

My friend was putting the DB Name after the /ords, hence that error message. You would only need to do that where you have multiple databases going for your ORDS configuration. In that scenario, you would follow the /ords with a /dbname.

Now, I would expect most folks would default that mapping pattern to the actual name of their database, but you can call it whatever you’d like. ORACLE-BASE has a nice set of instructions here.

I just lied a little bit there…you also need the db name after the /ords if you did a CDB (container) level install of ORDS, but that’s probably going to be much less common.

404 Not Found

Just the ORDS and the REST Enabled Schema

In this case, ORDS knows what you want, but is saying there’s nothing there. You have a valid REST Enabled schema (test), but that’s it.

You need to ask for something IN ‘test’ – a REST enabled schema object, or a RESTful Service. If you get this message, ORDS is working, it’s just that you are requesting something that doesn’t exist.

By the way, if you have APEX configured, if you do a request similar to this, ORDS will redirect you to the APEX login vs giving you a 404.

So, how do I test if ORDS is ‘working?’

After REST enabling a schema, the easiest way to test ORDS is with the /metadata-catalog endpoint.

/ords/hr/metadata-catalog

This brings back the inventory of REST Services in your REST enabled schema – in this case, HR.

But, then my friend ran into the NEXT question/problem.

401 Unauthorized

But, but, but… 🙁

This is a PROTECTED resource IF you do this when you REST enable your schema –

BEGIN
 
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => TRUE); -- TRUE means protect the catalog!
    COMMIT;
END;

THIS = setting P_AUTO_REST_AUTH to ‘TRUE’. This ONLY protects the /metadata-catalog endpoint.

So, if I login/provide the proper credentials on the request…

I have DB Auth enabled on this ORDS config, so I can login as HR.

Now if you have JUST done an install and REST enabled a schema and done NOTHING else, this call will work, but you’ll get an empty/null JSON collection back, because there’s nothing in the REST Services Catalog for that schema.

What do those funny Timestamp | asldfjalskdjfa messages mean on Error responses?

They are a STAMP that you can use to find the associated information in the ORDS standard out/Tomcat/WLS logs.

For example, let’s say I get a 500. Those logs can be HUGE. If I want to pinpoint where in the stack dump that is happening in the ORDS logs…

EZ-PZ. Colm added this in 2019, I think in ORDS 19.2.

I’m sure you have more questions…you know what to do!

In case you don’t know, search in Google. And then if you can’t fine a good answer, I’m happy to take those here 🙂

thatjeffsmith
Author

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

14 Comments

  1. Avatar

    Thank you, Jeff, for your help. When I tried option 2. It’s overwriting my original configuration with new DB information.

    For example.
    ORDS is already configured and pointing to dev DB with the URL: http://abc.com/ords. – Which is working fine.
    After setting up new config for QA with the following commands

    [ord]$ java -jar ords.jar setup -database qa [ GIVEN QA DB INFORMATION, INSTALL SUCCESSFULLY, NO ERRORS]
    [ord]$ java -jar ords.war map-url –type base-path /qa qa

    My original url http://abc.com/ords is now pointing to QA DB not DEV DB and it is overwriting the all config files pointing to QA not DEV anymore.

    And when I tried the url http://abc.com/ords/qa/f?p=4550 it is giving 404 page error “DispatcherNotFoundException [statusCode=404, reasons=[]]”

    Please guide me If I am doing something wrong.

    Regards,
    Rose.

    • thatjeffsmith

      No, that’s not right, you should have /ords going to load apex on your original database pool, and you should have /ords/qa going to APEX on your second database

    • Avatar

      Thank you Jeff, that is the strange thing I am facing. Any suggestion, how to debug this issue. I am not sure why it is overwriting my original configuration instead of mapping the new DB connection.

      I appreciate your help.

      Regards,
      Rose.

  2. Avatar

    Hi Jeff,

    I’m putting together a new ORDS Service which is working great using OAUTH2 as the authorisation scheme. Currently the URI endpoints to access the resources include the schema name of the user that installed the ORDS modules. In your article you make reference to the ORDS Request URI and in particular:

    hr – schema (alias!), service handler code runs as this user

    I’m struggling to see where I can set this alias. I need to achieve this via a script or config file edit as it’s for a deployment to a client. I have a schema of mprs_ws and want to use mprs-ws in the URI.

    Please can you point me in the right direction.

    Thanks

    • thatjeffsmith

      Yeah, just run the ODRS package and the ENABLE_SCHEMA procedure. The default behavior is for the schema name to be the alias.

      BEGIN

      ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
      p_schema => 'HR',
      p_url_mapping_type => 'BASE_PATH',
      p_url_mapping_pattern => 'not_hr',
      p_auto_rest_auth => FALSE);

      commit;

      END;

    • Avatar
      Ian Young

      I’ve tried using the following:

      ORDS.ENABLE_SCHEMA(p_enabled=>TRUE,
      p_url_mapping_type => ‘BASE_PATH’,
      p_url_mapping_pattern => ‘mprs-ws’);

      which is called from the MPRS_WS user but the following URL still fails with the following error:
      DispatcherNotFoundException [statusCode=404, logLevel=FINER, reasons=[]]
      With the schema left as the default mprs_ws all seems to work fine.

    • Avatar
      Ian Young

      Apologies URL is:

      http://mprsapp:9201/ords/mprs-ws/dubs/v1/address

      USER_ORDS_SERVICES shows:
      BASE_PATH PATTERN STATUS SOURCE_TYPE
      /dubs/v1/ address/ PUBLISHED plsql/block

      USER_ORDS_SCHEMAS:
      PARSING_SCHEMA TYPE PATTERN STATUS
      MPRS_WS BASE_PATH mprs-ws ENABLED

      url_mappings:

      Just can’t see why this isn’t working!

  3. Avatar

    The problem of “boring Java library loading” on 19.2 does not occur?

    Is there a patch on metalink on ORDS 19.2?

    • thatjeffsmith

      ‘boring Java library loading’ – I’m not aware of that issue for ORDS

      There’s a newer version of ORDS than 19.2 – there’s 19.4

      We are working on a patch for 19.4 though, it’ll be avail soon with a bug fix around a 500 issue popping up on the forums.

    • Avatar

      Hello Jeff,

      I am new to ORDS. I am trying to configure ORDS 3x with multiple 11g DB in a Tomcat . When I read document, you can achieve with multiple options.

      Option 1:
      You can simply copy the war file and do settup the war for each DB with different configuration dir, which I am able do. For example, abc.com/dev or abc.com/qa etc.,

      Option 2:
      Other approache is with one war file you can map the url to different db connection, which I am not succeed yet. Please point me for any good documentation. For example abc.com/ORDS/dev or abc.com)ORDS/qa .

      Which option is industry standard. If you can please let me know the pros and cons of each approaches.

      I appreciate your help.

      Regards,
      Rose.

    • thatjeffsmith

      having one ords to maintain (option 2) would be less work, less to maintain, less server resources. Do your setup BEFORE you copy the war file to Tomcat. Get it working in standalone mode, and then move it over to Tomcat.

  4. Avatar
    Geert De Paep Reply

    Thanks for this info, but still for me it remains very difficult to troubleshoot issues when it is not working. E.g. is there an easy way to identify that you run into the issue described in Doc 2488390.1? Is there a way to have more significant logging in catalina.log instead of 1000 lines of java stack trace that usually don’t help a lot? A the moment I have an apex setup where an image will not load using #APP_IMAGES#/someimage.gif, but again, how to easily troubleshoot this?
    If you see a chance to add a debug option that can clearly say what ords is doing exactly and where to look in case of issues, I would really appreciate this.
    Thanks and regards. Note that I remain a big fan of Apex and ORDS!

    • thatjeffsmith

      That one’s pretty simple, install APEX before you tell ORDS you want to configure it for APEX. If you run into this, run the validate command via ords (java -jar ords.war validate)

      You say a 1,000 lines of java stack trace that don’t help a lot – well, they generally have everything we need to know to figure out what’s going on. It’s a java app, so you get a java stack. Having more meaningful errors is important, and this is also why we just started adding the fingerprint info to the error dialogs so you could jump straight to the right place in that 1,000 line error stack. There already is a debug option for ORDS – it prints the full error stack (in the browser response) if you have print debug to screen also enabled

Write A Comment