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:
putty:-ssh -l oracle -pw password1 myhost
These may seem barely recognisable as URLs. You will be used to URLs like these:
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:
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:
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:
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;”