Category Archives: Oracle
All those yellow sticky notes about Oracle will usually end under this category.
Killing me softly…with this SQL
This is an old post that was on hold for several years, because I didn’t want to risk someone crashing our 11g R2 production servers with simple copy/paste from this site. In 2022 only fools still runs 11g in production, so I decided to release a test case to the public.
A few years ago I opened SR on MOS, because I considered the bug described in this blog as a serious one; basically Oracle built-in regexp functions allows anyone with a create session privilege to crash Oracle 11gR2 instance on Windows (and/or user session on Linux). Instead of fixing the bug in 11g R2, Oracle simply closed the SR as “solved” in 12c.
Well, if you have some legacy 11gR2 instance still up and running in 2022, then you can ask your DBA a question: “Do you feel lucky, punk? Do you…”;-)
With the query you’ll crash 11gR2 instance running on Windows (witouht a trace).
— We tested on 11gR2 up to and including >=BP Oct 2016.
— If you run the same query on 11gR2 on Linux (only) the user session will crash.
— Query runs fine on 10gR2 and 12cR1.
— If the query regex pattern is reduced to ~ 336 characters then even on Windows you’ll crash your user session and not the instance (with trace being generated!).
— If the query regex pattern is reduced even more, to ~ 304 characters the query will run fine without a crash.
with data as ( select 'This query will crash any 11gR2 instance on Windows and user session on Linux!' as demo from dual ) select demo, (case when regexp_substr(demo,'(\d+)G|(\d+)\sG|(\d+)G\s|(\d+)KG|(\d+)\sKG|(\d+)KG\s|(\d+)L|(\d+)\sL|(\d+)L\s|(\d+)ML|(\d+)\sML|(\d+)ML\s|(\d+)[,](\d+)G|(\d+)[,](\d+)\sG|(\d+)[,](\d+)L|(\d+)[,](\d+)\sL|(\d+)[,](\d+)KG|(\d+)[,](\d+)\sKG|(\d+)[,](\d+)ML|(\d+)[,](\d+)\sML|(\d+)[.](\d+)G|(\d+)[.](\d+)\sG|(\d+)[.](\d+)KG|(\d+)[.](\d+)\sKG|(\d+)[.](\d+)L|(\d+)[x](\d+)\sL|(\d+)[x](\d+)ML|(\d+)[.](\d+)\sML',1,1,'i') is null then 1 end) lol from data;
For those who wonder how did I come up with such regex expression, well, I did not. This regex is actually part of ETL production code; kudos goes to developer who put together this nifty little regex that determines from the data input a proper unit of […..].
I won’t tell what it does, because it’s so obvious from the code itself.
MS Access 2016 — a bug finally fixed after 16 years – NOT! (Part 2)
Only recently, I realized that I was dead wrong (two years ago), claiming that Microsoft finally fixed a bug in Microsoft Access 2016 that caused wrong results whenever we use a combination of:
- linked tables via Oracle ODBC driver,
- Oracle table contains attribute(s) with data type NUMBER(n,m),
- and Windows client OS is properly using whatever (Eastern Europe) regional settings is valid for the country. In Slovenia we’re using comma as a decimal separator.
If above requirements are met, then MS Access (any version!) interprets the decimal number as integer (2342,45 becomes 234245). This can be a potential hefty salary raise for someone.
When I reported that Microsoft finally fixed a bug, I didn’t realize that I was working on Windows 10 VirtualBox VM, where I freshly installed Oracle Client 12c, but didn’t bother to check NLS_LANG in the registry. As it is usually the case, Oracle installer puts (incorrect) NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 in the registry, instead of correct one, NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250.
While preparing some new standard PC configuration for our current customer, I was surprised when I found out that an old bug is back. It was “back” because on this “matrix” machine Oracle client was properly setup with NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250. Since going back to 32-bit MS Office 16 and Microsoft ODBC driver for Oracle (only 32-bit version is available!) was not an option, we were left to figure out some workaround. And we did. We found out that MS Access interprets decimal data type in Oracle table correctly, if we change territory from Slovenia to America (without changing *any* other regional settings at OS level!).
Something like this:
-- open cmd, setup NLS_LANG, then run MS Access cmd> SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 cmd> "C:\Program Files\Microsoft Office\Office16\msaccess.exe"
Changing NLS_LANG variable at registry (machine) level to SLOVENIAN_AMERICA.EE8MSWIN1250 doesn’t sound as a smart move, just for the sake of fixing a bug in one faulty product, so we decided to write some simple launcher for MS Access that can fix NLS_LANG variable behind a scene, leaving NLS_LANG in registry intact.
Below is a simple Go code that’ll run MS Access after properly setting up territory part of NLS_LANG variable. Note that we deliberately used absolute paths to MS Access binary that works for our Windows 10 standard configuration, something that you might want to change if you decide to use it. You can change language and character set part of NLS_LANG variable as well.
To build executable from the code follow these steps:
- download and install Go
- save code in some file, such as G:\SRC\runAccess.go
- compile code, open cmd and execute:
cd G:\SRC & go build - ** or, you can compile the code with:
go build -ldflags -H=windowsgui
** The alternative build with the -ldflags -H=windowsgui is optional but recommended. It will cause console window to close after it launches MS Access.
// runAccess.go -- MS Access launcher
package main
import (
"fmt"
"os"
"os/exec"
"flag"
)
func main() {
var version *bool
var help *bool
version = flag.Bool("version",false,"18.02")
help = flag.Bool("help",false,"MS Access 2016 Launcher")
flag.Parse()
if *version {
fmt.Printf("February 2018 by AlesK\nVersion %s\n", (flag.Lookup("version")).Usage)
os.Exit(0)
}
if *help {
fmt.Println("I'm here to silently run this code for a user:")
fmt.Println("cmd /c SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:\\Program Files\\Microsoft Office\\Office16\\msaccess.exe")
os.Exit(0)
}
if (os.Args != nil && len(os.Args) > 1) {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe", os.Args[1])
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
} else {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe")
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
}
}
You can do two things with runAccess.exe. You can run it as a standalone program. It’ll open MS Access with territory part of NLS_LANG set to AMERICA. Or, you can associate *.accdb or *.mdb file extension with this launcher and it’ll correctly start MS Access if user double clicks on MS Access database file.
If you’re wondering what is perhaps a negative consequence of tweaking territory part of NLS_LANG variable, then you can refer to Oracle official NLS lang FAQ, a quote:
What does the TERRITORY component of the NLS_LANG parameter control?
The territory component of the NLS_LANG parameter controls the operation of a subset of globalization support features. It specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA , FRANCE , or CANADA . If the territory is not specified, then the value is derived from the language value.
We can certainly leave with “wrong” monetary value and having american “default date” is easy to fix with a proper NLS_DATE_FORMAT.
UPDATE: April 11, 2018
I added a small troubleshooting feature to my Golang code that allows end users to easily check if they run instance of MS Access spawned from my runAccess app. I added /cmd at the end of the command line with text NLS_TERRITORY=AMERICA. This MS Access related “command line switch” is usually read by VBA function Command(), but can also be viewed from:
File -> Options -> Client Settings -> Advanced -> Command-line arguments NLS_TERRITORY=AMERICA
package main
import (
"fmt"
"os"
"os/exec"
"flag"
)
func main() {
var version *bool
var help *bool
version = flag.Bool("version",false,"18.03")
help = flag.Bool("help",false,"MS Access 2016 Launcher")
flag.Parse()
if *version {
fmt.Printf("February 2018 by AlesK\nVersion %s\n", (flag.Lookup("version")).Usage)
os.Exit(0)
}
if *help {
fmt.Println("I'm here to silently run this code for a user:")
fmt.Println("cmd /c SET NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:\\Program Files\\Microsoft Office\\Office16\\msaccess.exe /cmd NLS_TERRITORY=AMERICA")
os.Exit(0)
}
if (os.Args != nil && len(os.Args) > 1) {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe " + os.Args[1] + " /cmd NLS_TERRITORY=AMERICA")
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
} else {
cmd := exec.Command("cmd", "/C", "set NLS_LANG=SLOVENIAN_AMERICA.EE8MSWIN1250 & start C:/\"Program Files\"/\"Microsoft Office\"/Office16/msaccess.exe /cmd NLS_TERRITORY=AMERICA")
if err := cmd.Run(); err != nil {
fmt.Println("Error: ", err)
}
}
Please, can someone deliver some cloud “stuff” to Oracle Support?
Can you tell me what is wrong with this screen capture that I took on MOS and is part of my Service Request?
Right now, I’m waiting SR analyst to download and install Oracle software, so that he can run query provided by us. I hope that they have at least a decent bandwidth, if they’re not able to use provisioned virtual machines in the first place. And that is the same company touting their cloud offerings. LOL.
Unicode and Oracle SQLcl…on Windows — solved
I was struggling with sqlcl on Windows 7 to properly display our umlauts (we’re using Windows 7 desktops with NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250 setup in the registry — note: sqlcl is not reading this variable).
When I read Jeff Smith blog post “Unicode and Oracle SQLcl…on Windows” I though that my problem was solved. Someone reading an article without reading the comments would assume that sqlcl works out of the box on Windows with proper UTF-8 support, which does not. Partly due to the omission of proper parameter in supplied sql.bat file, but mostly because of the state of cmd.exe (powershell.exe) in versions of Windows 7 and below, Windows 10 is much better.
In this demo we’re using Windows 7 EE (Windows 10 EE), Oracle 12c R1 and sqlcl-4.2.0.16.308.0750-no-jre.zip.
First, we created test table called UMLAUT in SQL*Developer and inserted our umlauts:
Then we run a query from this table with sqlcl. Note an extra line between the rows returned from the query….
ok, how about writing some umlauts on the command line….
Well, we can write umlauts but console won’t show us what we wrote (note a presence of squares)…nevertheless the result of the query is correct.
What we can do? Well, for a start we need to patch the officially supplied sql.bat script.
Open sql.bat and replace line
SET STD_ARGS=-Djava.awt.headless=true -Xss10M
with
SET STD_ARGS=-Djava.awt.headless=true -Xss10M -Dfile.encoding=UTF-8
But don’t celebrate yet…what we achieved is this….
We still have an extra line between the rows, which is annoying, but at least we can see what we wrote in the WHERE condition. Plus an extra square :-)…if you’re “lucky” Windows 7 user.
However, above patch is enough on Windows 10, where, both writing of umlauts and properly displaying the records (without extra blank line) works as expected….
The only “workaround” that we found for Windows 7 clients is to simply forget about official console applications (cmd.exe and powershell.exe) as a “host” for sqlcl and use some alternative. We found out that ConEMU works great…(patch in the sql.bat is of course still mandatory until sqlcl guys do this for you).
And what about the suggestion that we can tweak the registry and permanently change the console application (cmd.exe) code page to UTF? Don’t do this, because you’ll disable some non-java applications, including SQL*Plus…look what happens with sqlplus.exe….
Installing Standalone Oracle Http Server 12c R2 (12.2.1.1) on Windows 2012 R2
IMPORTANT!
According to MOS note Oracle Web Tier – Statement of Direction (Doc ID 1576588.1) Oracle mod_plsql in Oracle HttpServer is deprecated as of version OHS 12.1.3.
If you’re reading this to learn how to install OHS because your application depends on mod_plsql functionality, then we have a bad news for you. Oracle removed mod_plsql from OHS 12.2. So, the best help that we can give you is our reference installation note for OHS 12.1.3 installation on Windows 2012 R2.
Oracle recommends to move to Oracle REST Data Services (ORDS) (formerly known as Apex Listener) on top of Oracle WebLogic, Oracle Glassfish or Apache Tomcat.
Of course, if you’re still interested in installation procedure for OHS 12.2 on top of Windows 2012 R2 keep reading….
Once again, we got a brand new server with Windows 2012 R2, on which we wanted to install standalone Oracle HttpServer 12c R2 (12.2.1.1.0). If you read our post from the last year covering Oracle HttpServer 12.1 installation on Windows 2012 R2, you already know that we’re a big fans of Oracle end user friendliness and admiration of Oracle engineering capabilities to transform a mouse to an elephant. Let’s see if this crap excellent piece of software installs as smooth in Release 2 as it did in Release 1.
Find and download fmw_12.2.1.1.0_ohs_win64_Disk1_1of1.zip from OTN, at the time of this writing you should see something like this:
Unpack fmw_12.2.1.1.0_ohs_win64_Disk1_1of1.zip and run setup_fmw_12.2.1.1.0_ohs_win64.exe.
Setup will check the system prerequisites, you should be fine if you’re installing on Windows 2012 R2. Now, we’ll show you some screenshots from our installation with comments whenever we think they’re necessary. Basically, we left most options at default (apart from changing the Oracle home).
According to Oracle documentation:
On the Windows platform, Oracle HTTP Server requires Microsoft Visual C++ run-time libraries to be installed on the system.
To meet this system requirement for Oracle HTTP Server, download the Visual C++ Redistributable for Visual Studio 2012
software from the following URL: https://www.microsoft.com/en-us/download/details.aspx?id=30679D
After we downloaded and installed Visual C++ (VS 2012) redistributable we hit the Rerun button and the check completed successfully…
As you can notice, there is no screenshot of step #9. After we clicked Next on Step #8 the new dialog window opened for a second and immediately closed. Perhaps it was just us….fortunately, we could made an educated guess that the last screen should likely be an informative one, like the one that we saw during OHS 12c R1 installation:
…suggesting that fun is not over yet and that you need to create domain with the Configuration Wizard. Start configuration Wizard from:
D:\ORACLE\OFM\OFM12\oracle_common\common\bin\config.cmd
and configure Oracle Http Server in standalone mode…
If you check Windows services at this time you’ll see, well, nothing. We need to create service for Node Manager before we can start Http server.
Note that we installed Oracle HttpServer 12c R2 in ORACLE_HOME: D:\ORACLE\OFM.
First open command prompt (cmd) wit “Run as Administrator” and set the path before you run installNodeMgrSvc.cmd:
set DOMAIN_HOME_BIN=D:\ORACLE\OFM\OFM12\user_projects\domains\base_domain\bin set PATH=%DOMAIN_HOME_BIN%;%PATH% installNodeMgrSvc.cmd
And if everything has gone well you should see the message at command prompt, with the line:
... Oracle Weblogic base_domain NodeManager (D_ORACLE_OFM_OFM12_wlserver) installed. ...
Script installeNodeMgrSvc.cmd created a new service, that reads, don’t hold your breath….:
"Oracle Weblogic base_domain NodeManager (D_ORACLE_OFM_OFM12_wlserver)"
with path to the executable like this:
D:\ORACLE\OFM\OFM12\wlserver\server\bin\wlsvcX64.exe
Yes, they probably set a World record for the length of a Windows service. What a schmucks.
The service is installed using the default Node Manager listen port (5556). By default Node Manager Listens only on localhost.
Now, we can finally start Oracle HttpServer 12c R2. First, make sure that Node manager service is running and if it’s not then start “Oracle Weblogic base_domain NodeManager (D_ORACLE_OFM_OFM12_wlserver)”.
You can START Oracle HttpServer 12c R2 on command line with:
set DOMAIN_HOME_BIN=D:\ORACLE\OFM\OFM12\user_projects\domains\base_domain\bin set PATH=%DOMAIN_HOME_BIN%;%PATH% startComponent ohs1
You’ll be asked to enter password for Node Manager which you entered on screen 6 of Oracle Fusion Middleware Configuration Wizard.
You can STOP Oracle HttpServer 12c R2 on command line with:
set DOMAIN_HOME_BIN=D:\ORACLE\OFM\OFM12\user_projects\domains\base_domain\bin set PATH=%DOMAIN_HOME_BIN%;%PATH% stopComponent ohs1
There is also a WLST tool (wlst.cmd) that allow us to manipulate with running OHS.
Using WLST to control OHS:
Open WLST D:\ORACLE\OFM\OFM12\oracle_common\common\bin\wlst.cmd:
Initializing WebLogic Scripting Tool (WLST) ...
Welcome to WebLogic Server Administration Scripting Shell
Type help() for help on available commands
wls:/offline> nmConnect('administrator','mysecretpassword','localhost','5556','base_domain','D:/ORACLE/OFM/OFM12/user_projects/domains/base_domain','ssl')
// status
wls:/nm/base_domain> nmServerStatus(serverName='ohs1', serverType='OHS')
RUNNING
// soft restart
wls:/nm/base_domain> nmSoftRestart(serverName='ohs1', serverType='OHS')
// stop OHS
wls:/nm/base_domain> nmKill(serverName='ohs1', serverType='OHS')
Killing server ohs1 ...
Successfully killed server ohs1
// status
wls:/nm/base_domain> nmServerStatus(serverName='ohs1', serverType='OHS')
SHUTDOWN
// start
wls:/nm/base_domain> nmStart(serverName='ohs1', serverType='OHS')
And finally, let’s see where is the config file for httpd. According to the Oracle Http 12c documentation there are runtime and staging config files and we should always edit staging config file(s). In our example the config file location is:
Domain home bin : D:\ORACLE\OFM\OFM12\user_projects\domains\base_domain\bin httpd.conf : D:\ORACLE\OFM\OFM12\user_projects\domains\base_domain\config\fmwconfig\components\OHS\ohs1
Final Test….open IE and go to http://localhost:7777:
dbaportal.eu 
You must be logged in to post a comment.