Forms: How to Base a block on a FROM Clause Query

 Collected from link :https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-how-to---base-a-block-on-a-from-clause-query

If you have worked Oracle Forms for any amount of time, you will eventually hear about basing a block on a From Clause query.  If you are like most Forms developers, you probably haven’t used this method because you were never shown how to use them or you didn’t understand the circumstances of when it would be appropriate to use this method.

What is a From Clause anyway?  Basically, a From Clause query in Forms is the same as an In-Line View in SQL; which enables you to select columns from a result set.  The query that produces a result set is referenced through the FROM clause of a query – hence the name “From Clause” query.  Here is a basic example using SQL (based on the HR demo schema):

SELECT name             ,email             ,department_name             ,manager_name FROM ( SELECT e.last_name ||', '|| e.first_name AS name                               ,e.email                               ,d.department_name                               ,m.last_name ||', '|| m.first_name AS manager_name              FROM employees e, DEPARTMENTS d, EMPLOYEES m             WHERE d.department_id = e.department_id             AND M.MANAGER_ID = E.MANAGER_ID             );

So you may still be asking yourself “Why would I want to use this in a form?”  The most common answer is when you want to base a form on a result set, but you don’t have privileges to create a view in the database or where creation of database objects if closely controlled and your request for the creation of a view would likely be denied.  The Forms “From Clause” query is the perfect solution and is easier to implement and is more efficient than building a form based on the primary table in a join and then using the block Post-Query trigger to retrieve the needed values from related tables and the From Clause query.

Using the above in-line view SQL statement, you would typically create a Forms data block based on the HR.EMPLOYEES table and then use the EMPLOYEES block Post-Query trigger to fetch the department_name and manager_name.  This method is less efficient because the Post-Query trigger just execute up to two additionally queries (causing network traffic) to fetch the department_name and manager_name from their respective tables.  Using the “From Clause” query method, there is only one network trip to the database for the necessary data for your form.

Another benefit of using the “From Clause” query is that it allows you to use SQL methods from Forms that are not supported yet.  It is common knowledge that most (if not all) Oracle tools that include their own SQL and PL/SQL parsing engines are at least one version behind the database.  Consequently, if you are connecting to the latest version of an Oracle database, you will not be to utilize the new SQL and PL/SQL features of your database in Forms because the SQL and PL/SQL engine’s in Forms do not support these features.  A good example of this is the use of ANSI Joins in an Oracle Forms 10g R2 form and connecting to an Oracle 10g RDBMS.  If you were to write a SQL statement in a trigger using ANSI joins, you will get a compilation error, however, you can write a SQL statement and assign it to the “Query Data Source Name” property of your From Clause data block and it will work just fine.  This is because Oracle Forms will send the SQL statement to the database to be parsed rather than parse it in the Form.  This is a handy feature when you don’t want or can’t create a view on the database but you need to use SQL features not supported by Forms. 

This document will walk you through the creation of a simple Oracle Form using a From Clause Query as the data source of a block.  This sample form uses the HR schema found in the default installation of Oracle 10g (or higher) RDBMS’s (demo schemas).  If you don’t have these demo schemas – consult your DBA. J

This document and the supplied sample files were written using Oracle Forms 10g R2 (10.1.2.0.2).

What are the limitations with using a FROM Clause Query?  Your data block is READ ONLY and you can not use the BLOCK WHERE Clause to further restrict your query result set.  If you need to be able to perform Insert, Update and Deletes you will have to use Transactional Triggers (On-Insert, On-Update and On-Delete) to perform the DML. 

If you need to use a WHERE clause you will have to dynamically modify the query and assign the query to the block “Query Data Source Name” property using the Set_Block_Property built-in.  I’ll provide an example later in this document.

Setup

Open Oracle Forms Builder and connect to the HR schema (User ID = HR, Password = HR) or connect to your database with a user that has SELECT privileges on the HR schema objects.

Forms defaults to opening a new Forms Module.  You can use this new module or create a new one.  Save the module as “FROM_Clause_Query.fmb.”

Build your data block

One of the keys to using a block based on a FROM Clause Query is that the item names in your data block MUST match the columns returned by your SQL query.

1. Click on the Data Blocks node of the Object Navigator and click on the Create (+) button in the side tool bar.  This will start the Data Block Wizard.
1. Select the “Build a new block manually” option and click OK.
2. Add the following items to your block and set their properties as listed:

                                                              i.      Item Name:  EMPLOYEE_ID

1.      Properties: 

a.       Data Type:  Number

b.      Maximum Length:  6

c.       Primary Key: Yes

                                                            ii.      Item Name:  FIRST_NAME

1.      Properties: 

a.       Data Type: Char

b.      Maximum Length:  20

                                                          iii.      Item Name:  LAST_NAME

1.      Properties:

a.       Data Type:  Char

b.      Maximum Length:  25

                                                          iv.      Item Name:  PHONE_NUMBER

1.      Properties:

a.       Data Type:  Char

b.      Maximum Length:  25

                                                            v.      Item Name:  HIRE_DATE

1.      Properties: 

a.       Data Type:  Date

b.      Maximum Length:  11

                                                          vi.      Item Name:  DEPARTMENT_NAME

1.      Properties:

a.       Data Type:  Char

b.      Maximum Length:  30

                                                        vii.      Item Name:  MANAGER_NAME

1.      Properties:

a.       Data Type:  Char

b.      Maximum Length:  47

1. Open the Property Palette of your newly created data block and set the following properties:

                                                              i.      Name:  FROM_CLAUSE

                                                            ii.      Query Data Source Type:  FROM clause query

                                                          iii.      Query Data Source Name: 

(SELECT e.First_name       ,e.last_name       ,e.phone_number       ,e.hire_date       ,d.department_name       ,m.last_name||', '||m.first_name as manager_name FROM employees e, departments d, employees m WHERE e.department_id = d.department_id AND e.manager_id = m.employee_id) Note: It is important that you include the parenthesis around your SQL statement.

                                                          iv.      Insert Allowed:  No

                                                            v.      Update Allowed:  No

Build your Layout

1. Right-Click on the canvases node and select the Layout Wizard
1. The “Canvas:” option will list “(New Canvas}” and “Type:” will list “Content.”
2. Click Next to navigate to the Data Block screen
3. Add the FIRST_NAME, LAST_NAME, PHONE_NUMBER, HIRE_DATE, DEPARTMENT_NAME and MANAGER_NAME columns to the Displayed Items and click next.
4. Set the Prompt text, width and height properties.  When finished, your screen should look similar to the following and click next:

Name	Prompt	Width	Height
FIRST_NAME	First Name	68	14
LAST_NAME	Last Name	68	14
PHONE_NUMBER	Phone Number	68	14
HIRE_DATE	Hire Date	68	14
DEPARTMENT_NAME	Department	68	14
MANAGER_NAME	Manager	68	14

1. Select “Form” as the layout style and click next
2. Set the following Frame properties:

                                                              i.      Frame Title:  Employee Information

                                                            ii.      Records Displayed:  1

                                                          iii.      Distance Between Records:  0

                                                          iv.      Display Scrollbar:  Leave unchecked

                                                            v.      Click Finish to complete the layout wizard

1. Open the Canvas property palette and change the following properties:

                                                              i.      Width:  480

                                                            ii.      Height:  120

1. Lastly, you will need to adjust the properties of the default window (WINDOW1) to fit your canvas.  Open the property palette for WINDOW1 and set the Width and Height properties to match those of the canvas.
2. Your canvas should look similar to the following:
   

Now, let’s test the form.  This can be done one of two ways.  First; deploy the form to your TEST or Development application server or second; run the form from the Forms Builder.  This document does not discuss how to run a form from the Forms Builder – this is the subject of a separate document.

When run, your form should look similar to the following:

 

As I mentioned above, I will now discuss, briefly, how to modify the base SQL query used by the FROM Clause data block.  If you want to limit the results to a specific department or a manager, you would normally add this to the WHERE clause of the data block.  However, since your block is based on a FROM Clause query, you can’t use this property to limit the results.  Instead, you have to actually modify the source query to add your limiting conditions.  So we can see what is going on, I’ve added a button to the form (centered below the Employee Information frame called “Set Where Clause”.

The key to modifying the query is to remember to remove the trailing parenthesis before you modify the statement and to put it back when you are done and before assign the statement to the block.

As stated, I added a button (I created a control block for the button, but this isn’t necessary) to the form and added a When-Button-Pressed trigger to the button with the following code:

DECLARE             v_Before         VARCHAR2(2000) := Get_Block_Property                                                                                       ('FROM_CLAUSE',Query_Data_Source_Name);             v_After           VARCHAR2(2000); BEGIN             :Control.where_clause := v_before;                         -- Now, lets limit the query to only return a specific department             v_After := replace(v_before,')',' ')||' and d.department_id = 90 )';                         Set_Block_Property('FROM_CLAUSE',Query_Data_Source_Name, v_after);             Go_Block('FROM_CLAUSE');             Execute_Query;          END;

When you run your form and press the “Set Where Clause” button, only two “Executive” records will be returned.

I hope you have found this information useful.  If you find errors or have suggestions, please let me know (leave a comment below).

I have include a working version of the form in the Attachments below.  Just download the FROM_Clause_Query.zip file to find the Form Module (.fmb) as well as a Microsoft Word (2003) version of this document.