You can use this new module or create a new one.
ORACLE 10G FORM BUILDER PASSWORD
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.įorms defaults to opening a new Forms Module. I’ll provide an example later in this document. 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.
ORACLE 10G FORM BUILDER UPDATE
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. 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. This document and the supplied sample files were written using Oracle Forms 10g R2 (10.1.2.0.2). If you don’t have these demo schemas – consult your DBA. This sample form uses the HR schema found in the default installation of Oracle 10g (or higher) RDBMS’s (demo schemas). 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 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 is because Oracle Forms will send the SQL statement to the database to be parsed rather than parse it in the Form. 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. 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. 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. 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. Using the “From Clause” query method, there is only one network trip to the database for the necessary data for your form.Īnother benefit of using the “From Clause” query is that it allows you to use SQL methods from Forms that are not supported yet.
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 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. 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. 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. ,m.last_name || ', ' || m.first_name AS manager_nameįROM employees e, DEPARTMENTS d, EMPLOYEES m Here is a basic example using SQL (based on the HR demo schema):įROM ( SELECT e.last_name || ', ' || e.first_name AS name The query that produces a result set is referenced through the FROM clause of a query – hence the name “From Clause” query. 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.
ORACLE 10G FORM BUILDER HOW TO
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. If you have worked Oracle Forms for any amount of time, you will eventually hear about basing a block on a From Clause query.