Your ORDS questions answered: Oracle APEX Office Hours

What should all APEX developers know about ORDS? I spent about an hour discussing that with 500 of my closest friends on a Zoom this week.

It’s hard to take questions from people as you present, so I punted mostly to answering those ‘offline.’

Here follow the questions and comments, mostly. I’ve re-worded them to more closely match what someone might Google.

But first, because you always ask this first…

The Slides?

Data Pump inside of Database Actions, how do we use that?

Currently it’s for 3 major use cases:

1. Getting a list of data pump jobs and their current run statuses
2. Viewing the job logs
3. Downloading the DMP file(s)

So for now, you actually start the jobs with say our DB API REST endpoints or with SQLcl…or calling the DBMS_DATAPUMP pl/sql API or using the Data Pump CLI.

Later this Summer, you’ll be able to run both Import and Export jobs from here. We’ll have a guide set of screens where you pick what you want, and we’ll schedule the job for you.

How can we automate building OAuth(2) clients for individual groups/users/partners?

I would use the OAUTH package in the ORDS_METADATA schema. There you could create scripts with SQLcl to batch automate creating OAuth2 clients in any schema, and assign whatever privs/modules are required.

22.2 Preview: We now have a PL/SQL proc for rotating OAUTH2 client secrets.

Tell us more about connection pools!

Ok. Your pools probably aren’t properly sized. The default is 10 connections. Your code is probably not fast enough, and/or you probably have too many ‘users’ to run your apps in production with that number of connections.

There is help available!

• Whitepaper: Real World Perf Sizing Guide
• Video: Office Hours Session

Is SQL DEV WEB available on always free tier?

Absolutely. Any of the SQL Developer Web blog posts I’ve written here are either with ORDS running on my laptop or using my Always Free Autonomous Transaction Processing Oracle Database.

We’re working to make SQLDev Web available, in a free & managed service for any/all Oracle Database instances in the Oracle Cloud…hopefully this Fall!

“how about sql injection with these bind variables?”

Not a problem. The question is regarding this slide –

I’m not saying it’s 100% impossible, but the feature utilizes Oracle Binds for many reasons, including it’s ability to sanitize SQL Injection attempts. There’s no code ‘injected’ or appended into a string. Our SQL Advocate Chris Saxon of AskTom fame has a nice story/demo around this technique.

Can we use Swagger UI with ORDS?

Yes! You can do the following:

• generate OpenAPI client for docs/testing your APIs
• import OpenAPI v3 or Swagger v2 JSON to create new APIs

Are there any REST APIs for database admin, e.g. creating users?

Yes! And, no 🙁

So yes we have 600+ endpoints for managing your database, and there are endpoints around users. But we don’t have a POST or PUT to create/edit database users.

But we DO have REST Enabled SQL.

Will AutoRest Support Oracle Text queries?

I’m guessing you’re asking if you can use a $contains query parameter on an Oracle Text indexed column, for a table that’s REST Enabled?

I don’t know. I need to research this and give it a try.

How do you prevent SQL injection?

Code reviews, use bind variables, scan your code for vulnerabilities.

Thankfully we have tools for helping with that.

Does the DB API require ORDS to be installed in the database?

Yes. You’ll enable ORDS for the DB API. The ORDS_METADATA schema will be installed along with the ORDS_PUBLIC_USER user in your database. You’ll need a REST Enabled SCHEMA to access the APIs.

More demos, how-to’s here.

Will there be support for MySQL?

In the Oracle Cloud at least, yes. And that’s new as of this week. See Brian’s post.

Effectively today it’s limited to REST Enabled SQL access on a MySQL instance running in the Oracle Cloud.

Yes, APEX could take advantage of that. Stay tuned.

Can you discuss securing Web Sql Developer / REST Services in general?

Get in the habit of thinking about security on day 1. When you build a new module or REST Enabled object, immediately assign a required privilege. Don’t rely on the ‘SQL Developer’ role. OAuth2 will be much faster than DB Auth.

Always HTTPS over HTTP.

There are other ways, things to worry about, but that’s like an entire whitepaper, not a Q&A topic.

Is the combination of ORDS 22.1 and APEX 22.1 a certified configuration? MOS note does not indicate that it is.

ORDS is certified for any version of the Database and/or APEX that’s available and supported by Oracle at the time of it’s release. If the MOS note isn’t up to date, that only means the MOS note needs updated.

ORDS 22.1 is being used to run APEX 22 in the Oracle Cloud right now.

Are there any features that you’d recommend be disabled from a security perspective if the ORDS/APEX instance is public facing, e.g. SQL Developer Web?

Nope. None are really more dangerous than others. If you’re not sure your web tier is secured, don’t enable ANYTHING on the public internet.

I would be pretty paranoid for any environment with the DB API and/or REST Enabled SQL enabled…you can literally do anything in your database with those features, assuming you’ve got the proper privs.

Please comment on support for ORDS with Oracle FMW 12.2.1.4. ORDS 22.1 only supports Oracle FMW 14.1.

ORDS now needs Java 11. If you’re in a world that doesn’t support Java 11 or 17, you’ll need to stand up ORDS somewhere it IS available. That might mean a separate WLS server, or having ORDS running in a docker, or, or, or.

Does SQL Web Developer have capabilities to load data

Yes. Holy moly, yes.

Here’s one example.

Here’s another example (importing JSON).

GraphQL planned ?

Feature request for @OracleREST. The ability to identify (in a header) which columns you want returned from a GET Handler based on a SQL statement. A bit like GraphQL. Reduces the payload size for requests when you don’t need all the columns.

— Jonathan Dixon (@JonDixonUS) June 3, 2022

I put it on the 2022 roadmap. No promises.

Automatic monitoring and instrumentation for HTTP calls?

That’s your webserver’s job. And if your webserver is ORDS (with embedded Jetty), then yeah you can enable HTTP logging. And then use whatever monitoring solution you want.

ORDS doesn’t give you monitoring…but the Oracle Cloud does. And you could ship your logs to the appropriate service for fancy dashboards, alerting, etc.

autoscaling and pool params

Not really a question, but I’ll try. The pools are defined with a starting level, min, and max. So the pools CAN automatically grow to meet demand.

Does Autonomous Auto-Scaling auto-adjust your ORDS pools as it adds oCPUs to your database? No, not yet.

That HA-doc, is it geared towards on-prem? Or is it the doc about the cloud? If the former it would be very interesting to read and compare with what we’re running with.

It’s ‘geared’ towards Oracle Cloud, but it’s 100% compatible and relevant for an on-premises deployment. Questions can go to Brian. If you want to give it a look-see…here it is.

Any plans to support ORDS with https on AIX? Thanks

As soon as IBM supports Oracle Java, you should be good. I don’t see that happening though, so just run ORDS literally anywhere else.

Is there a way to have multiple application alias  for the same app ?

You can have multiple routing patterns for the same connection pool in the url-mapping.xml config file (Docs.)

MFA Support for SQL Developer Web? I wouldn’t want to open that to the internet without MFA….

We don’t have MFA support for any of our tools, yet. We’re working on getting Multi-Factor Auth going for SQL Developer Desktop, first. The JDBC driver has added support for it as of 23c, but we’ve got them to give us a 21c driver that supports it.

Stay tuned. It’ll get here, eventually.

Is there a plan for ORDS to connect directly to external rest endpoints, not through the database (acl, etc.)?

ORDS connection pools are UCP/JDBC, we’ll be needing direct access to the database for the considerable future.

Is there any feature to manage ACLs? (many acls must be defined for external endpoints)

No, but that’s a great idea for SQL Developer Web.

I understand that ords was created primarily for easy access to the database, but support for administering external non-oracle database endpoints is not clear where?

If it’s an Oracle or MySQL Database (or even TimesTen/NetSuite) database, then we care. If it’s something else, that’s not really our job or purview to worry about.

Hello, what about versioning the different API’s, is there a possibility to do that from apex?

You can version your REST APIs, the easiest way to do that is via the module, so have a module and base URI template of say, payroll /payroll/v1, payroll2 /payroll/v2, …

How do you talk DBAs with security concerns to install Apex with ORDS?

Most DBAs already have a great handle on security around the database. If they have concerns, start with explaining to them what APEX is, and how it works. APEX starts out life as PL/SQL programs. They’re comfortable with that.

Any reason why SQL Developer Web is not part of APEX automatically? Why are they separate tools? I would love to see SQL Developer web part of APEX

We’re working now to have links for SQL Developer Web from APEX, or any authenticated user in an APEX development runtime.

Is the any REST modules created here, are visible inside APEX REST services screen as well? I means is it centralized?

REST Modules belong to ORDS, in the ORDS_METADATA repository. APEX has a front-end built on top of this, but it was done several years ago. Our recommendation for the best experience is to use SQL Developer Web if you want a GUI for working with ORDS REST APIs.

When I Register my Schema for ORDS on APEX it and all apex images (including CSS style) were removed.

Yeah, I’m not sure what this means. That sounds like it could mean you’re setting up an APEX Workspace or simply REST Enabling a schema…you asked anonymously, so if you see this again, please leave a comment with a better description.

Can you talk about ORDS cache capabilities in general?

I could, but Kris is so much better at it.

Unfortunately on the Autonomous DB with a small connection pool

Actually…the smallest number of connections you’d have is 300, and as many as..1200 I think. Unless you’re talking about the Always Free Tenancies, and then yes, you’re limited to the number of connections overall there, not just the ORDS connection pools.

And if you don’t like our mid-tier setup, you can run your own 🙂

“Just sayin’ we’ve been using 3.0.4 for about 7 years now and its FAN-tastic for most of these features. catching up to the latest version soon”

I’m almost equally impressed as I am horrified. But I’m glad to hear you’ve had great success, and yes yes yes – UPGRADE. ORDS will be more secure and faster.