Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
28 août 2006 1 28 /08 /août /2006 22:19

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).

Partager cet article

Published by Oracle Forms community - dans Bean - general
commenter cet article

commentaires

Francois 30/08/2006 14:15

Mike,This is only an example to test the bean. In the real life, it is sure that the DB password would probably be stored (and encrypted) in the database.The purpose of this bean is to demonstrate that you can use a Forms application to start a function/procedure without freezing the screen for minutes or hours like you can do with Oracle Jobs.

Mike Friedman 30/08/2006 12:47

This does not seem very useful if you need to modify every client in order to have the java bean connect back to your DB and if it requires a separate DB connection that does not go through the Application Server.
For example, where is your bean going to get the DB password from?
And do you even want your clients to be able to directly connect to the DB?

Oracle Forms community 09/10/2006 16:56

Concerning the user/password, they probably are the same as them used for the current Forms connexion. So you do not store anything, but you just use the Get_Application_Property() built-in to get both username and password  ;o)