A Simple Oracle Query Form

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,

 First step in applying for a new Oracle Developer is not an easy step. Since applying for this job needs you not only to be familiar with the whole track courses, but technically it needs you to be familiar with what makes the work goes forward.

Today, I am going to explain you one of the most common tasks that you should be acquainted with before applying for a new job.

 Furthermore, you should never miss the query form enhancements i added at the end of this article. It simply facilitates the user input in re-query different criteria.

ask Definition:   T

    Create A Simple Query Form Manually.

Task Solution idea: 

Initially, we all had learned from Oracle documentations, tutorials, or even at educational centers from the very beginning steps how to query data from the database into an Oracle form as follows:

·        In Web-based forms the user press CTRL+F11 function key to retrieve all data from the database.

·        Or press the F11 function key to filter data in Enter Query mode. Then, Press the CTRL+F11 function key to execute the query.

 But today we will learn how to customize a query form in more useful and practical way. The idea of the task solution is based on:

·        Create one master control block with ' Lov' for a search criteria input.

Note: A control block is a block that is not associated with any database table. Items in a control block do not, by default, relate to any database columns.

·        Create a muti-record detail database block.

·        Create a relation between the two previous blocks.

Task Solution: 

1.     Create a master control block (database= yes) to accept retrieving data from the detail block. Hence, no data source specified in control block property.

2.     Create two List of Value ' Lov'  for two non database item e.g. Semester_Year  and Class_Id; these two text item will copy the value entered or selected from ' Lov'  to the detail block.

3.      Create a detail data block:

o   You must select both Semester_Year  and  Class_Id with other related table items.

o   It is also recommended to make both Semester_Year and Class_Id for the detail block invisible.

4.     Create a relationship between the two blocks with having e.g. Semester_Year and Class_Id (will do the job of Copy Value from Item property) for filtration of data.

5.     Create a PRE-QUERY trigger in the detail data block :

The following code will help to pro-grammatically filter the retrieved data from the related database.

SET_BLOCK_PROPERTY ('SECOND_BLOCK_NAME', DEFAULT_WHERE, 

'Semester_Year = ' ||:FIRST_BLOCK_NAME.Semester_Year || ' AND Class_Id = ' ||:FIRST_BLOCK_NAME.Class_Id); 

6.     Change the Master's block property:

 Navigation Style > Change Data Block.

7.     Create a WHEN-NEW-BLOCK-INSTANCE trigger in the detail data block

       EXECUTE_QUERY;

Query Form Enhancement    

Now it's time to add some enhancement to your form to avoid an expected issue you will face on re-querying other search criteria...

       

When a set of record displayed in the detail block, the user will definitely need to choose another value once again from LOV existed in Master block. Consequently, the relationship between the control block and the detail block will not work since copy value from item will not change and the old set of records will keep appearing in the detail block.

At Header Block create WHEN-NEW-BLOCK-INSTANCE Trigger and paste the following sequential built-ins:

GO_BLOCK('Detail_Block');

CLEAR_BLOCK;

GO_BLOCK('Header_block'); 

All you have to do is to rename the form blocks upon yours.

 
Remember, criticizing your failure and work by all the means to pass through the barriers is a self building esteem to your career path. The profound desire to solve problems and learn new development techniques is really a good sign of an experienced developer you will find later in yourself.  

  

    Learn more about:

·        PDE-PLI018 Could not find library NAME

·          Fingerprint Time Attendance Clock Tips  

·          Calling a secured Oracle 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.