Data Validation

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,

        A reliable application, relies on data validation methodology. It involves     

  recognizing the business rules and applying the five  types of data validations. 

 Data Validation Types:

1.     Code validation 

2.      Data type validation 

3.      Data range validation

4.     Constraint validation

5.      Structured validation

Task Definition:   

    Today's task is to use data validation in a specific formula. This requires you to check out the input of the left hand side must logically equal the right hand side of the mathematical equation.
   
     Let's assume that we have an estimated plan for issuing the total annual quantities of the raw material for production in a factory. Then, the system should check for accurate user input before saving; the estimated total quantity must equal all raw material quantities entered by the user. 

e.g.   Total =  item1 +  item2 +  item3 

 If the logical equation violated, then you must prevent the user from saving the data into the database.

Task Solution:

 There are two ways to apply a solution for this task:

1- Create WHEN-VALIDATE-RECORD Trigger (block level)

DECLARE

    v_total   NUMBER  :=0 ;

BEGIN

     

     v_total  := NVL(:item1,0) + NVL(:item2,0) + NVL(:item3,0) ; 

IF tot  <> :total THEN

     -- Create a message or Alert here 

MESSAGE('Quantities Don't Match');

PAUSE;

 RAISE FORM_TRIGGER_FAILURE;

END IF;

END;

If  the data validation is accurate, it works fine. Otherwise the data condition will be violated and the trigger will fire to Prevent inaccurate data from saving the form.

2- Create PRE-INSERT or PRE-COMMIT Trigger ( form level)

DECLARE

     v_total  NUMBER  :=0 ;

BEGIN

    v_total  := NVL(:item1,0) + NVL(:item2,0) + NVL(:item3,0) ;

IF v_total  <> :total THEN

     -- Create a message or an alert here 

MESSAGE('Please Check Related Quantities to FormItem1');

PAUSE;

-- To prevent a user fro saving the violated condition use...

     RAISE FORM_TRIGGER_FAILURE;

END IF;

END;

Learn more about:

           Oracle Report With No Data

·          Oracle Forms Data Exchange

·           

Hope this helps...

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

A Report With No Data

  A Report With No Data

  

       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,

        The Oracle Report is a visual representation of the company data stored in one or more database tables or views. If your report is running correctly, this means, your code behinds calling this report works just fine. In fact, It is just all about the 
execution of a code to achieve the report mechanism of calling it.

        On the other hand, a report running with no data does not mean your report is at the save mode. It's only a warning message that denotes one or more ambitious problems is waiting for you. Even though it is only one problem, but it.causes it has so many reasons 

    Today's task in shaa Allah, I will list the reasons for displaying a report with  no  data even though there is a data stored in the database.

Problem Definition:   

      A report with no data.

Problem Reasons:

 7 Reasons Leads To A Report With No Data:

          1.        No data stored into the database.

         2.        Invalid report query conditions.

         3.        Incompatible parameter name.

         4.        Incompatible parameter types.

         5.        The parameter numbers are not equal.

         6.        Invalid data input.

         7.        Invalid report name.

Problem Task Solution:

7 probabilities for a solution to a report with no data:

•    Execute the report query into SQLPLUS or Toad to check for data. If it displays data then the problem exists either in the report or the parameter form.

•     Comment each suspected condition at the where clause query line by line.Then, re-execute the query. Repeat these steps until you get the data displayed.

                

      Start inspection through checking up these questions with answers:

                         i.   Are the database tables' constraints and relationships correct?

                       ii.   Is there any incorrect data inputs by the user himself?  
               iii.    Is  the query parameters are logically correct?

               iv.     Is the  query parameters format mask matches?  

                  v.    Is the specified value for the parameter date has data?

              vi.   Is there any irrelevant conditions in the where clause of the report's query.

3.        Mismatch Parameters' Names : 

      This means the report parameter name itself is not the same one as on the parameter form or page. You may have:

           ·        A spelling mistake; So you have to make sure that both names are the same. You may use, copy & pate just to assure equality on both sides.

          ·       One single extra space; using copy and paste may lead to one or more extra character space.
         ·  A colon character, You may copy the parameter preceding colon ':'  in the report query as follow:

 SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'paramform=no'

                                      ||' :P_DEPT_ID='|| TO_CHAR(:DEPTS.DEPT_ID));

 Note:  The report will also display with no data just in case the report parameter has no initial value. Otherwise, it will display data upon the initial value.

4.        Incompatible Parameter Types: 

        The report parameters' types MUST BE compatible with the parameter form's parameters according to Oracle Explicit & Implicit Data Type Conversion rules, e.g. Oracle treats 1500 the same as '1500'. But using TO_NUMBER  function in a  string contains non-numeric characters, the function returns an error. 

 SET_REPORT_OBJECT_PROPERTY(v_report_id,REPORT_OTHER, 'paramform=no'

                                               ||' :P_DATES='|| TO_CHAR(:EMP.HIRE_DATE, 'DD-MM-RRRR'));

Note:  All parameters in the parameter form called especially date & number parameter data type MUST BE in character data type. Otherwise, no data will be displayed. If  NOT the form item datatype is not defined as the character data type you MUST convert it using TO_CHAR()  as giving in the example.

5.        Unequal parameters' numbers: 

      The number of a report parameters referenced in the report query  MUST BE equal to the parameters' form numbers referenced in the parameter form's code. This involves one or more  extra parameter report not includes in the parameter  form or the vice versa .You have to count them and remove any redundant or extra parameters.

6.        Invalid Data Input:

       This may occur from the application user or the oracle developer or the application testers; they may input incorrect data when calling a report from the parameter form, a developer, e.g. may write the report query contains a range of  date  parameters as the following:

  

WHERE INVOICE_DATE BETWEEN :P_START_DATE AND :P_END_DATE

  

The wrong date input:

From: 15-01-2017   To: 01-01-2017   

  

The correct date input:

From:   01-01-2017    To: 15-01-2017

Note: 
         The date range is reversed, so you can solve this problem if you correctly reverse the wrong date range to the correct one. A good developer can expect this problem and handle it through writing a date range validation on each item on the parameter form to enforce the user to input the correct logical date range and etc.

7.        Invalid Report Copy:

         This case rarely happens, a developer may mistakenly replace an old report copy of the new one. So you have to pay more attention to this mistake. So you have to test and review all report copy from the correct working folder and the valid path. Making sure you have the correct generated run time copy of both the page or form and the report. 

Learn more about:

·        Calling Oracle Report 10G
·        Oracle Arabic Report 10G

Hope this helps...

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

 Auto-Refresh Data Block Timer's Trick  

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,

     You may work in a critical work area where the change of data on time means a significant importance of the business data flow work progress.


    Data change controls the logical work flow the user expects 

      The start or the end of customer business transaction like:

• The bank customer's queue needs an instance data refresh with the flow of  the in-coming bank-customers to determine the next customer's transaction or its end. 
• The patient's role management control for doctors and nurse station in a medical clinic depends on the data of patient's attendance time. 

    Data change motivates your customer to make his purchasing decision.

       The daily or instance data change controls the client's decision like: 

• The instant Stock prices change for shareholders needs a dynamic update or an auto-refresh for data blocks.
• The daily foreign currency trading in the exchange companies and banks.

       
        Consequently, it is a significant task we need for a specific needs to fulfill the  requirements of your system application user.  On the other hand, Oracle form has not a direct control to auto-refresh you data block. But it gives you a free use of a timer where you can use it in different purposes.  Thus, let's start some simple work around using Oracle Forms' Timer.  

Task Definition:   

       Data Block Auto-Refresh

Task Solution:

        Initially, we need to know that the  timer is not an object but it is actually an internal clock you pro-grammatically create and manipulate displaying data automatically. So let's create a timer as follow...

Create a Timer:

1.    Declare a variable of timer data type.
2.      Initialize a variable with one second number data type.
3.      Use a CREATE_TIMER built-in with the timer's name, interval and it's status  repeats every one second on timer's expiration.
4.      Assign the timer's creation built-in to a variable of timer data type. 
5.   In  WHEN-NEW-FORM-INSTANCE trigger Form-Level paste the following:    

DECLARE
   timer_id   Timer;
   one_sec    NUMBER (5) := 60000  ;   -- 1 minute in milliseconds
BEGIN
   timer_id := CREATE_TIMER ('MYTIMER', one_sec, REPEAT);
END;

                                    

    

       Delete a Timer:

       
       In timer's creation and expiration one second  created, executed in regards to current system date. It repeats itself endlessly. 
Paste the following code in When-Timer-Expired Trigger Form-Level :

BEGIN

EXECUTE_QUERY(NO_VALIDATE);

END; 

       

         

       In the previous example, i assumed you have one data block with multi-records.

       Now, you can compile, generate and  run the form, if it works then save your form as a ready made example you can refer to in a need.

       Let's assume, you have two  data blocks, you have to specify a logical master-detail relationship according to your table database master and detail relationship.
 Then, paste the following code in When-Timer-Expired Trigger Form-Level :

BEGIN

Go_block('block_name');

EXECUTE_QUERY(NO_VALIDATE);

END; 

   

      This is a simple data block refresh example without any restriction or conditions. 

If you want to Filter the data display into your block, then You have to follow one of the two ways:

• Block Property: The master data block property > where clause.
• Pro-grammatically: using the default_where clause.

     If you want to Order the data display into your block, then You have to follow one of the two ways:

• Block Property: The master data block property > Order By clause.
• Pro-grammatically: using the Order by using SET_BLOCK_PROPERTY built-in.

  

 Learn more about:

         ·         Oracle Forms Errors & Informative English Script Messages

         ·        Oracle Database Installation Environmental Path Error
   ·     Oracle Form Global Variable Recognition

        ·       In-Direct Global Variable Access
   ·     Menu Item Enable/Disable

Hope this helps...

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