Posts Tagged ‘PL/SQL Developer’

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…

Anyone that uses Standard Edition Oracle has a problem: the principle management tool for Oracle databases provided free by Oracle Corporation (Enterprise Manager) is not terribly useful (all the good bits, like performance tuning, ASH, ADDM and so on require Enterprise Edition licenses, plus -more often than not- additional licenses on top of that). It’s especially not useful when you have two or three databases per production server -for without Grid Control, there is no satisfactory way of using dbconsole as a single point of managing more than a single database.

A reasonable replacement for Enterprise Manager, however, is Fourth Elephant’s Insider. It costs quite a bit of money (over US$1000 per database, last time I checked), but it provides a central management console for lots of databases with quite a lot of functionality and with a reasonably-presented interface. It’s mostly Java-based, and therefore a version is provided that’s supposed to run on multiple OSes. I wasn’t able to get it running on Debian 5.0, though -but that’s probably more my fault than anyone’s.

Happily, if as root you do aptitude install wine, you can then download the Windows-specific version that ships with an in-built Java Runtime Environment. You then simply do (as yourself) wine insider.exe and have the thing install itself just as it would on Windows. A thin-client connection option means that there’s no requirement to install any Oracle client software at all in the Wine ‘bottle’. The program then works well and has all the functionality I’ve come to expect from using it on ‘proper’ Windows PCs.

Another tool we use a lot at work is PL/SQL Developer. I know I’m supposed at this point to recommend switching to Oracle’s own SQL Developer tool, which is free and very capable, but PL/SQL Developer (definitely not free) is the tool of choice in-house and is something you get very used to very quickly. And the especially bad news is that it’s a Windows-specific program, which is not cool. Luckily, it works well under Wine.

So, assuming you’ve already installed Wine as mentioned above, download the Windows Instant Client software bundle from Oracle. Save that to your Linux desktop, then use the Linux file browser to navigate to your .wine directory (you’ll need to switch on the ’show hidden files’ option to find it). Then open the drive_c directory: you’re now looking at a sort-of virtual C: for Wine’s fake Windows PC. Copy the Instant Client bundle to this C: drive, right-click it and select ‘extract here’. Once the thing has uncompressed, rename the directory it created to be, for example, “instantclient”.

Now open a Linux terminal and (as yourself) invoke the Windows registry editor: wine regedit. Navigate to the HKEY_LOCAL_MACHINE\System|CurrentControlSet\Control\Session Manager\Environment registry key. Edit the key named PATH: add C:\instantclient to the end of whatever is already there. Mine ended up reading: C:\windows\system32;C:\windows;c:\instantclient. Then, in the same place, right-click and select New -> String Value. You may find that immediately something called ‘New Value #1′ gets created. Right-click that and select rename. Type in the right name for the new key: TNS_ADMIN. Right-click once more and select Modify. In the Value data field, type the path to your instant client installation once more: c:\instantclient in my case. You’ve just set things up so that any Oracle management tool will expect to find a tnsnames.ora in the c:\instantclient directory.

So, now you create your tnsnames.ora in that c:\instantclient directory. I copied one from a Windows PC that had already had one created using the standard Oracle Net Configuration Assistant, but however you get it there, it needs to be there!

Finally, you (as yourself) install PL/SQL Developer: wine plsqldev715.exe. The installation should go without drama, but the program won’t be functional yet. To make that happen, launch the program, cancel any attempt to connect to a database, and get to the main program window. Select Tools -> Preferences. Select the User Interface -> Appearance options on the left, and then de-select the “Faded disabled buttons” option in the main part of the screen. Finally, select Oracle -> Connection in the left pane and add c:\instantclient to the Oracle Home field and c:\instantclient\oci.dll in the OCI Library field. Shut the program down and re-start it and you should now be able to connect to all your databases as normal. (I had to fiddle with the User Interface -> Fonts option, bumping up the font sizes so they looked half-decent, but that was about it).

You will probably see spurious red crosses in the hierarchy trees shown in the object browser: ignore them and they won’t do you any harm. I’ve not encountered any other dramas editing code or using the program to issue SQL statements against databases, but -as with all things Wine- your mileage might vary!

Just in case, I have downloaded Oracle’s SQL Developer, which provides a ‘native’ way to interface with Oracle databases on Linux. But I’m hoping I won’t have to use it much!