Purpose

Here is a Java Bean that allows to start asynchronous jobs from an Oracle Forms application.

When you execute a stored procedure or function within a Forms application, the end user cannot get the hand until the procedure/function is completly finished, so the application seems to be "frozen".
By using a class that implements the Runnable interface, it is possible to run a job in its own thread, so the end user can continue to work while the execution of the stored function.

The JavaBean can connect to the database through the JDBC driver, execute the stored procedure/function, then call back Forms to indicate the end of the job.

The Java code

     asyncjob.java


The implementation class of the Bean Item

     oracle.forms.fd.AsyncJob


The methods you can call

Set the connection string

Set_Custom_Property('BLOCK.ITEM',1,'INITCONN','the_connection_string');

e.g. :
Set_Custom_Property( 'BL.BEAN', 1, 'INITCONN', 'jdbc:oracle:thin:@my-machine:1521:XE' ) ;   
(Provide the complete jdbc:oracle:thin:... syntax)


Set the username

Set_Custom_Property('BLOCK.ITEM',1,'INITUSER','user_name');


Set the password

Set_Custom_Property('BLOCK.ITEM',1,'INITPWD','password');


Set the call of a function

Set_Custom_Property('BLOCK.ITEM',1,'INITFUNC','the_function_call');

e.g.:
Set_Custom_Property( 'BL.BEAN', 1, 'INITFUNC', 'begin ? := F1(5); end;' ) ; 


Set the call of a procedure

Set_Custom_Property('BLOCK.ITEM',1,'INITPROC','the_procedure_call');

e.g.:
Set_Custom_Property( 'BL.BEAN', 1, 'INITPROC', 'begin P1(5); end;' ) ; 



Set the log mode to output the Bean messages

Set_Custom_Property( 'BL.BEAN', 1, 'SETLOG' , 'true|false' ) ;


In the sample dialog provided with the article, I use 2 buttons:

- One to execute a stored function:

  If :BL.CONN_STRING is not Null
      And :BL.USERNAME is not Null
      And :BL.PASSWORD is not Null Then
    Set_Custom_Property( 'BL.BEAN', 1, 'SETLOG' , 'true' ) ;
    Set_Custom_Property( 'BL.BEAN', 1, 'INITCONN', :BL.CONN_STRING ) ;   
    Set_Custom_Property( 'BL.BEAN', 1, 'INITUSER', :BL.USERNAME ) ;
    Set_Custom_Property( 'BL.BEAN', 1, 'INITPWD' , :BL.PASSWORD ) ;   
    Set_Custom_Property( 'BL.BEAN', 1, 'INITFUNC', 'begin ? := ' || :BL.SQL_ORDER || ' end;' ) ; 
    Message('Command sent'); synchronize;
  End if ;   

- Another to execute a stored procedure:

  If :BL.CONN_STRING is not Null
      And :BL.USERNAME is not Null
      And :BL.PASSWORD is not Null Then
    Set_Custom_Property( 'BL.BEAN', 1, 'SETLOG' , 'true' ) ;
    Set_Custom_Property( 'BL.BEAN', 1, 'INITCONN', :BL.CONN_STRING ) ;
    Set_Custom_Property( 'BL.BEAN', 1, 'INITUSER', :BL.USERNAME ) ;
    Set_Custom_Property( 'BL.BEAN', 1, 'INITPWD' , :BL.PASSWORD ) ;   
    Set_Custom_Property( 'BL.BEAN', 1, 'INITPROC', 'begin ' || :BL.SQL_ORDER || ' end;' ) ;
    Message('Command sent'); synchronize;
  End if ;   

The event that can be raised from the JavaBean

   EVENT

It indicates to Forms that the procedure/function is finished.
You can catch this event in the WHEN-CUSTOM-ITEM-EVENT trigger of the Bean item:

Declare
    LC$Error   Varchar2(4000) ;
    LC$Result  Varchar2(32000) ;
Begin   
  clear_message ;
  LC$Error  := Get_Custom_Property('BL.BEAN',1, 'GETERROR') ;
  LC$Result := Get_Custom_Property('BL.BEAN',1, 'GETRESULT') ;
  If LC$Error is not null Then
       Message( 'Error : ' || LC$Error ) ;
  Else
     Message( LC$Result, no_acknowledge);
  End if ;
End ; 

As you can see, the 2 properties you can get from the JavaBean are the return value (GETRESULT) and the error message (GETERROR) provided by the execution.

For this case I have created one stored function and one stored procedure like the following:

CREATE OR REPLACE
PROCEDURE P1 ( PN$Param IN NUMBER )
IS
BEGIN
  DBMS_LOCK.Sleep( PN$Param ) ;
END;
/

CREATE OR REPLACE
FUNCTION F1 ( PN$Param IN NUMBER )
RETURN VARCHAR2
IS
BEGIN
  DBMS_LOCK.Sleep( PN$Param ) ;
  RETURN ('Function ended at ' || To_Char(SYSDATE,'HH24:MI:SS') );
END;
/

As you can see, they do nothing else than waiste some time.
You can get the creation source in the asyncjob.sql provided with the sample.

To compile this code, the user must have the execute privilege on the SYS.DBMS_LOCK package granted.


Accessing to a remote database

Because the JavaBean is executed within an applet, it is not possible to connect to a remote database without a little adaptation:

   - first, the jar file must be signed.
   - second : you have to update the java.policy file stored in the Jinitiator directory

For example, to access to the database located on the machine-name server on the port 1524, add the following lines to the java.policy file:

C:/Program Files/Oracle/JInitiator 1.3.1.xx/lib/security/java.policy file
 
permission java.net.SocketPermission "machine-name:1524-", "accept,connect,listen,resolve";	
permission java.security.AllPermission;


The sample dialog

asynchronous job


     . Download the asyncjob.zip file
     . Unzip the file
     . run the asyncjob.sql under the Oracle user you want to test (It creates the 2 small stored function/procedure).
     . copy the asyncjob.jar file in the <ORACLE_HOME>/forms/java directory
     . Edit your /forms/server/formsweb.cfg file to add both asyncjob.jar and classes12.jar (the classes12.jar is located in the <DEV_HOME>/jdbc/lib directory. add a copy of this file in the <DEV_HOME>/forms/java directory).
     . Open the ASYNCJOB.fmb module (Oracle Forms 10.1.2)
     . Compile all and run the module


Note : If you rebuild the jar file, it has to be signed. (the asyncjob.jar file provided in this article is already signed).