ORDS, SQL Developer and SQLcl Version 18.1 Released : Plus Some ORDS Documentation Comments

If you’re active in the Twitter-verse you will have seen a bunch of tweets yesterday about the release of the 18.1.x versions of Oracle REST Data Services (ORDS), SQL Developer (and Data Modeler) and SQLcl.

The first thing I did was edit my ORDS Docker build to use the latest versions of ORDS, SQLcl, Tomcat9 and Java9. If you are interested in playing with that you can find the build on my GitHub here. It was all smooth sailing! 🙂

There are a couple of things I would like to point out about the ORDS 18.1 documentation.

ORDS Installation in Multitenant

The way I read it, the documentation suggests there are two ways of installing ORDS in a multitentant environment, both of which involve installing it into the CDB.

Multitenant is an architecture, so it doesn’t imply the presence of the Multitenant option, or multiple PDBs in the CDB. In fact, I would suggest the vast majority of CDBs will only ever contain a single PDB, as most instances will eventually switch to Lone-PDB (for free) now that non-CDB is deprecated. So what does this have to do with the installations described above?

The advantage of installing ORDS in the CDB is you can have a single connection pool for all PDBs in the instance, which is a big advantage if you have 4096 PDBs in your CDB. If you only have one PDB per CDB, there is no advantage, and actually there is a disadvantage because the PDB has yet another dependency on the CDB, which means ORDS must be installed in the other CDBs before you clone/relocate a PDB to them.

In my opinion, the best way to install ORDS for Lone-PDB, and possibly even for small numbers of PDBs in one instance, is to install directly into the PDB, just like we do with APEX. This means each PDB needs its own connection pool in ORDS, but that’s not a problem. This way the PDB doesn’t have an external ORDS dependency on the container and can be moved between containers without any fuss.

You will see my ORDS installation article installs directly into the PDB, although I will be amending it with some comments about other options.

Database Authentication for PL/SQL Gateway Calls

One of the new features of ORDS 18.1 is you can now use database authentication to provide basic authentication for your calls to PL/SQL. You won’t see that in the documentation though. It’s only present in the examples under the “/path/to/ords/examples/db_auth” directory when you unzip the ORDS media.

Using this type of authentication is not advisable, it’s much better to use OAuth2, but for people like me that have a lot of applications and XML web services still using mod_plsql, this is a really handy feature, and certainly eases the transition from mod_plsql to ORDS.

It would be nice if this feature were put into the main documentation, as it will be a welcome addition for many people out there. It is a feature, so it should be documented as such, and anything that improves the uptake of ORDS has got to be a good thing.

How’s about including the document upload/download functionality from mod_plsql in ORDS also? That would help with the transition too. Yes, I know it’s easy to code this yourself, but that is still an application change, rather than just switching from one gateway to another. Just sayin’. 🙂

I’ll be playing around with ORDS 18 over the coming weeks. I’ll probably amend some of my existing ORDS articles as a result of that, and no doubt put out a new article on database authentication, for the lost souls that don’t look at the media examples, like me before Jeff told me to. 🙂

Cheers

Tim…

SQL Developer 4.0

Oracle have given birth to SQL Developer 4.0 today, as announced by Jeff Smith.

I had access to the first Early Adopter a few weeks before it hit the OTN download page, so it feels like I’ve been using some flavour of SQL Developer 4 for ages. I’m kinda old-school, so I still find myself working with a text editor (UltraEdit) and SQL*Plus a lot, but I’m trying to use SQL Developer more these days. The addition of the Performance Reports (AWR, ADDM and ASH) was certainly a nice touch.

Cheers

Tim…

Performance Reports in SQL Developer 4 EA2

SQL Developer 4 EA2 includes a performance node in the DBA tree.

sqldev-performance

 

You can use this to view ADDM, AWR and ASH reports directly from SQL Developer. I know I can get these from Cloud Control, but previously I tended to pull these out from the command line on the server. This is a much better approach IMHO.

I found out about this stuff at OOW2013, whilst talking to Jeff Smith and Barry McGillin in the demo grounds. I tweeted some pictures at the time, but figured it was worth a post for those that don’t do Twitter. 🙂

Cheers

Tim…

PS. Yes, it is only going to be of use to people connecting to Enterprise Edition databases with the Diagnostics & Tuning Pack.

SQL Developer 4 EA2 Connecting to SQL Server

I wrote a blog post some time ago about using SQL Developer 3.x to connect to SQL Server. I tried the same thing today from SQL Developer 4 EA2 and it works just fine.

You have to download the 3rd party JDBC driver and point SQL Developer to it. You can read how to do it here.

Cheers

Tim…

Update: My colleague just told me this.

“If you at any point decide to use Windows authentication to connect (like I just did), you might hit this error:

Status : Failure -I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

…found a work around, which involves copying the ‘jtds-1.2\x86 (or X64)\SSO\ntlmauth.dll’ from the JTDS directory to the JDK’s \jdk\jre\bin folder. Then I restarted SQL Developer and it worked J”

SQL Developer 3.1 : Scheduler Support…

I had a play around with the scheduler support in SQL Developer 3.1 today. I’m late to the party because most of it has been there since 3.0, but what the hell.

It’s more of an opinion piece, so it started as a blog post, but it got too big. Not really what I usually put on the website, but I figured if I put it on the blog it would get in the way of the movie and book reviews and that simply wouldn’t do… 🙂

I am still worried about feature creep turning SQL Developer into the new TOAD, but so far so good. I’m liking it more and more with each release.

Cheers

Tim…

SQL Developer 3.1 : Data Pump Wizards…

One of the things that sounded kinda neat in SQL Developer 3.1 was the Data Pump Wizards, so I thought I would have a play with them.

As you would expect, they are pretty straight forward. They can’t do everything you can do with expdp and impdp, but they are pretty cool for on-the-fly tasks.

You can use the wizard to generate data pump definitions using the PL/SQL API. It would have been a nice touch if it gave you the option to see a regular command line or parameter file definition also, since I would be more likely to put that into source control than the API definition of a job. Even so, a nice little feature.

Cheers

Tim…

SQL Developer and MS SQL Server…

This afternoon I’ve been cleaning up some data in an SQL Server database. I decided to use SQL*Developer to connect to SQL Server by following this post.

I made liberal use of the following tip when dealing with TEXT and NTEXT types.

The joys of dealing with multiple engines…

Cheers

Tim…