Running Oracle REST Data Services Without Oracle Application Express

thatjeffsmith ORDS 23 Comments

Tell Others About This Story:

Can you?

YES. Absolutely, even.

No mention of APEX – not that that’s a bad thing.

Go from your app to your Oracle Database resources via REST (GET|POST|PUT|DELETE), and your responses come back via JSON. No APEX required.

And even better, you can have ORDS up and running in less than 5 minutes.

Ok, but what about ORDS & APEX?

If you want to provide a RESTful API for your Oracle Database, then ORDS is for you. But, when I do talks on ORDS, I ask the room, who in here is an APEX Developer?

The answer to this question very much determines who the audience comes to the talk in terms of expectations and experience with ORDS.

To run APEX, you need a ‘Web Listener’ – that is, you need a service that can handle the http(s) traffic to and from the database – which is where APEX runs. It runs in the database, because APEX is built with PL/SQL. That’s also why it’s so fast – PL/SQL in the database is awesome when you want to get to your Oracle data and ‘do stuff’ with it.

ORDS logo

GET, POST, PUT, DELETE your way in and out of Oracle Database.

ORDS used to be called the Oracle APEX Listener. This was an accurate, but incomplete description of what it offered. Because it does so much more than that, and of course it’s available to run WITHOUT APEX. Hence the branding/name changes a few years back to Oracle REST Data Services.

So how do you get from your browser or mobile app to the database?

You have a few options today, and ORDS is the recommended way to go – version 3.0.9 is currently available for your APEX and/or REST needs.

If you want to create and deploy RESTful services for your APEX app, then ORDS is DEFINITELY the way to go. When you install ORDS, it has a few questions regarding APEX, and it’ll also offer to migrate any RESTful Services you have defined in APEX over to ORDS.

So ORDS does have a ton to offer to the APEX developer other than just being the vehicle that gets you to the database where APEX is installed.

Tell Others About This Story:

Comments 23

  1. Hi Jeff, In an earlier post you said ORDS 3.0 and higher standalone is supported for production. We are trying to decide for our production environment if stand-alone mode is sufficient. I know lots of sources online in the past said standalone should not be used for production, but at this point in time in April 2018, can you address this in a little more detail or provide any additional resources to this question? We are concerned for the scalability and performance aspects as well as security requiring https with TLS. And wondering if say Tomcat was really required.

    1. thatjeffsmith Post
      Author
  2. Alex,
    Thanks for the quick response, much appreciated.
    I will give it a try and switch from standaone mode to Tomcat and see if that solves my problem.

    Not sure how long you’ve been using APEX on RDS, but how do you like it? I have set up a POC envoronment using RDS/APEX/ORDS 17 (on separate EC2), but have not used it for production yet. I have done several typical AWS Oracle/APEX implementations (i.e Oracle on EC2 not RDS) but I am exploring RDS & APEX now

    1. I’m on testing and exploration phase so far too 🙂 Actually, after my posts here I had to switch to other projects but now I’m going to switch back and see how it goes. Right now I’m developing a REST API using ORDS on local Oracle 12.2.
      Overall, I’m not a major fan of AWS or any other limited/crippled environment. Unfortunately, it doesn’t look like Oracle is planning to release a new Oracle XE 12, 18 or whatever…

      1. thatjeffsmith Post
        Author

        No, we are. New XE coming out later this year. It’ll be an 18c, which is just a 12.2.0.2 update.

        And our DBaaS un-managed service allows you to do pretty much anything you want on it, if you’re looking for a hosting provider.

  3. Alex,
    I understand the trick you made to have the ORDS schemas create successfully , but when you start ords.war, does in still ask you for sysdba username and pwd? I don’t need RESTful services in my case as I use APEX 4.2 on RDS, but I am trying to find a way to have ords.war not prompt me for sysdaba credentials 3 times each time I start ORDS (I am running it in standalone mode btw). Any hint?

    1. thatjeffsmith Post
      Author
      1. Yeap, this section is in the link from my first post. I followed the steps, but it looks like it fails to install correctly. After tweaking config files I managed to get APEX working, however, not the REST services. I installed my sample app and APEX REST sample app, but all the REST requests fail.
        I assume ORDS_METADATA and ORDS_PUBLIC_USER are required for ORDS to work properly. However, they are missing in my DB. And I can’t run “java -jar ords.war validate” to install’em and check structure because it asks specifically for SYS as SYSDBA password which is unavailable for RDS instances
        I tried a script in ords.war to install it manually, but it fails too…
        The next step I was thinking of is just export all necessary schemas including (ORDS_METADATA and ORDS_PUBLIC_USER) from my local DB, which works just fine, and import everything to RDS.
        By the way, I am very impressed by the way how you guys made it so accessible and easy to use. Now anyone can create REST API in just a few clicks and couple of lines of PL/SQL or SQL code.

      2. Just to answer my own question for those having the same issue.
        I managed to get APEX5.1 running with ORDS 17.4 on Amazon AWS RDS and EC2 for ords.war.

        After fiddling with ords.war installation you need to run @\apex\apex_rest_config.sql under user.
        It will still fail if ran unmodified, so I’ve changed a few lines of code in one of the dependent files apxpreins.sql
        1) there’s a line
        select cdb into l_cdb from v$database;
        replace with
        — select cdb into l_cdb from v$database;
        l_cdb:=’NO’;
        2) and a second line same file
        l_apex_schema := sys.dbms_registry.schema(‘APEX’);
        replace with, assuming you are running APEX 5.1
        — l_apex_schema := sys.dbms_registry.schema(‘APEX’);
        l_apex_schema := ‘APEX_050100’;

        That did the trick.

      3. thatjeffsmith Post
        Author
    2. Hi , I have a APEX5.1 running with ORDS 17.4 on Amazon AWS RDS and EC2 for ords.war. Same as yours. But can not use #WORKSPACE_IMAGES# in APEX applications. Do you have the same issue?

  4. Hi Jeff,

    could you please further describe the realtionship between apex ords sqldeveloper?
    I am going to move
    from old) Oracle-DB V10.2/Apex V4.2/ORDS V2 with REST-defined-in-Apex (of course)
    to new) Oracle-DB V11.2/Apex V5.1/ORDS V17.4 with REST-defined-in-ORDS (as you suggest)
    and I am using sqldeveloper V17.3

    The initial setup of the new stack is done without any application / REST-modules defined.
    (–> so at the time when ords is installed there is nothing that it could offer to be migrated.)
    Currently in old) sqldeveloper allows me to view and edit REST that is in APEX.
    Now when connecting to new) I assume the it would be possible to put REST into APEX in V5.1 also even if it
    is not suggested?
    How would sqldeveloper know where to put the REST definition? To ords or to apex?

    For the migration: old) allows to export the REST-definition.
    Where do I import the sql-File so that it goes to ords and not to apex in new)?

    Thank you
    Christian

    1. thatjeffsmith Post
      Author

      You had me up to here

      sqldeveloper allows me to view and edit REST that is in APEX.

      No, SQL Developer only shows you and allows you to dev/edit the RESTful Services in the ORDS Repository.

      When you install and configure ords for your DB, it will offer to migrate your RESTful Services from APEX over into ORDS. Have you tried this?

      1. no, the new) stack is not yet installed completely.

        But I tried on another system: DB = 12c, apex = 512, ords = 309, an apex-app running,
        no rest so far (in the apex ide / sql-workshop no module is shown)

        I created an admin-account in ords (java -jar ords.war user ordsdev “SQL Developer”).
        Then I was able to connect from sqldeveloper/REST-Development. It showed an empty list – no surprise.
        Now in sqldeveloper I created a module with a GET (select sysdate from dual) , validated and did the upload.
        And now this moduled is shown in APEX-IDE’s SQL-workshop/Restful-services. Surprise here.

        Now I wonder if my rest-module is defined in apex or in ords and how I can control/determine this.
        Or does the apex-ide act as another dev-tool for the ords-based rest?

      2. thatjeffsmith Post
        Author
  5. Hi, I was playing around with ORDS and creating templates/handlers etc via the ORDS package (which I’ve done successfully), i.e. not as RESTful Services within Apex, these are completely standalone.

    I’m then trying to protect them with OAUTH2 using the OAUTH package and again fine and works well but only if I use the unsupported for production use file-system user repository, i.e. via $ java -jar ords.war user .

    What I’m trying to work out is if I can configure ORDS to validate the entered user credentials (in authorization or implicit flows) against either an Apex user repository (again, via the ORDS / OAUTH packages only) or indeed via a custom “users” table?

    Do you know if this is possible?

    Regards

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *