Mostly Making oracle easy, mostly:
Search for:
In ORDS

Everything you need 2 know about ORDS On Oracle Autonomous

3 Mins Read

Updated 2 May 2022

Oracle Autonomous Database is a Platform as a Service (PaaS) offering on the Oracle Cloud (OCI). It’s managed, meaning you don’t have to worry about updates, patches, or much in way of configuration.

One of the core features is the ability to publish and consume REST APIs for the data in your Autonomous Database – whether that be on the Dedicated, Serverless, or Always Free service offerings.

I wanted to answer a few frequently asked questions and share a couple of useful resources for you in this post.

How are my endpoints addressed?

All database REST APIs in Autonomous use the HTTPS protocol. Each HTTPS call generally has two main components, an address and a verb.

Well, if you publish a REST API in Autonomous, what the heck is my URL? We show you this critical information in your Database Actions REST workshop page.

Your Database Actions base URL is also your base URI for your REST APIs.

So what comes after the /ords/ ?

Your REST APIs will look like this –

  • /schema/module/service-pattern
  • /schema/rest-enabled-object

The schema is the REST Enabled schema name, or it’s schema alias, which we refer to as the url_mapping_pattern.

What else do I need to know about ORDS in Autonomous?

OS Authentication is available if you want it. But note that means consumers of your API will be logging in as actual Oracle Database users, and you’ll be using BASIC Auth, which is generally frowned upon, even when HTTPS is in play.

We recommend you use an ORDS provided OAUTH2 client. Hot Tip #1 – use Todd’s awesome blog post to see how to do this.

Not only is OAUTH2 more secure, it’s ALSO faster. Each every call you make will be significantly faster, maybe even 3x faster.

From 831ms to 281ms just switching from Basic to OAUTH2

DO NOT USE ADMIN FOR PUBLISHING REST APIs

WE DO RECOMMEND YOU CREATE AN APPLICATION SCHEMA AND USE THAT.

Building Services – with what tools?

You can use anything that can connect to your database and exercise the ORDS PL/SQL APIs. We recommend you either use SQL Developer on your desktop or stay in your browser and simply use Oracle SQL Developer Web..

REST Development in SQL Developer (Desktop)

The preferred interface is SQL Developer Web, also known as Database Actions.

Modules, templates, handlers, parameters, documentation for your RESTful Web Services.

We also make using the Oauth2 workflows easy to setup and use!

Just give me a movie to watch, TL;DR

I have a 12 minute video demonstration showing a REST service in Autonmous that you can watch here.

Fairly short…and I’ve skipped all the blah blah blah and go STRAIGHT to the demo!

Can’t do YouTube at work? Here are some slides.

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.

Related Posts

4 Comments

  1. Raul Castro Reply

    Hi Jeff, I am trying to install ORDS on a Compute Instance on OCI, I am following the documentation:
    https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-and-configuring-customer-managed-ords-autonomous-database.html#GUID-D90EFD26-AC36-4907-BF95-3E05E82BC3E0

    I have tested connection from sqlcli to the autonomous database an is succesfull….

    I’m kidda stuck on the part where the ords installation asks me for the administrator username:

    —————————————————————————————————–
    Enter the location to store configuration data: config
    Specify the database connection type to use.
    Enter number for [1] Basic [2] TNS [3] Custom URL [1]:2
    Enter the TNS Network Alias:grupokatp_low
    Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
    Enter the database password for ORDS_PUBLIC_USER:
    Confirm password:
    Requires to login with administrator privileges to verify Oracle REST Data Services schema.

    Enter the administrator username:admin
    Enter the database password for admin:
    Confirm password:
    Connecting to database user: admin url: jdbc:oracle:thin:@grupokatp_low

    Retrieving information.
    java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
    ORA-06512: at line 81
    —————————————————————————————————

    Can you lead the way please

    Thanks

    • thatjeffsmith Post Author

      You won’t actually do AN install or ORDS against autonomous – because ORDS is already installed there. Also, ORDS_PUBLIC_USER is already there, and you CANNOT have/know it’s password.

      You’re just going to stand up the local ORDS config files, and have a pool configured to use your own ORDS_PUBLIC_USER account, aka ORDS_PUBLIC_USER2, to create your JDBC Connection pool to Autonomous.

      Here’s a great blog post on the subject that goes into pretty good detail
      https://fuzziebrain.com/content/id/2004/

  2. Sorin Reply

    Hi,

    When I am trying to use some nested json response, with a SELECT like:

    select col1, col2
    cursor(select col3, col4
    from t2 where t2.col1 = t1.col1
    ) as ArtInfo
    from t1

    It does not return anything, but if I run the select statement standalone, it returns data.
    Do you know why when I put this select statement in p_source from ords.define_service, it does not return anything? It’s like it is trying to retrieve data, without end … 2 minutes passed and nothing.
    I want col3 and col4 to be displayed as a nested item of col1 and col2 (which are kind o “header” data)

    Thanks!

    • Raul Castro

      Thanks Jeff!

      The Post you refered worked!

      Thanks!!! An Cheers!

Write A Comment