Monthly Archives: January 2024

January 24, 2024

ANSI vs. Oracle Proprietary SQL

By Rich Soule

On Tuesdays Cary Millsap runs a meeting for Method-R customers to chat about Oracle stuff. Often he has something prepared, but this Tuesday he didn’t. Doug Gault decided to share an image that finally helped him get his head around the ANSI SQL syntax. Doug has been around the Oracle world for a long time but he’s always been able to work with Oracle proprietary SQL so he never really learned the ANSI SQL syntax. Recently he got assigned to a project where ANSI SQL is mandated so he had to get everything straight in his head. He shared an image that he had created from some training and we all took a look at it. Me, being me, I immediately jumped in with what I thought would be improvements to the image. I was challenged to come up with a better image, and so, I created the below.

My hope is that this will help some folks move away from the horrible (in my opinion) Oracle propriety SQL syntax to the totally awesome ANSI SQL syntax. I think the Oracle syntax is horrible because where clauses in queries end up doing two things; joining table AND filtering rows. With the ANSI syntax, join clauses join tables and where clauses only filter rows.

A note on the above: I used the preferred USING syntax to join tables for the ANSI queries:

join using (deptno)

instead of the ON syntax to join tables

join on e.deptno = d.deptno

I believe this is easier to read and understand and, in general, less code is better code, and this is smaller. If you use the USING syntax just note that you no longer associate the column with one table or another in the other clauses (like SELECT or WHERE) but instead leave it unconstrained. For example:

select deptno, e.ename, d.dname
  from emp e
  join dept d using (deptno)
 where deptno > 20;

If you were to qualify the DEPTNO column in either the select clause or the where clause (d.deptno for example) you’d get an ORA-25154: column part of USING clause cannot have qualifier message.

Let me know if this is helpful for you!

2 Comments   |  tags: , , , , , | posted in Code, Oracle Database, SQL

January 10, 2024

It Finally Happened For Me!

By Rich Soule

I’ve been working on an upgrade of Oracle Database on Windows. Despite working with Oracle Database for over 30 years, I really never spent a whole lot of time working on a Windows server. Unix, Solaris, Linux, heck even AIX, oh yeah. Windows, not so much.

While attempting to patch a brand new software-only install of Oracle 19c from the original 19.3 up to 19.21 I kept on getting UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed during my opatch apply. It appeared that the JDK home in my ORACLE_HOME was in use. Of course, this didn’t make any sense since there wasn’t anything running out of this home.

Here’s what I was seeing:

Following active files/executables/libs are used by ORACLE_HOME: c:\app\oracle\product\19.0.0.0\dbhome_1

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\bin\java.exe

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\java.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\management.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\msvcr100.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\net.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\nio.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\server\jvm.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\verify.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\zip.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\cldrdata.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\localedata.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\zipfs.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\jsse.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\rt.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\bin\java.exe

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\java.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\management.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\net.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\nio.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\verify.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\zip.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\bin\server\jvm.dll

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\jsse.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\rt.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\cldrdata.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\localedata.jar

c:\app\oracle\product\19.0.0.0\dbhome_1\jdk\jre\lib\ext\zipfs.jar

Why were they in use? Why were they listed twice? What Windows process had a lock on them? I couldn’t figure it out.

I reached out to the system admins for the Windows box to learn that the machine had anti-virus software. Pete Magee has a very well-written blog post about running anti-virus software on your Oracle Database server. Of course that was it! I got them to turn it off, but it didn’t fix the issue. Same exact error, same exact message.

Time for drastic measures. I downloaded IObit’s Unlocker program (use at your own risk) and used it to look at the files. It was showing no locks on any of those files, but I went ahead and unlocked them anyway. Of course this was it. This had to fix it… Nope! Still broken.

In desperation, I reached out to Oracle Support. And that’s when it finally happened for me. Support generated this:



My Oracle Support has performed a Knowledge search using your Service Request details (Summary, Error codes, Product) and located the following documents which may be related to your issue.



Search Results

====================================================================================

99% - Doc ID 2507120.1 Opatch 11.2.0.3.20 : Applying PSU/Windows BP fails with:'To run in silent mode, OPatch requires a response file for Oracle Configuration Manager (OCM)', 'error code = 73'

99% - Doc ID 1475147.1 OPatch - Failed to load the patch object. Possible causes are: OPatch failed with error code = 73 LsInventorySession failed:

99% - Doc ID 2950970.1 opatch apply fails with Error:" Prerequisite check "CheckActiveFilesAndExecutables" failed" on $oracle_home/jdk files

99% - Doc ID 1472242.1 Applying Patch Fails as Copy Failed for crsctl.bin as EM dbconsole was not Stopped

99% - Doc ID 2978449.1 "opatch util verify" reports OUI-67073:UtilSession failed: Files are not updated completely & OUI-67124:Files check failed: Some files under oracle_home are not patched , after applying RU Patches

Wait… That third Doc ID looks exactly like my error. I took a look and started reading and while it didn’t match my situation, it did mention the problem. Someone had downloaded opatch for the wrong platform. Could I really have done that? You betcha! (been watching a lot of Fargo with my wife, and “Minnesotan” is very catchy).

I got a new version of OPatch, this time for Windows instead of for Linux and you know what? Everything worked just fine.

In over 30 years of using Oracle Support, this is the first time I’ve ever had the “Oracle Support did a search and we found these documents” actually have the solution to my problem!

Happy New Year and Happy DBAing!

Leave a comment   |  tags: , , | posted in Code, Oracle Database, Windows