Category Archives: Oracle

All those yellow sticky notes about Oracle will usually end under this category.

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

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


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 ( on Windows 2012 R2


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 ( 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 from OTN, at the time of this writing you should see something like this:


Unpack and run setup_fmw_12.

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:

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:


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

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:


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
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
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')
// 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')
// 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:


HTTP 400 error Too many arguments passed in

In one of our Apex-like application user reported a HTTP 400 error after clicking on a button that invokes PL/SQL procedure. At first glance I suspected a missing execute privilege but the quick look at Oracle HTTP log revealed the true reason:

[2016-05-04T10:58:23.9709+02:00] [OHS] [ERROR:32] [OHS-9999] [core.c] [host_id: ACMEHOST] [host_addr:] [pid: 5048] [tid: 1816] [user: SYSTEM] [VirtualHost: main]  MODPLSQL-00359: mod_plsql: /sales/!appschema.orderentry.program HTTP-400 Too many arguments passed in(2002). Limit is 2000, referer:!appschema.orderentry.program

-> HTTP-400 Too many arguments passed in(2002). Limit is 2000
.. so, the reason for the error is the default limit in mod_plsql on the number of parameters that can be passed to the pl/sql procedure.

A search on MOS confirmed by suspicion, please refer to:

Modplsql application throws “HTTP-400 Too many arguments passed in. Got 7076 parameters. Upper limit is 7000” (Doc ID 386946.1)

After adding directive “PlsqlMaxParameters 4000” to the plsql.conf and restarting the http listener (cmd> opmnctl restartproc process-type=OHS), the problem was solved.
Our plsql.conf looks like this:


# ============================================================================ 
#                       mod_plsql configuration file                           
# ============================================================================ 
# 1. Please refer to plsql.README for a description of this file               
# 2. Parameters in this file have to be configured manually                    
# ============================================================================ 

# Configure Oracle HTTP Server to load mod_plsql
LoadModule plsql_module ${ORACLE_HOME}\ohs\modules\mod_plsql.dll

# Load in mod_plsql directives only if mod_plsql is loaded
<IfModule mod_plsql.c>

# ============================================================================ 
# Global Settings Section : Directives that apply to all DADs
# ============================================================================ 

PlsqlLogEnable on

PlsqlLogDirectory "${ORACLE_INSTANCE}\diagnostics\logs\${COMPONENT_TYPE}/${COMPONENT_NAME}"

# 4.5.2016 -- (see MOS ID 386946.1)

PlsqlMaxParameters 4000

# PlsqlIdleSessionCleanupInterval 15 (default)

# PlsqlDMSEnable On (default)

# ============================================================================ 
# Database Access Descriptors Settings Section
# ============================================================================ 
include "${ORACLE_INSTANCE}\config\${COMPONENT_TYPE}\${COMPONENT_NAME}\mod_plsql\dads.conf"

# Custom DAD's for
include "${ORACLE_INSTANCE}\config\${COMPONENT_TYPE}\${COMPONENT_NAME}\mod_plsql\marvel.conf"

# ============================================================================ 
# Cache Settings Section
# ============================================================================ 
include "${ORACLE_INSTANCE}\config\${COMPONENT_TYPE}\${COMPONENT_NAME}\mod_plsql\cache.conf"


MS Access 2016 — a bug finally fixed after 16 years

Around ~2001/2002 we found a bug in Microsoft MS Access in a combination with Oracle ODBC.
Bug is finally fixed in MS Access 2016.

Imagine this Oracle table:

create table salary (name varchar2(10), salary number(7,2), salary2 number);

insert into salary values ('King',12345.55,12345.55);

1 row created.


Commit complete.

select * from salary;

NAME           SALARY    SALARY2
---------- ---------- ----------
King         12345,55   12345,55

Note that salary table has salaries stored in NUMBER(n,m) and plain NUMBER data types.

Let’s see what we get if we use MS Access 2010 with Microsoft ODBC driver for Oracle to link the salary table:


Everything is OK. Now, let’s try to link the same table, but this time with Oracle ODBC driver:


Oooppsss. MS Access converted decimal number 12345,55 to an integer 1234555. Someone would argue that this can also be a bug in Oracle ODBC. But it isn’t, because Excel (or any other ODBC app) has no problem handling decimal data over Oracle ODBC driver. This bug persisted in all recent releases MS Office 2000, 2002, 2003, 2007, 2010 and 2013. Using Oracle ODBC with any of those MS Access versions was a big NO for us.

The problem is that the only workaround was to link tables using Microsoft ODBC driver for Oracle, which is 32-bit only, deprecated by Microsoft and not enhanced in at least a decade. You can imagine my surprise after I installed 64-bit version of MS Office 2016 and trying to link to Oracle table with Oracle12c ODBC driver (also tested with older 11g driver) and realized that Microsoft finally nailed the bug after 16+ years.


Oh, and if you wonder if I submitted bug report to Microsoft and Oracle sixteen years ago? Yes, I did. Microsoft product manager simply redirected me to Oracle Support and Oracle support told me that they think that the problem are undocumented hooks in MS Access, hence, MS ODBC driver for Oracle somehow works and driver written by Oracle according to official ODBC specs doesn’t. Oracle suspicion was correct anyway, because the bug was always limited to MS Access.