Video : APEXExport : Export APEX Applications and Workspaces From the Command Line

In today’s video we’ll give a quick demonstration of using the APEX command-line export utility.

The video is based on this article, which includes more examples, and Windows-based examples also.

The star of today’s video is my daughter Heli “Hell-Squirel” Helskyaho. Make sure you check out the cloud forming a Pikachu tail above her head! 🙂

Cheers

Tim…

Video : Resource Manager : SQL Quarantine in Oracle Database 19c Onward

In today’s video we give a quick demonstration of using the SQL Quarantine features of Oracle Database 19c.

It’s a follow-on to last week’s video.

Today’s video is a cut down of this article.

These might come in handy too.

The star of today’s video is Martin Widlake. I put out a message to “The Family” as I had run out of “.com” clips, and Martin was the first one back. He sent three, but this one had me laughing, and suited the quarantine theme. 🙂

Cheers

Tim…

PS. If you want to send me a clip with you saying “.com”, you know where I am.

Vagrant and Docker Builds : ORDS 20.2 and SQLcl 20.2 Updates

The recent Oracle REST Data Services (ORDS) 20.2 release prompted my usual reaction. I’ve gone through my Vagrant and Docker builds, and updated them to use ORDS 20.2 and SQLcl 20.2.

The Vagrant database builds, which include ORDS, can be found here.

The Docker ORDS builds can be found here.

There were also some small Tomcat mods.

  • Tomcat upgraded to 9.0.37.
  • HTTP/2 enabled.
  • Compression enabled.
  • Cache-Control enabled for images, CSS and Javascript.

All that went pretty well so as soon as I got to work yesterday I rolled ORDS 20.2 to all non-production environments, and a few “not yet production” environments. If you follow the blog you will know we use Docker for ORDS (similar to my Github builds). It makes rolling out a new version really simple. Just throw away all the containers and replace them with the spangly new ones.

If it’s looking OK after a few days we’ll push it out to the remaining production installations.

Cheers

Tim…

Video : Resource Manager : Runaway Query Management

In today’s video we give a quick demonstration of using Resource Manager to cancel a long running SQL statement.

This video is based on a series of articles from Oracle 8i to the present day. There is an example here.

Although there are aspects of it throughout most of the Resource Manager articles on the website.

The horror show was brought to you by Jeff Smith. Notice his proximity to assorted torture devices he probably describes as “tools” when questioned by the authorities.

Cheers

Tim…

When Implicit Date Conversions Attack

Yesterday, one of the developers was having a problem and emailed to ask what was going on. They sent me a section of code from an old trigger that included some date handling that looked “interesting”.

TO_DATE(SYSDATE,'DD/MON/RRRR')

Some bright spark had decided this was the best way to trim the time component off a date, and unfortunately for us it worked for a very, very long time. Many years in fact.

Why was this causing a problem now? They were trying to do an update of a table that caused this to trigger to fire. The update was working fine from SQLcl, but failed when it was called from APEX, giving this error.

ORA-01843: not a valid month

Let’s take a look at what was happening here.

The SYSDATE function returns a date. The TO_DATE function expects a string as input, so Oracle does you a “favour” and does an implicit conversion from date to a string. How does it know how to do this conversion? It uses the NLS_DATE_FORMAT value for the session. What is the default value in our database?

SQL> SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') FROM dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
----------------------------------------
DD-MON-RR

SQL>

OK. So this is what is really happening, thanks to the implicit conversion.

TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR')

That’s lucky. Those formats will work with no errors. Phew.

SQL> SELECT TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR') FROM dual;

TO_DATE(T
---------
08-JUL-20

SQL>

So what’s the problem with APEX?

If I print out the NLS_DATE_FORMAT value from my APEX session it defaults to ‘DS’. What does that look like?

SQL> SELECT TO_CHAR(SYSDATE,'DS') FROM dual;

TO_CHAR(SY
----------
08/07/2020

SQL>

This is ‘DD/MM/YYYY’. Now I’m hoping you see the problem.

MON = MON
MM <> MON

We can see the result here.

SQL> ALTER SESSION SET nls_date_format = 'DS';

Session altered.

SQL> SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual;

Error starting at line : 1 in command -
SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual
Error report -
ORA-01843: not a valid month

SQL>

The implicit conversion used a different format mask that happened to be incompatible with the format mask used by the TO_DATE call.

In this case, they should have removed the time component using TRUNC(SYSDATE), but that’s not really the point of this post. Relying on an implicit conversion is *ALWAYS* a bug waiting to happen. In this case it took many years to surface, but the bug was always there. Waiting! APEX didn’t break the code. The code was already broken.

Date handling seems to mystify a lot of people, but it’s not that hard. You just have to pay attention and understand the functions you are using, rather than randomly combining things together until they appear to work.

Cheers

Tim…

PS. If someone says Oracle stores dates as strings, punch them in the face. I take no responsibility for the outcome of this action.

PPS. If you want to know more about Oracle dates, timestamps and intervals, you might want to look at this article.

Video : SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

In today’s video we’ll give a quick demonstration of deploying an APEX application using the SQLcl implementation of Liquibase.

I Know what you’re thinking. Didn’t I do this video two weeks ago? The answer is yes and no. This video is very similar to the Liquibase video I did two weeks ago, but that was using the Liquibase Pro client. This video uses the SQLcl implementation of Liquibase, and more specifically the runOracleScript tag to achieve the same thing.

The video is based on this article, which has an example of deploying an APEX workspace and an APEX application.

If you are new to Liquibase and SQLcl, you might find it easier to start with these.

The stars of today’s video are the offspring of Jeff Smith. I had been annoying Jeff on Twitter DMs while he was meant to be on holiday, so I agreed to pay him back by turning his children into international megastars. I take no responsibility for how they handle the fame! 😉

Cheers

Tim…

20 Years of ORACLE-BASE.com

It was twenty years ago today that the first incarnation of my website was born.

It started life as a few scripts and notes put on the internet so I didn’t have to carry them around on floppy disks or CDs when I was moving around between contracts. I had been working with Oracle technologies for five years before the website was born, but most of the early content was Oracle 8i stuff.

I added new articles now and then, but I guess it really started to take off when I was preparing for the Oracle 9i OCP upgrade exam. For the 7.3, 8 and 8i OCP exams I used books to prepare, but for 9i OCP I decided to do the beta exam. This meant there were no books available yet, so I had to do it the hard way. I wrote my own revision notes for the whole of the syllabus and put them on the website. They got pretty popular, as free things often do. 🙂 From that point on I just kept adding articles on a regular basis.

Over the last 20 years the site has gone through lots of changes, but also stayed reassuringly the same. It takes a lot of work to keep producing new articles, but what most people don’t see is the amount of time that is taken up reworking some of the old articles. Some of this stuff was written 20 years ago and I come back to things and want to hang my head in shame. Someone will ask a question about an article, I will read it, hate it and rework it. It’s a never ending job, and the more you write, the more you have to refactor down the line. 🙂

Thanks to everyone who has supported me over the last 20 years. It’s not over yet.

Cheers

Tim…