Category Archives: MS Windows

Notes about various technical issues of supporting and administering Windows Servers.

SQLPLUSW.EXE – The king is dead, long live the king!

Hands down, an old “GUI” version of Oracle SQL*Plus (sqlplusw.exe) which was available only on Windows, was always a bit awkward to use compared to a command line version of the same tool. Nevertheless, it was GUI version of the SQL*Plus that I was using most often for ad hoc access to database. Despite of obvious shortcomings in the area of command line history and editing, it felt familiar environment to many developers and DBA’s and now that it’s gone forever with the release of Oracle 11g, I thought I would prepare a “replacement” that will somehow visually resemble old tool but also bring some goodies not available in sqlplusw.exe.
First thing that we need to do, is to correctly setup environment. Since I’m using Windows 7 with regional settings for Slovenia and with Oracle NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250, it means that I can not simply create a shortcut to sqlplus.exe, because command line version of sqlplus.exe is not able to deal with code page CP1250. Before we run sqlplus.exe we need to change NLS settings to NLS_LANG=SLOVENIAN_SLOVENIA.EE8PC852 (or whatever MS DOS codepage variant you’re using in your country for Window command line). This “limitation” in particular was the #1 reason for sticking with sqlplusw.exe all those years and since “MS DOS” LATIN 2 codepage (CP852) is a subset of CP1250 it means that we must be aware that some characters can’t be displayed. For example euro symbol (€) is not supported by CP852, you won’t see properly € character and whenever you’ll want to insert € you must do it with Oracle function CHR(128) instead of typing € on keyboard — otherwise the character will be lost during client-server characterset translation. The simplest approach is to prepare a “special” launch script just for sqlpluse.exe, here is mine (saved in E:\Oracle):

@echo off
::
:: RunSQLPlus.cmd
:: 
title ORA11 x64
SET ORACLE_HOME=E:\ORACLE\ORA11P3
SET PATH=%ORACLE_HOME%\BIN;%PATH%
SET SQLPATH=E:\ORACLE\scripts
SET NLS_LANG=SLOVENIAN_SLOVENIA.EE8PC852
cmd /k sqlplus /nolog

Now we can create shortcut to this batch file, of course with legendary icon;-)
sqlplusw-icon

sqlplusw-0

By default we have a boring sqlplus command prompt window. Let’s change some properties, open context menu of the window, then “Properties”. Make sure both “Quick Edit Mode” and “Insert mode” are checked, also increase “Buffer Size” size from 50 to something bigger, like 200.

sqlplusw-1

On the next tab select font of your choice, my favorite font is “Lucida Console”, size 16.

sqlplusw-2

On the “Layout” tab increase “Screen Buffer Size” and if you don’t like 80 x 25 character sized default window, change the proportion to something else.

sqlplusw-3

On the “Colors” tab pick a color of your choice, since I want to achieve the look of old GUI sqlplusw I changed background to white and treated myself with a blue color for text, something I could not do in GUI variant.

sqlplusw-4

Let’s close (and save changes) and again launch sqlplus to see the changes…

sqlplusw-5

This is much better. The last tidbit of tweaking our new “king” is making SQL*PLus display customized sql prompt with user and database that we’re connected to. If you recall cmd batch script from above you’ll see SQLPATH pointing to E:\ORACLE\scripts. In this directory create your customized login.sql script, like this one:

-- login.sql
host title &_user@&_connect_identifier
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER~SQL> "
set linesize 2000
set pagesize 500
set truncate on

This script will properly set both sqlplus prompt as well as title of the command line window to reflect user and db to which we’re connected. Even if command prompt is in minimized state on taskbar, we can easily determine to which database we’re connected by hovering mouse pointer over the icon.

sqlplusw-6

And finally, I would again like to warn about properly setting up NLS_LANG environment variable (in my case to MS DOS Latin 2 codepage), otherwise you’ll end up with data corruption due to incorrect code page conversion between the client and server.

sqlplusw-7

That’s pretty much all. One thing that you must be extra careful about is when you just want to use sqlplus to run some script written in Notepad/Notepad++ (written likely in cp1250 in CE region) in which case you should run sqlplus.exe with EE8MSWIN1250 settings. The instructions above are targeted to interactive ad-hoc querying.

Even if you’re a die-hard user of sqlplusw it’s just a question of time when you’ll appreciate the benefits of cmd.exe:

  • Arrows Up and Down allow you to navigate between the commands in the buffer
  • Arrows Left and Right allows you to navigate inside current command line
  • F7 brings a menu with commands in the buffer. Select and press Enter to execute the command line selected, or Select and Right Arrow to put the selected line on the command prompt where you can edit it
  • F8 allows you to search command line buffer. For example start writing your query, then press F8 and cmd will try to pull the line from the buffer.

Installing Windows 8.1 as VirtualBox Guest (Error 0x000000C4)

A short reminder to myself of what to do to prepare VirtualBox guest VM for Windows 8.1 (or Windows Server 2012 R2) installation:

Determine VM name dedicated to Windows 8.1 installation:

cmd> "c:\Program Files\Oracle\VirtualBox\VBoxManage.exe" list vms

"WIndows8-64" {2962e10b-2168-4d0c-b4b0-b9b104b66661}

Turn on CMPXCHG16B CPU property for VM:
"c:\Program Files\Oracle\VirtualBox\VBoxManage.exe" setextradata "WIndows8-64" VBoxInternal/CPUM/CMPXCHG16B 1

Gpg4win – file encryption

This is a short note about using GnuPG on Windows 7/2008R2 to encrypt file(s) with symmetric AES encryption. GnuPG is installed on every Linux box that I work with. Fortunately, Windows port is well maintained and for simple batch use it’s enough to install Gpg4win-vanilla package from gpg4win.org. Current version Gpg4win 2.2.21 is shipping GnuPG 2.0.22.

Make sure that you have Gpg4win binaries in PATH (try to run gpg2), if they’re not then add to your path:

SET PATH=C:\Program Files (x86)\GNU\GnuPG;%PATH%

Here is an example of encrypting single file with gpg2:

cmd> gpg2 --batch --yes --passphrase mysecret -z 0 --cipher-algo AES128 --output "D:\TEMP\MYBACKUP.BAK.gpg" --symmetric "D:\ETL\MYBACKUP.BAK"

We’re telling gpg2 that we’re running command in batch mode (–batch), that we don’t want to compress data since input file is already compressed (-z 0) and that we wan’t to use AES128 encryption. With –output switch we’re telling gpg where to write encrypted file – this parameter always precedes command (–symmetric in this case), otherwise we get an error. In current version it’s not possible to specify wildcards for files, so encrypting single file is the only option with –symmetric command.

Decryption is as easy as:

cmd> gpg2 --batch --yes --passphrase mysecret --output "D:\ETL\MYBACKUP.BAK"  --decrypt "D:\TEMP\MYBACKUP.BAK.gpg"

SQL Server BI Studio 2008R2 on Windows 7 (x64) Gotcha

Yesterday migration of our main OLAP server from MS Analysis’s Services 2005 to 2008 R2 was (more or less) uneventful experience, we thought installing “SQL Server Business Intelligence Development Studio” on developers desktops running Windows 7 x64 would be as dull as migration itself. That was not the case. It took us almost half a day troubleshooting error that prevented us to open any Cubes in our migrated databases from developer workstation. Every attempt was greeted with the error “Insufficient memory to continue the execution of the program”.

Configuration of the workstation looks like this:

  1. Windows 7 Enterprise (x64)
  2. Oracle client 11gR2 (32-bit)
  3. MS Office 2010 (32-bit)
  4. MS SQL Server BI Studio 2008 R2 (32-bit)
  5. HW: Fujitsu Esprimo Workstation with iCore5 and 8GB of RAM

Everything patched to the maximum possible level as of May-21-2013. My first assumption was that something related to x64 vs. x86 miss-match between installed components is preventing VS shell to open the cube, but how if all components that need to work together are 100% 32-bit!?
After I dismissed my initial hunch and some other miss-trials, I did what every self respecting systems engineer does in such situation. I went to seek some intimacy in my “cubicle”, then when no one was looking I opened the Chrome and asked Google for help. Typical day in the life of SE, don’t you think?
At last I found a post from Jerry Nee in this thread, pointing to Microsoft Download Center where we can download “Office 2003 Add-in: Office Web Components” needed by MS BI Studio 2008R2 to browse the cubes. After I installed owc11.exe on developer’s workstation browsing cubes was at last possible.
Many thanks to Chief Software Architects at Microsoft. Keep up with your work, as long as you’re doing your job as you do, I’m not worried to loose mine.

Replacement for choice.com on Windows 7 x64

I have a plenty of Windows interactive batch files that are using choice.com tool as a helper when I need some input from the user, for example:

:: ***********
:: * BEGIN   *
:: *********** 
:begin
color 0e
cls
echo.
echo **********************************************
echo You started interactive batch script.... 
echo **********************************************
echo Select server:
echo (1) EUROPE 
echo (2) ASIA
echo (3) US
echo (4) ALL THREE
echo (5) EXIT 
echo.

..\util\choice /C:12345 Pick one

if errorlevel 5 goto end
if errorlevel 4 goto all
if errorlevel 3 goto US
if errorlevel 2 goto ASIA
if errorlevel 1 goto EUROPE

:EUROPE
... do some stuff ...
goto end

:ASIA
... do some stuff ...
goto end

:US
... do some stuff ...
goto end

:ALL
... do some stuff ...
goto end

:: END
:END

Choice.com is 16-bit application that can not run on Windows x64. The simplest workaround that I found is to use SET /P command. It’s a little bit of more code to write, but it’s quite trivial and does not hurt script readability. We can rewrite above script as:

:: ***********
:: * BEGIN   *
:: *********** 
:begin
color 0e
cls
echo.
echo **********************************************
echo You started interactive batch script.... 
echo **********************************************
echo Select server:
echo (1) EUROPE 
echo (2) ASIA
echo (3) US
echo (4) ALL THREE
echo (5) EXIT 
echo.

set choice=
set /P choice="Select 1..5: "

::
:: I used ~0,1 to "substring" first character from the input
::
if not '%choice%'=='' set choice=%choice:~0,1%

if /I '%choice%'=='1' goto europe
if /I '%choice%'=='2' goto asia
if /I '%choice%'=='3' goto all
if /I '%choice%'=='5' goto end

::
:: if we came here then we know that user entered 
::
echo "%choice%" is not a valid option
pause
echo.
goto begin


:EUROPE
... do some stuff ...
goto end

:ASIA
... do some stuff ...
goto end

:US
... do some stuff ...
goto end

:ALL
... do some stuff ...
goto end

:: END
:END