Monday, June 8, 2015

Oracle Forms Data Exchange
"Global Variable"


In the name of Allah, Most Gracious, Most Merciful
Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that

   One of the essential tasks that an Oracle developer is commonly used is passing data  between two forms

 There are Four different ways for forms data exchanges

1.     Global variables.
2.      Parameter Lists.
3.     Global Record Groups.
4.     PL/SQL variables in shared libraries.

 All above methods can pass data between two forms by one way data pass from form A to form B But, in such cases, you may need to pass data two ways data exchanges; meaning you can pass a value from form A to Form B and the vice versa form B to 
form A.

Only Global variables can accomplish this task if they are correctly recognize how to use them correctly. 


ask Definition:T

Passing data exchange between two forms. 

Assuming we have a Student Reservation System, the system user is currently opening a reservation form. Let's assume the following scenario:

1.     A new student is coming to reserve a specific course. 
2.     The user will need to register the new student's personal data in the students form. 
3.     Then, the system user will need to call the reservation form with the new registered student's id and name, with no need to rewrite them once again, in the reservation form to complete other related student reservation data.

Task Solution Idea: 

  Global variables is the task solution for such a requirement. 

Global Variable Limitations :

·        Can contain a maximum of 255 characters datatype.
·        Each Global variable Must has the same name to each form session.
·        Global variables can be created by a PL/SQL assignment, or by the DEFAULT_VALUE built-in.

The following Solution Algorithm has two stages for passing data between two forms a reservation form and Students form to show you how it works... 

 Stage 1: The following steps are one way data pass.(reservation to students)

1.     Call the students form From Reservation form. 
2.     In the students form, initialize two Global variables with the  student_id  and student_name. 
3.     In the students form, assign the student_id to and student_name to two Global variables.
4.     In the reservation form, Assign the two Global variables storing the student_id and student_name value to the two form items student_id and student_name
5.     Then, Erase the two Global variables to avoid repeating displaying old data.

 But perhaps you want to edit or update the address or telephone number of the current reserved student for emergency cases. In this case you have to reverse the rudder
How...? Repeat the same previous steps as follow:

 Stage 2: The following steps are one way data pass.(reservation to students)

1.     In the reservation form, initialize two Global variables with the  student_id  and student_name. 
2.     In the reservation form, assign the student_id and student_name to the two Global variables.
3.     Call the students form for editing. 
4.     In the students form, Assign the two Global variables storing the student_id and student_name value to the two form items student_id and student_name
5.     Then, Erase the two Global variables to avoid repeating displaying old data.

First, we have to recognize how Global variable works according to a specific characteristics and limitations as  follow:


Task Solution: 

     Stage 1one way data pass (reservation to students)       
  1. Call the students form From Reservation form. Using "The Parameter List" 
  2. In the students form, initialize two Global variables with the student_id  and student_name. I would suggest:
    •       Form-Level: when-new-form-instance trigger or Pre-Form  trigger.
    •       Block-Level:  when-new-block-instance trigger    
               
  :GLOBAL.student_id := TO_CHAR(:students.student_id ); 

   :GLOBAL.student_name := :students.student_name 


  ·        You can either initialize a Global variable to Null value

DEFAULT_VALUE(NULL, 'GLOBAL.variable_name'); 
    
  ·        Or You can initialize a Global variable to a blank value.

 DEFAULT_VALUE(' ', 'GLOBAL.variable_name'); 

Note: Null or blank value is almost One, the same as null. It's just two different syntax to initiate a null value.
  
 ·        You can  also initialize a Global variable to a character value.

DEFAULT_VALUE('Egypt', 'GLOBAL.variable_name'); 

·        You can initialize it externally to a Number using a character format mask.


 ;(GLOBAL.variable_name := TO_CHAR(33:

·        You can initialize it to a Number but with  a single quote.

;'GLOBAL.variable_name := '33:

 ·        You can also rely on form implicit conversion.
     
   :GLOBAL.variable_name := 33 ;

         
       3. In the students form, assign the student_id to and student_name to two Global variablesChoosing the trigger depends upon your business logic or your requirements. I would suggest using:
      
    •        Form-Level:  when-new-form-instance trigger or Pre-Form  trigger.
    •       Block-Level:  when-new-block-instance trigger        

 BEGIN
   :GLOBAL.student_id       := TO_CHAR(:students.student_id ); 
     :GLOBAL.student_name := :students.student_name 
 END; 

      Note: Global variables accepts ONLY character data types.
       
       4. In the reservation form, Assign the two Global variables storing the student_id and student_name value to the two reservation form items student_id and student_name


 BEGIN
   TO_CHAR(:reservation.student_id ):=   :GLOBAL.student_id 
    :reservation.student_name  := :GLOBAL.student_name 
 END; 
         

  Note: Since Global variables are always char datatype then, you will need to cast it's stored values back to its initial data type e.g. student_id of NUMBER data type should be converted to number datatype using  TO_NUMBER() built-in function. 
 Oracle recommends explicit conversions, rather than rely on implicit or automatic conversions for these reasons:
  

         5. Then, Erase the two Global variables to avoid repeating displaying old data.


     ERASE ('GLOBAL.student_id ');
     ERASE ('GLOBAL.student_name ');

    •     Once the Global variable value is assigned to a form parameter or a form item it MUST BE DESTROYED. 

Stage 2two ways data exchanges (students to reservation )    

  1. In the reservation form, initialize two Global variables with the  student_id  and student_name. 
  :GLOBAL.student_id := TO_CHAR(:reservation.student_id ); 

   :GLOBAL.student_name := :reservation.student_name ; -- display item

     2.  In the reservation form, assign the student_id and student_name to the two Global variables. Same as previous step.

    3. Call the students form for editing "The Parameter List" . 

    4. In the students form, Assign the two Global variables storing student_id and student_name value to the two form items student_id and student_name

 BEGIN
   TO_CHAR(:students.student_id :=   :GLOBAL.student_id 
    :students.student_name           := :GLOBAL.student_name 
        Go_block('students');
       Execute_query;
 END; 
       
Now you can make the changes you want to the student's address or mobile number.
         5. Then, Erase the two Global variables to avoid repeating displaying old data.

          ERASE ('GLOBAL.student_id ');
          ERASE ('GLOBAL.student_name ');

          Honestly, "Global Variable"  is essential in this task, it looks easy and require few lines of code but generally, it has few drawbacks that makes you think twice before taking the decision to use. Still  the charms of "The Parameter List"   is the best reliable technique to choose when passing data between forms.  


    Hope it Helps…


    My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.