Tony’s Oracle Tips

Launching SQL*PLUS or PuTTY from Application Express

A couple of years ago I developed an Application Express (well, HTMLDB in those days) report that included the ability to launch SQL*PLUS and PuTTY sessions. The technique can be used to launch any script or application.

I was recently asked to explain how this works so I was given the perfect nudge to write this blog entry.

There are two parts to this solution. Firstly, you need to create a link to a URL from within your report. That is pretty standard stuff but there may be some special security issues that I will address shortly. The second part of the puzzle is to tell your operating system to launch your application when it is given this URL. I know the solution for Windows XP and I will show that here. The solution may well work for other Windows operating systems as well.

The URLs look something like this:

sqlplus:system/change_on_install@orcl
putty:-ssh -l oracle -pw password1 myhost

These may seem barely recognisable as URLs. You will be used to URLs like these:

http://www.google.co.uk
file://c:\
about:blank

The part before the colon is the URL protocol. The interpretation of what occurs after the colon is specific to each URL protocol. The process for adding your own URL protocol is described by Microsoft here:

http://msdn.microsoft.com/en-us/library/aa767914(VS.85).aspx

However, if you want a shortcut, copy and paste the following text into notepad and save it as a file called “sqlplus.txt”.


Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\sqlplus]
@="URL:SQLPLUS Protocol"
"URL Protocol"=""

[HKEY_CLASSES_ROOT\sqlplus\DefaultIcon]
@="c:\\orahome920\\sqlplusw.exe"

[HKEY_CLASSES_ROOT\sqlplus\shell]

[HKEY_CLASSES_ROOT\sqlplus\shell\open]

[HKEY_CLASSES_ROOT\sqlplus\shell\open\command]
@="C:\\sqlplushelper\\sqlplushelper.cmd \"%1\""

You will need to edit this file to change the location of the sqlplusw application (or sqlplus if, like me, you prefer the regular text interface). You may also need to change the location of the “helper” script location if this is inconvenient to you.

The helper script (in C:\sqlplushelper\sqlplushelper.cmd) looks like this:


echo %1 >> c:\sqlplushelper\connect.log
@FOR /F "tokens=1* delims=:" %%a IN ("%~1") DO start sqlplusw.exe "%%b"

The first line is a security hazard and you may wish to remove it after debugging. It records invocations in a logfile. Once again you may choose between sqlplus and sqlplusw. This command assumes that it is in your PATH.

Now comes the risky bit. You need to edit the registry. You might want to take a backup first. See here:

http://support.microsoft.com/kb/322756

You should then change the name of sqlplus.txt to sqlplus.reg and then open the file. This will ask you for a confirmation and the URL will be added to Windows. You can then try this out by launching your favourite browser and invoking sqlplus from there using the syntax shown above:

sqlplus:system/change_on_install@orcl

This should launch sqlplus. A similar setup can be used for PuTTY. The registry settings are:


Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\putty]
@="URL:Telnet Protocol"
"EditFlags"=dword:00000002
"URL Protocol"=""

[HKEY_CLASSES_ROOT\putty\DefaultIcon]
@=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,00,74,00,25,\
  00,5c,00,73,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,75,00,72,00,\
  6c,00,2e,00,64,00,6c,00,6c,00,2c,00,30,00,00,00

[HKEY_CLASSES_ROOT\putty\shell]

[HKEY_CLASSES_ROOT\putty\shell\open]

[HKEY_CLASSES_ROOT\putty\shell\open\command]
@="c:\\sqlplushelper\\puttyhelper.cmd \"%1\""

and the helper command is:


echo %1 >> c:\sqlplushelper\connect.log
@FOR /F "tokens=1* delims=:" %%a IN ("%~1") DO start c:\putty\putty.exe %%b

Note the removal of the quotes around the “%%b”.

Once again you can test this with your browser.

Ok, that’s the hard bit done. All you need to do now is link to the URL from within your APEX application and you are there. To demonstrate this, I will ask you to create a table as follows:


create table t1 as select 'SYSTEM' username, 'CHANGE_ON_INSTALL' password,'ORCL' instance from dual;

You will of course need to change the actual connection details to suit your system. You should then generate a report based on the following query:


select username,password,instance,'connect' conn from t1;

The password column should not be shown. The conn column should have the “Link” details setup as follows:

Link text: Connect
Link attributes: onmouseover=”return true;”
target: URL
URL: sqlplus:#USERNAME#/#PASSWORD#@#INSTANCE#

Most of the above is fairly straightforward but what is that link attribute? Well, by default the link is displayed in the browser status bar when you hover over it. This rather trivial javascript code overrides this behaviour and prevents the password being displayed in the status bar! You can change “true” to “false” for debugging and the status bar will show.

1 Comment »

  1. Thanks for that, Tony – exactly what I was looking for!

    Comment by Other Tony — October 16, 2008 @ 10:08 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: