Want to publish some RESTful services using SQL Developer and Oracle REST Data Services (ORDS)?

You’re in luck!

View > REST Data Services > Development.

Before you can do this, a few things.

  • you’ll need to install and run ORDS
  • you’ll need to REST enable a schema
  • you’ll need to have created an ORDS user with the ‘SQL Developer’ role

When installing ORDS, you’re prompted to create two users, and ADMIN user for configuring ORDS, and a development user for publishing RESTful Service modules.

These are the users you'll be logging in with to admin ORDS and configure RESTful services.
These are the users you’ll be logging in with to admin ORDS and configure RESTful services.

You may have skipped this step. Oops.

Or maybe you need to create a new user.

If you try to login using an invalid user/connect, then you’ll get this:

Doh!
Doh!

So how are we connecting? If we look at the connection properties, we see this:

Details, details,...
Details, details,…

So I’m asking to connect over HTTP (if you picked secure/https – this won’t work), to server ‘localhost’ on port ‘8080’ using user ‘ords_dev’, and we’re doing ORDS RESTFul services (vs APEX) – so that’s ‘/ords’, and then our schema is ‘/peeps/’ – which is what I aliased HR to when I REST enabled it.

When ORDS gets a request and it sees ‘/ords’ – it switches to REST enabled schemas and schema objects and modules. Otherwise, it sees your request as an APEX thing. The bit that comes AFTER ‘/ords’ will be the schema name or schema alias.

So…either my user is setup wrong, or I forgot to set it up.

Jump to the box where ORDS is running. Find your .war file. If you do a ‘ps -ef | grep ords.war’ you should see where ORDS is running out of. Then you can create the user like so:

You're prompted for the password and you'll have to repeat it.
You’re prompted for the password and you’ll have to repeat it.

By the way, this is covered in the Docs, the Admin and Dev Guide to be specific.

Now we’re cooking with gas. Now I can connect and pull down and push up modules for my RESTful Services.

I really like this part, if you know SQL or PL/SQL, you can create a RESTful Service.
I really like this part, if you know SQL or PL/SQL, you can create a RESTful Service.

What if you’re not running ORDS in standalone mode?

If you’re running it in Glassfish or WebLogic, then you’ll still create a user – a Glassfish user – and give it the ‘SQL Developer’ role. So same difference basically.

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.

19 Comments

  1. Avatar

    Hi Jeff – I was trying to get the SQL Developer Rest Development window connection to work in https mode without success – you say above “So I’m asking to connect over HTTP (if you picked secure/https – this won’t work)”.

    Is there any configuration I can do to enable this connection via https? All my curl commands and tests work fine from the command line using https, so it looks like a wrinkle in SQL Developer that I can’t iron out.

    Would be nice to get this to work under https, if you can provide a fix I’d appreciate it.

    Cheers, Mark

    • thatjeffsmith

      There’s no real reason i can think of (assuming you have access to the database) to use this feature anymore. So, is there a reason you’re wanting to use this vs the Database connection tree and the REST Services section?

  2. Avatar
    Martín Zabala Reply

    Hello,

    My solution for this issue in weblogic is, add


    false

    in file config.xml, restart the domain and sucess connect from SQL Developer

    Regards

  3. Avatar

    Hi Jeff.
    When I installed ords in Standalone Mode,
    I specified the next lines in the parameter file:

    ords.sqldev.password=desarest
    ords.sqldev.username=desarest

    I’ve created and probed successfully a REST from a table, following the instructions on
    “https://docs.oracle.com/database/ords-17/QSORD/get-started-with-oracle-rest-data-services”.
    Then, I tried to reproduce the section in “Create a RESTful Service from a SQL Query section”, but I got Authentication Failed message (Invalid resource owner credentials) when attempted to Create RESTful Services Connection, with
    Connection Name: ordstest
    Username: desarest
    X http
    Hostname: localhost
    Port: 8080
    Server Path: /ords
    Squema/Workspace: ordstest

    When executing the command line:

    java -jar ords.war user desarest “SQL Developer”

    it shows: “You need a console to read user credentials” and it doesn’t create the credentiasl file.
    It seems like I cannot create developer user.
    Thanks

  4. Avatar

    Looks like the docs link has gone bust. Wish Oracle wouldn’t move the docs around.

    • thatjeffsmith

      I have the same dream Zach.

      I’ve updated the post to fix the Docs link, sorry for the trouble.

    • Avatar

      Thanks, Jeff! Thanks for the article (as always), too.

  5. Avatar

    Were you able to fix the issue of connecting to ords services in weblogic thro’ SQL developer? I am facing the same issue. I am getting HTTP/1.1 401 Unauthorized. Thanks

  6. Avatar
    Omar Sawalhah Reply

    Hi Jeff,
    I need to do the same thing GlassFish, can you help, and if it is a must to create the same user in ORDS as in the standalone setup?

    Omar

  7. Avatar

    Hello,
    I have a question. I want to run ords in (integrated) weblogic.
    How do I give a user the SQL Developer role in weblogic?
    Does this role exist?

    Thank you

    • thatjeffsmith

      that’s a weblogic question, but you just give it a role called ‘sql developer’ – ORDS will know what means

    • Avatar
      Dimitris

      when I’m doing so I get the following error
      “Cannot connect to weblogic.
      HTTP/1.1 401 Unauthorized”

    • Avatar
      Adam Niwczinski

      I am facing the same problem and can’t find a solution.
      I have created the user in ORDS (using: java -jar ords.war user test_developer “SQL Developer”) and then created a user with the same name in weblogic, created a group with the name “SQL Developer” and assigned it to the test_developer user, but I am still getting the “Cannot connect to weblogic.
      HTTP/1.1 401 Unauthorized”
      Help! 🙂

    • Avatar

      Were you able to fix the issue? I am facing the same issue and get HTTP/1.1 401 Unauthorized

    • Avatar
      Balachandran

      Exactly I faced the same issue and i resolved it. Go to Edit existing RESTful Services Connection > correct the last parameter – Schema/Workspace. It should work.

    • Avatar

      correct the last parameter to what value? what needs to be changed?

Reply To Zach Cancel Reply