Executing command line scripts from the Oracle Forms client – using WebUtil

Our challenge: we are building the new ERP system – a pure HTML5 browser based application in Oracle ADF. This application replaces the current Oracle Forms based application. For more than a year and a half – the users will have a hybrid situation on their hands: some of their tasks are handled in the new application while others are still supported by the current application. It is clear that this dual application situation is not ideal. To make life easier for the users we at least want to offer smart short cuts and intelligent deeplinks from the Forms application to the new ADF application: at a growing number of screens in the Forms applications links will appear that allow the user to navigate to specific deeplink destinations in the ADF application to complete a task, retrieve relevant data etc. This deeplink should carry context: the business object that the user is working with in the Forms application should be prepared in the ADF application to provide the same context at the other end of the deeplink.

The Forms application runs as Java Applet in Internet Explorer on the desktops of the users. The new ERP system is designated to run in Google Chrome. The Java Run Time (JRE) plugin required to run Oracle Forms is no longer supported in the Chrome browser (or other modern browsers). That means that the deeplink navigation will not just mean a navigation to another URL from Forms – one that can be opened in the same browser. It also entails starting up Google Chrome – basically executing a command line operation (chrome.exe <target url>) on the user’s client system on which the Forms application is running inside Internet Explorer.

note: alternatively, Forms can be run in the IE Tab addin for Google Chrome (see https://www.ietab.net/ie-tab-documentation for more details). IE Tab can be configured in Chrome in such a ways as to ensure that only specific URLs are opened in IE Tab and all others in regular Chrome. That would mean that by setting up IE Tab and configuring it to only run the URLs associated with the current ERP system, all URLs accessed from Forms will be opened in a regular Chrome page – exactly the behavior we are looking for. After some initial experiments, we decided to abandon the IE Tab option because it seemed to introduce quite a management effort.

Clearly, this is not a trivial challenge. Apps running in a browser are not supposed to reach out to the client’s operating system. They run in a sandbox. They’re restricted to running in your browser and accessing a limited set of resources — they can’t view your webcam without permission or read your computer’s local files. If websites you visit weren’t sandboxed and isolated from the rest of your system, visiting a malicious website would be as bad as installing a virus. So this sounds like the end of the story.

Well, fortunately it’s not. The WebUtil library – a fairly recent article with some useful resources can be found here – is a utility that any Forms developer could use to carry out the complex tasks on the client browser machines by simply coding PL/SQL. One feature available in WebUtil is to run Host commands on the client machine – which is what I need. Through WebUtil, we can run an operating system host command on the end user’s client machine to launch Google Chrome with a specific URL:

chrome http://special_url?param1=something&param2=something_else

We need to be able to extract the relevant key from the Windows Registry order to learn how to run chrome.exe.

Call out from Form to Operating System to Read Windows Registry Key and to Open Chrome for a specific URL

This PL/SQL procedure uses webutil_host.blocking (to run a host command on the client machine) and wait for the result) as well as webutil_host.non_blocking to run a host command in the background. It also uses webutil_host.get_standard_output() to retrieve the result of running the blocking host command into the PL/SQL program.

function EXEC_CMD
(command varchar2) 
return varchar2 
IS     
  stnd     WEBUTIL_HOST.OUTPUT_ARRAY ;
  error    WEBUTIL_HOST.OUTPUT_ARRAY ;   
  process  WEBUTIL_HOST.PROCESS_ID ;
  result varchar2(10000); 
BEGIN      
  process := WEBUTIL_HOST.Blocking(command);
  stnd := WEBUTIL_HOST.Get_Standard_Output(process);
  If stnd.count > 0 
  Then         
    For i in stnd.first .. stnd.last Loop
     result:= result || stnd(i) || CHR(10) ;
    End loop ;
  End if ;  
  error := WEBUTIL_HOST.Get_Standard_Error(process);
  If error.count > 0    
  Then         
    result := result || ' !!! E R R O R !!!' || CHR(10) ;
    For i in error.first .. error.last Loop
     result := result || error(i) || CHR(10) ;
    End loop ;
  End if ;
  If process.handle is not null  
  Then               
    WEBUTIL_HOST.Release_Process(process);
  End if;
  return result;
END EXEC_CMD;

This generic procedure is invoked by the specific procedure that handles the deeplink navigation:

procedure deeplink_adf 
( p_target_page in varchar2
 , p_param1 in varchar2 default null
 , p_param2 in varchar2 default null )
is   
  l_registry_query_result varchar2(1000);
  --TODO:  this registry query key should be read from a system parameter     
  l_registry_query varchar2(100) := '"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\chrome.exe"';
  l_command  varchar2(500) := 'REG QUERY '||l_registry_query||' /S /v Path' ;
  l_terminal varchar2(100):= pkg_get_object.fn_parameter_char_value('TERMSITE');   
  --TODO: a deeplink URL should be constructed into ADF application     
  -- it looks like http://127.0.0.1:7101/ourerpapp/faces/controller?targetPage=contract&param1=6271&lc=UQIA     
  -- the host and port should be read from a system parameter
  -- the targetPage and param values depend on the deeplink that is activated
  l_deeplink_url varchar2(200):= 'http://host:port/ourerpapp/faces/controller?targetPage=';  
  l_chrome_path varchar2(500);   -- default value on my dev machine :='c:\program files (x86)\Google\Chrome\Application';     
  l_chrome_command varchar2(1000);
  l_temp varchar2(1000);
  l_special_code varchar2(10) := 'AQE';
begin   
   l_registry_query_result:=  EXEC_CMD(l_command);  
   -- retrieve chrome path from result     
   -- then execute chrome.exe in that path  
   --l_chrome_path := extract_path_from_query_result(l_registry_query_result); 
   --HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\chrome.exe 
   -- --    Path    REG_SZ    C:\Program Files (x86)\Google\Chrome\Application 
   -- --End of search: 1 match(es) found.
    -- to run Chrome for a specific deeplink url: ; note: we need to determine whether this call is blocking or nonblocking; nonblocking seems logical  
    -- look for REG_SZ as the starting point for the entry we are looking for   
    l_temp := ltrim(substr(l_registry_query_result, instr(l_registry_query_result, 'REG_SZ')+6));   
    -- look for a carriage return character (ASCII code 10) for the end of the entry   
   l_chrome_path := substr(l_temp,1, instr(l_temp, chr(10))-1);   
   -- what remains is the path where chrome.exe can be started 
   l_chrome_command:= '"'||l_chrome_path||'\chrome.exe" '||l_deeplink_url||p_target_page||'&param1='||p_param1||'&param2='||p_param2;
   -- pr_display_info('run chrome '||l_chrome_command);
   webutil_host.nonblocking(l_chrome_command ); 
end deeplink_adf;

Note: we need to verify that the form running on the client (VDI) of an end user has the required privileges to execute these operating systems commands and read directly from the registry. 

Enable WebUtil in Form – add from webutil object library

In order to make webutil work – we have to go through a few simple steps in Forms Builder, for each form that we want to add an outbound deeplink to.

Open Forms Builder and open the form to which you want to add WebUtil functionality.

Add webutil as an attached library:

Open the webutil.olb object library in Forms Builder:

Open the node Object Libraries. Double click on the WEBUTIL node. The Object Library property palette opens. Drag the WEBUTIL object group from the property palette to the Object Groups node in the target form:

Click on the Copy button in the popup that asks Do you want to copy the object or subclass it?

When the copy action is done, a number of objects – alerts, data block, canvas and object group – have been added to the form, as shown in the next figure:

You can now close the WEBUTIL object library. The form can now be compiled and run.