Overblog
Folge diesem Blog Administration + Create my blog

Do it silently

27. Februar 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

Another often asked question from the forum

How can i supress that message FRM-40401..... when doing some programmatic COMMIT_FORM ? And its not only the COMMIT_FORM, it could also be EXECUTE_QUERY with the message FMR-40350...

The logic behind

Each message or error forms raises has a message-level assigned (something like a "ranking"). And there is a forms-setting what is the "actual" level the forms-session deals with. This is stored in :SYSTEM.MESSAGE_LEVEL. Now, for every message or error to be shown, forms checks the messages message-level against the "actual" level. The message or error is raised only if the messages message-level is equal or bigger as the system-setting. The actual raising of messages and errors occurs by firing the ON-MESSAGE-triger for messages and ON-ERROR for errors.

An example
  • The user clicks on the save-symbol in the smartbar, there are no changes to save.

  • Forms now would show the "FRM-40401 No changes to save"-message, which has a message-level of 0.

  • Forms checks this message-level (0) against the SYSTEM.MESSAGE_LEVEL (which is by default also 0). Because 0>=0, forms fires the ON-MESSAGE-trigger (if existing) with message_type='FRM' and message_code=40401, or it simple displays the message in the message-bar.

How to influence the behaviour - Permanent solution

If the requirement is to supress a message completely, you have to "overwrite" the message or error-handling by implementing the ON-ERROR or ON-MESSAGE-trigger. To stay with the example, lets say we want to supress the FRM-40401 permanently. So we implement our own ON-MESSAGE-trigger:

BEGIN
  IF     MESSAGE_TYPE='FRM'
     AND MESSAGE_CODE=40401 THEN
    NULL;
  ELSE
    MESSAGE(MESSAGE_TYPE || '-' || MESSAGE_CODE || ': ' || MESSAGE_TEXT);
  END IF;
END;

So we just filter the specific message. Running through the example:

  • The user clicks on the save-symbol in the smartbar, there are no changes to save.

  • Forms now would show the "FRM-40401 No changes to save"-message, which has a message-level of 0.

  • Forms checks this message-level (0) against the SYSTEM.MESSAGE_LEVEL (which is by default also 0). Because 0>=0, forms fires the ON-MESSAGE-trigger with message_type='FRM' and message_code=40401

  • Our ON-MESSAGE-trigger just "consumes" the message and nothing is shown to the user.

How to influence the behaviour - Only for specific situations

The other requirement is to supress some messages only in specific situations, e.g. when doing some automatic COMMIT_FORM. Thats the moment to adjust the SYSTEM.MESSAGE_LEVEL up to a level higher than the message(s) to be supressed, do the action and then readjust SYSTEM.MESSAGE_LEVEL again. This would look like

BEGIN
  :SYSTEM.MESSAGE_LEVEL:=5;
  COMMIT_FORM;
  :SYSTEM.MESSAGE_LEVEL:=0;
END;

Running through the example again:

  • The user clicks on the save-symbol in the smartbar, there are no changes to save.

  • Forms now would show the "FRM-40401 No changes to save"-message, which has a message-level of 0.

  • Forms checks this message-level (0) against the SYSTEM.MESSAGE_LEVEL (which is now 5). Because 0>=5 is not true, forms does nothing more.

Encapsulating the logic

As i'm a fan of encapsulation, i created a procedure for the committing logic and have it in my pll:

PROCEDURE PR_SILENT_COMMIT IS
   nMessageLevel NUMBER:=NAME_IN('SYSTEM.MESSAGE_LEVEL');
BEGIN
   COPY('5', 'SYSTEM.MESSAGE_LEVEL');
   COMMIT_FORM;
   COPY(TO_CHAR(nMessageLevel), 'SYSTEM.MESSAGE_LEVEL');
END;

The same also exists for silent execute-query ....

 

 

Weiterlesen

Calling a form and passing a context

20. Februar 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

One common task in a forms-application is call one form form another one and give some context-information to that new form when calling it. In some cases you also want to get a kind of "return-value" when the called form is finished to that you can react on it. There are several solutions for this task i will describe here:

Test-case

For the examples i take a simple application consisting of two forms-modules, both based on the EMPLOYEES-tables form the HR-demo-schema. The first form, i call it OVERVIEW is a tabular form where you can query data from the employees-table, but cannot change anything. Beneath the table there are two buttons, one called "Edit" and one called "New". Both will call the second form i call EDIT_EMP which can either be used to edit an existing record form the EMPLOYEES-table, or to create a new employee. What action should be possible in the second form is given as context from the OVERVIEW-form.
In detail,

  • when pressing the Edit-button i want to give the EMPLOYEE_ID from the selected record to the EDIT_EMP, along with the information to edit that record
  • when pressing the New-button i want to call EDIT_EMP with a blank record which enables the user to enter data for a new employee.

In the examples i will deal only with CALL_FORM (for first). There are some issues with OPEN_FORM which are similar (or equal) but also some which may differ.

Use parameters to pass a context

One approach of passing a context from one OVERVIEW to EDIT_EMP is to use Parameters.
To do that create two parameters in the Object-Navigator in EDIT_EMP:

  • P_EMPLOYEE_ID with datatype Number
  • P_MODE with datatype Char

To give a context from OVERVIEW to EDIT_EMP you now have to create a parameter-list, fill in the appropiate parameters and values and pass the parameters when calling the EDIT_EMP.

Here the example-code for the WHEN-BUTTON-PRESSED-trigger on the Edit-button in OVERVIEW:

DECLARE
  pl PARAMLIST:=GET_PARAMETER_LIST('PL_EDIT_EMP');
BEGIN
  -- Delete list if existing
  IF NOT ID_NULL(pl) THEN
    DESTROY_PARAMETER_LIST(pl);
  END IF;
  -- Create new
  pl:=CREATE_PARAMETER_LIST('PL_EDIT_EMP');
  -- Add parameter for Mode
  ADD_PARAMETER(pl, 'P_MODE', TEXT_PARAMETER, 'EDIT');
  -- Add parameter for EMPLOYEE_ID
  ADD_PARAMETER(pl, 'P_EMPLOYEE_ID', TEXT_PARAMETER, :EMP.EMPLOYEE_ID);
  -- Now call the second form, giving the parameter-list
  CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY,pl);
END;


And now in EDIT_EMP you have to "take over" the parameters and react on them. A good trigger to do so is the WHEN-NEW-FORM-INSTANCE-trigger. Heres the code that will react on the Edit-Mode and query the appropiate record.

IF :PARAMETER.P_MODE='EDIT' THEN
  -- go to the employee-block
  GO_BLOCK('EMP');
  -- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:PARAMETER.P_EMPLOYEE_ID)
  EXECUTE_QUERY;
  -- Restrict createing new records
  SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;


Important here is the WHERE-condition which is set at the EMP-block to restrict the query to the parameter-value.
Thats it for the parameter-approach.

Pro's:

  • You can see in the Module-definition of the called form which parameters it accepts.
  • The values of the parameters are specific to the called "instance" of the form, so if one form is called more than one time, each form can have it's own values

Con's:

  •  Parameters are one-way, means you can pass parameters when from the calling form to the called form, but you cannot return values back to the called form.
Using globals to pass a context

With this approach you make use of the Global-variable-concept in forms to pass a context. Globals are not defined at design-time, but implicitly at runtime when you first assign a value to them or use the Built-In DEFAULT_VALUE.

Lets do the same thing as before using Globals:

Heres the WHEN-BUTTON-PRESSED-trigger on the Edit-Button:

-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);


And also, you take over the context in the WHEN-NEW-FORM-INSTANCE-trigger of EDIT_EMP:

-- Make sure the Gloabls exist
DEFAULT_VALUE(NULL, 'GLOBAL.MODE');
DEFAULT_VALUE(NULL, 'GLOBAL.EMPLOYEE_ID');
IF :GLOBAL.MODE='EDIT' THEN
  -- go to the employee-block
  GO_BLOCK('EMP');
  -- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:GLOBAL.EMPLOYEE_ID)
  EXECUTE_QUERY;
  -- Restrict creating new records
  SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;



Again, the WHERE-condition at the block EMP is important to be set.
So far, the Global-approach is quite similar to the parameter-approach.

Pro's:

  • Globals can be seen by every module in a forms-session, so you can also use them to return a value from the called form back to the calling form

Con's:

  • Globals are not defined declarative so you have to look into the code
  • Globals can be seen by every module in a forms-session, so if one form is called more than one time, the second call will overwrite the globals from the first call.
Returning values from a called form

As returning a value from a clalled dialog is a common requirement, lets enhance the example and return a flag from the EDIT_EMP to indicate if the user has changed the edited employee, so that we can do a requery in that case to reflect the changes in the overview.

First, we implement the WHEN-BUTTON-PRESSED-trigger on the save-button in EDIT_EMP:

IF :SYSTEM.FORM_STATUS='CHANGED' THEN
  -- save changes, if needed
  DO_KEY('COMMIT_FORM');
  -- set "saved"-flag
  :GLOBAL.USER_HAS_SAVED:='TRUE';
  -- exit
  EXIT_FORM;
END IF;


And also the WHEN-BUTTOn-PRESSED-trigger on the cancel-button:

-- reset saved-flag
:GLOBAL.USER_HAS_SAVED:='FALSE';
-- exit without saving
EXIT_FORM(NO_VALIDATE);


Last we have to adjust the Edit-button code to react on the return-value

-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
-- Make sure the global exists
DEFAULT_VALUE('FALSE', 'GLOBAL.USER_HAS_SAVED');
-- Check retur value
IF :GLOBAL.USER_HAS_SAVED='TRUE' THEN
  -- Requery data
  GO_BLOCK('EMP');
  EXECUTE_QUERY;
END IF;

 

Encapsulating the logic

Both approaches have pros and cons and both have in common the same problems when a form is called from more than one other form:

  • The names of the parameters/globals must match on both sides.
  • The passed values must match on both sides
  • When enhancing the called form its sometimes hard to find all calling forms to adjust the call

So i took the following approach to "hide the complexity" and make it quite straight-forward to do the communication:

  1. Create a pll to contain all communication logic.
  2. For each form that can be called, create a package in it
  3. Inside each package, create the following functions/procedures
  • a procedure to call the form, having all the parameters needed to call as IN-parameters, or
  • a function to call the form, having all the parameters needed to call as IN-parameters and returning the result
  • a procedure for retrieving the callers parameters from the package to the called form.
  • a procedure to set the return-value from the called form into the package.
  • All communication with the called form in done exclusively using this package
  • In case there are different modes or variants to call a form, there may also be different "calling"-procedures/functions.

 

This approach has some (in my eyes big) advantages:

  • The way the communication between caller and calling-form is implemented is hidden and doesn't really matter to the caller
  • All usage of Global-names/parameternames is only done in this package, so the chance of having typos in it is quite small
  • Parameters are given with its "real" datatype and can even be checked before calling the form.
  • If the signature to call a form must change the signature of the package has to be changed, and with a recompile it's quite eays to find modules using the changed module, for they will no longer compile


So, lets change the example to match this approach:

First, heres the code for the package:

PACKAGE PK_EDIT_EMP IS

  -- Call the dialog to edit the employee with the given ID
  -- function return TRUE if user changed the Employee, else FALSE
  FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
  RETURN BOOLEAN;

  -- retrieve the callersparameters
  -- o_vcMode will be EDIT for editing NEW for creating a new Employee
  -- o_nEmployeeId will give the Id of the employee to be queried when o_vcMode is EDIT
  PROCEDURE PR_GET_PARAMETERS(o_vcMode      OUT VARCHAR2,
                              o_nEmployeeId OUT NUMBER);

  -- Sets the return-value                            
  PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN);  

END;

PACKAGE BODY PK_EDIT_EMP IS
 
  VCC_GLOBAL_MODE   VARCHAR2(30):='GLOBAL.EDIT_EMP_MODE';
  VCC_GLOBAL_EMPID  VARCHAR2(30):='GLOBAL.EDIT_EMP_EMPID';
  VCC_GLOBAL_RETURN VARCHAR2(30):='GLOBAL.EDIT_EMP_RETURN';
 
  FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
  RETURN BOOLEAN IS
  BEGIN
   -- Set the globals
    COPY('FALSE', VCC_GLOBAL_RETURN);
    COPY('EDIT', VCC_GLOBAL_MODE);
    COPY(TO_CHAR(i_nEmployeeId), VCC_GLOBAL_EMPID);
    -- call the form
    CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
    -- return result
    RETURN (NAME_IN(VCC_GLOBAL_RETURN)='TRUE');
  END;  

  -- ----------------------------------------------------------------
 
  PROCEDURE PR_GET_PARAMETERS(o_vcMode      OUT VARCHAR2,
                              o_nEmployeeId OUT NUMBER) IS
  BEGIN
    -- transfer globals to out-params
    o_vcMode:=NAME_IN(VCC_GLOBAL_MODE);
    o_nEmployeeId:=TO_NUMBER(NAME_IN(VCC_GLOBAL_EMPID));
  END;

  -- ----------------------------------------------------------------

  PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN) IS
  BEGIN
    -- map boolean to Char-result
    IF i_bSaved THEN
      COPY('TRUE', VCC_GLOBAL_RETURN);
    ELSE
      COPY('FALSE', VCC_GLOBAL_RETURN);
    END IF;
  END;

END;


Now the WHEN-BUTTON-PRESSED-trigger in OVERVIEW:

IF PK_EDIT_EMP.FK_EDIT_EMPLOYEE(:EMP.EMPLOYEE_ID) THEN
  -- Requery data
  GO_BLOCK('EMP');
  EXECUTE_QUERY;
END IF;


The WHEN-NEW-FORM-INSTANCE-trigger in EDIT_EMP:

DECLARE
  vcMode      VARCHAR2(4);
  nEmployeeId NUMBER;
BEGIN
  PK_EDIT_EMP.PR_GET_PARAMETERS(vcMode, nEmployeeId);
  -- to avoid using the globals directly in the form, created a item 
  -- in y control-block
  -- and restrict the query  on that
  :BL_CONTROL.IT_EMPLOYEE_ID:=nEmployeeId;
  -- go to the employee-block
  GO_BLOCK('EMP');
  -- execute the query (the block has a 
  -- WHERE-condition using EMPLOYEE_ID=:BL_CONTROL.IT_EMPLOYEE_ID
  EXECUTE_QUERY;
  -- Restrict createing new records
  SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END;



Note that for avoiding to "break" the encapsulation i created a control-block with an item to store the employee_id in.

And last, the two button "Save"

IF :SYSTEM.FORM_STATUS='CHANGED' THEN
  DO_KEY('COMMIT_FORM');
  PK_EDIT_EMP.PR_SET_RETURN(TRUE);
  EXIT_FORM;
END IF;


and "Cancel"

PK_EDIT_EMP.PR_SET_RETURN(FALSE);
EXIT_FORM(NO_VALIDATE);
Weiterlesen

Welcome

20. Februar 2009 , Geschrieben von Andreas Weiden

So, finally i started my own blog to have a place where i can put my ideas about designing and developing applications using oracle-technologies.

I would be happy if you find something useful.

Andreas
Weiterlesen