By default, a data block inherently supports query-by-example, update, insert, and delete functionality. Oracle Forms issues the appropriate SELECT, UPDATE, INSERT, and DELETE SQL statements in response to operator actions and programmatic control. For example, to execute a query programmatically, navigate to the desired block (for example, with GO_BLOCK ), then call the EXECUTE_QUERY Built-in procedure.
There is no need to write the SQL SELECT statement to populate the appropriate items in the blocks, because data items are bound at design-time to the corresponding database columns, and Oracle Forms issues the SQL for you automatically. Oracle Forms also issues the SQL statements required to support default locking functionality.
For most applications, write SQL statements in triggers to extend the default Oracle Forms functionality. For example, to populate a look-up item that corresponds to a table other than the block's data table, write a SELECT...INTO statement in a Post-Query trigger.
It is possible to select a database value into a form item using block_name.item_name notation to refer to the form item:
SELECT column
INTO :block.item
FROM table
WHERE primarykey = :block.pri_key_item;
The statement above opens an implicit cursor. Declare explicit cursors when you want to have complete control over database queries.
As a general rule, DML statements are supported only in transactional triggers; that is, triggers that fire in response to transaction processing events, such as Pre-Update, Post-Insert, and On-Delete. The On-Event triggers completely replace the default SQL that Oracle Forms would have issued at that point. While you can write a trigger that uses any DML (Data Manipulation Language) statement, Oracle advises that you follow the recommendations that are stated for each trigger in the reference topics.
Using DML statements in certain triggers can de-synchronize the state of records between Oracle Forms and rows in the database, and can cause unexpected results. Oracle Forms keeps track of changes to the data in a form by examining item, record, block, and form status.
For example, when the operator changes the value of a text item and then issues the Save command, Oracle Forms examines the status of the record to determine that an UPDATE needs to be processed.
When DML statements are issued independently of default processing, Oracle Forms is unaware of any such changes, and you cannot rely on the default commit processing to manage them.
DDL (Data Dictionary Language) commands, such as CREATE TABLE and DROP SEQUENCE, can be issued by calling the FORMS_DDL Built-in procedure. Be aware that FORMS_DDL Built-in always issues an implicit COMMIT_FORM after executing the desired statement. Consequently, FORMS_DDL Built-in should be called only when the application does not have unsaved changes pending that the operator might want to subsequently undo.