Posts Tagged ‘Wine’

I mentioned a couple of posts ago that I’d had to wipe my Ubuntu machine at work, replacing it with Windows 7, because PL/SQL Developer running via Wine refused to do extended characters properly. I was convinced at the time that this happened because Wine uses code page 437 by default, and I needed it to use code page 1252 (that is, a Western European code page rather than a US ASCII one). A commentator called Sokrates suggested I should set the Oracle NLS_LANG environment variable -but, although I had considered that at the time, I dismissed the idea because (a) setting a Linux environment variable didn’t seem, to me, to be a likely way to alter a Windows application’s settings and (b) that default code page 437 is definitely ‘wrong’ for doing extended characters -and thus seemed the most obvious line of attack.

Well, I should have listened to Sokrates!

So, first of all, here’s a video of me using the wine regedit command inside a Centos 5.3 virtual machine and noting the default, original code page settings. You’ll then see me run PL/SQL Developer, connect to a Windows 10.2.0.4 database, and encounter the problem of the disappearing extended characters. First, I create a procedure that uses extended characters and am able to compile it no problem… but when I re-open the code, all the extended characters have been stripped out or converted:

So that’s the problem. The question now is whether a code page setting in the registry will fix the problem, as I had hoped. In this next video, therefore, you’ll see me change the code page setting in the registry from its original 437 to 1252 (which is a code page that definitely supports extended characters). With the registry changed, I relaunch PL/SQL Developer and try to store my procedure with the correct extended characters re-inserted. Here’s how well that went:

Finally, I take Sokrates’ original advice and set the NLS_LANG environment variable to a locale that’s identical to the one being used by the database itself. I have to launch PL/SQL Developer from the command line this time, so that it picks up that new environment variable setting. Once it’s running, though, I do the same as before: paste back in my extended characters, re-compile the code… and then open the procedure back up to have a look at what got stored. Here’s how that turned out:

So, the good news is that I can have my Ubuntu installation back at work. The bad news is that I was an idiot to have dismissed an idea without having tried it out. Sorry, Sokrates. And thanks!

I am lucky in my place of employment: I am pretty much free to run whatever operating system I like and install whatever applications I feel the need for, without anyone getting upset and whispering the dread acronym ‘SOE’ in my ear. For the past three months, therefore, I’ve been running Ubuntu 9.04 on my work PC, just as I do at home. It’s worked beautifully for the most part: I occasionally get sent spreadsheets with more than 65,000 rows which OpenOffice is therefore not capable of handling, but nothing a VirtualBox XP virtual machine with an Office 2007 installation couldn’t fix. Similarly, Thunderbird is not a particularly great Microsoft Exchange client, but in its IMAP mode, it served well enough for my purposes. Perhaps the stickiest moment I had was getting an appropriate PL/SQL development tool working: as I’ve documented in these parts before now, the in-house product of choice is PL/SQL Developer, which is a Windows-only product. I’ve tried hard to love the Java-based (and hence completely free and cross-platform) SQL Developer from Oracle, but it’s been a tempestuous affair and hasn’t progressed particularly well! So, PL/SQL Developer it more or less had to be… and, thanks to Wine, it worked pretty well.

Until yesterday.

I had occasion to write a procedure that would add an ê (e-circumflex) to certain fields of certain records. Not very complicated, written and compiled in a jiffy. The only thing was that it didn’t take long to notice that a lot of data was turning up with ¿ characters in them (upside-down question marks). So I opened the procedure in my Wine-cum-PL/Developer, and sure enough: there was my code saying to append ¿ to data. I assumed I’d had a typo fit, and edited the code to once again specify field=replace(field,’e',’ê’).  I hit F8 to compile the corrected code, and it all looked fine. Shut the procedure down, and re-open it… and the upside-down question mark was back once more!

So I opened up my XP virtual machine, and manually typed in a couple of test cases, such as these:

SQL> select 'Ø' from dual;

'
-
O

You might notice that the output is missing the forward-slash that the input contained. This is the sort of thing that happens when your Windows client is operating in classic ASCII mode whilst the database is running in, say, the WE8ISO8859P1 character set. Sure enough, a drop-down to the command prompt and a quick chcp later, and this was the result:

C:\>chcp
Active code page: 437

…and code page 437 is pretty much standard 7-bit ASCII for US Markets. Switch it to code page 850 (Multilingual Latin I), however, and the problem goes away:

C:\>chcp 850
Active code page: 850

C:\>sqlplus sys/password as sysdba

SQL> select 'Ø' from dual;

'
-
Ø

…and now the output and input match exactly. So, from my XP virtual machine, I knew that the tendency of my procedural code to forget the ‘extended’ character I’d typed was probably code page-related. Had I been running ‘true’ XP, I could have set code page 850 as my system-wide default and PL/SQL Developer would have picked up on that change and worked fine thereafter. But I was running PL/SQL Developer courtesy of Wine emulation (and yes, I know that Wine Is Not an Emulator, but let’s not get pedantic!)… and I was (and remain) unaware as to how one goes about setting a default code page for programs running via Wine. Probably there’s a configuration file edit to be made that accomplishes the trick, but if so, I didn’t know what it was… and Google wasn’t any help either.

In short, my principle development tool, on my OS of choice, was incapable of storing anything but plain ASCII characters in code… and I had a (pressing!) need to store ‘extended’ characters. The immediate fix was quite simple: fire up Oracle’s own SQL Developer and edit the procedure in that. Another paste-in of the correct ê character, a compile and a final re-open: the correct extended character was there, just fine. Problem solved. However, I was left in the position that if I ever re-opened that code in PL/SQL Developer and hit ‘compile’, the wrong character would be compiled back into the code. Effectively, therefore, PL/SQL Developer-via-Wine was now no longer usable.

So I did what all sensible people would do in that position, I like to think: install Windows 7 and say goodbye to my workplace desktop Ubuntu experiment.

Well, there were probably a lot subtler ways of fixing the problem properly, and if you know how to set a default code page for Wine, I’m all ears. But Windows 7 did install impressively fast (24 minutes, all up), and it’s not a completely unpleasant OS to use… and PL/SQL Developer works out of the box, with the full panoply of Western European characters available, no problem, no sweat.

Sadly, I shall probably have to do the same at home this weekend, because I like to keep my work and home environments pretty much as mirror images of each other. So it looks like Windows 7 will win the war of my particular set of home desktops after all. I am however happy to report that my old laptop, which serves very effectively as a DNS server, as well as a router via the Bigpond Wireless Broadband account, will remain running Ubuntu (especially since the broadband and Internet connection sharing runs much better on Ubuntu than it ever did on Windows). Some small comfort, perhaps…