一共有四种提交方式:commit_form、Do_key('commit_form')、commit、FORMS_DDL('commit');
方式一:commit_form
先验证forms代码的数据,然后对每个数据块提交。
COMMIT_FORM Built-in
Description
Causes Oracle Forms to update data in the database to match data in the form. Oracle Forms first validates the form, then, for each block in the form, deletes, inserts, and updates to the database, and performs a database commit. As a result of the database commit, the database releases all row and table locks.
If the end user has posted data to the database during the current Runform session, a call to the COMMIT_FORM Built-in commits this data to the database.
Following a commit operation, Oracle Forms treats all records in all base-table blocks as if they are queried records from the database. Oracle Forms does not recognize changes that occur in triggers that fire during commit processing.
Syntax
PROCEDURE COMMIT_FORM;
Built-in Type restricted procedure
Enter Query Mode no
COMMIT_FORM Restrictions
If you use a PL/SQL COMMIT statement in an anonymous block or a form-level procedure, Oracle Forms interprets that statement as a call to the COMMIT_FORM Built-in.
COMMIT_FORM Examples
Example 1
/*
** Built-in: COMMIT_FORM
** Example: If there are records in the form to be
** committed, then do so. Raise an error if the
** commit was not successful.
*/
BEGIN
/*
** Force validation to happen first
*/
Enter;
IF NOT Form_Success THEN
RAISE Form_Trigger_Failure;
END IF;
/*
** Commit if anything is changed
*/
IF :System.Form_Status = 'CHANGED' THEN
Commit_Form;
/*
** A successful commit operation sets Form_Status back
** to 'QUERY'.
*/
IF :System.Form_Status <> 'QUERY' THEN
Message('An error prevented your changes from being
committed.');
Bell;
RAISE Form_Trigger_Failure;
END IF;
END IF;
END;
Example 2
/*
** Built-in: COMMIT_FORM
** Example: Perform Oracle Forms database commit during commit
** processing. Decide whether to use this Built-in
** or a user exit based on a global flag setup at
** startup by the form, perhaps based on a
**
** Trigger: On-Commit
*/
BEGIN
/*
** Check the global flag we set during form startup
*/
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
User_Exit('my_commit');
/*
** Otherwise, do the right thing.
*/
ELSE
Commit_Form;
END IF;
方式二:Do_key
DO_KEY Built-in
Description
先执行触发器的内容,如果没有则子程序执行。
Executes the key trigger that corresponds to the specified Built-in subprogram. If no such key trigger exists, then the specified subprogram executes. This behavior is analogous to pressing the corresponding function key.
Syntax
PROCEDURE DO_KEY
(built_in_subprogram_name VARCHAR2);
Built-in Type restricted procedure
Enter Query Mode yes
Parameters
built_in_subprogram_name
Specifies the name of a valid Built-in subprogram.
Built-in
Key Trigger
Associated Function Key
CLEAR_BLOCK Key-CLRBLK [Clear Block]
CLEAR_FORM Key-CLRFRM [Clear Form]
CLEAR_RECORD Key-CLRREC [Clear Record]
COMMIT_FORM Key-COMMIT [Commit]
COUNT_QUERY Key-CQUERY [Count Query Hits]
CREATE_RECORD Key-CREREC [Insert Record]
DELETE_RECORD Key-DELREC [Delete Record]
DOWN Key-DOWN [Down]
DUPLICATE_ITEM Key-DUP-ITEM [Duplicate Item]
DUPLICATE_RECORD Key-DUPREC [Duplicate Record]
EDIT_TEXTITEM Key-EDIT [Edit]
ENTER Key-ENTER [Enter]
ENTER_QUERY Key-ENTQRY [Enter Query]
EXECUTE_QUERY Key-EXEQRY [Execute Query]
EXIT_FORM Key-EXIT [Exit/Cancel]
HELP Key-HELP [Help]
LIST_VALUES Key-LISTVAL [List]
LOCK_RECORD Key-UPDREC [Lock Record]
NEXT_BLOCK Key-NXTBLK [Next Block]
NEXT_ITEM Key-NEXT-ITEM [Next Item]
NEXT_KEY Key-NXTKEY [Next Primary Key Fld]
NEXT_RECORD Key-NXTREC [Next Record]
NEXT_SET Key-NXTSET [Next Set of Records]
PREVIOUS_BLOCK Key-PRVBLK [Previous Block]
PREVIOUS_ITEM Key-PREV-ITEM [Previous Item]
PREVIOUS_RECORD Key-PRVREC [Previous Record]
PRINT Key-PRINT [Print]
SCROLL_DOWN Key-SCRDOWN [Scroll Down]
SCROLL_UP Key-SCRUP [Scroll Up]
UP Key-UP [Up]
DO_KEY Restrictions
DO_KEY accepts Built-in names only, not key names: DO_KEY(ENTER_QUERY).
To accept a specific key name, use the EXECUTE_TRIGGER Built-in: EXECUTE_TRIGGER('KEY_F11').
DO_KEY Example
/*
** Built-in: DO_KEY
** Example: Simulate pressing the [Execute Query] key.
*/
BEGIN
DO_KEY('Execute_Query');
END;
方式三:
commit
提交数据库block以及forms里面的insert,update,delete等dll语句;如果有冲突以界面上的block为主。
方式四:
FORMS_DDL(commit)
仅仅对forms内部程序提交,不会对block进行操作。
FORMS_DDL Built-in
Description
Issues dynamic SQL statements at runtime, including server-side PL/SQL and DDL.
Note: All DDL operations issue
an implicit COMMIT and will
end the current transaction without allowing Oracle Forms to process any pending changes.
Syntax
FUNCTION FORMS_DDL
(statement VARCHAR2);
Built-in Type unrestricted function
Enter Query Mode yes
Parameters
statement Any string expression up to 32K: a literal an expression or a variable representing the text of a block of dynamically created PL/SQL code a DML statement or a DDL statement
Usage Notes
Commit (or roll back) all pending changes before you issue the FORMS_DDL command. All DDL operations issue an implicit COMMIT and will end the current transaction without allowing Oracle Forms to process any pending changes, as well as losing any locks Oracle Forms may have acquired.
Some supplied stored procedures issue COMMIT or ROLLBACK commands as part of their logic. Make sure all pending changes in the form are committed or rolled back before you call those Built-ins. Use the SYSTEM.FORM_STATUS variable to check whether there are pending changes in the current form before you issue the FORMS_DDL command. (See Example 4.)
If you use FORMS_DDL to execute a valid PL/SQL block: Use semicolons where appropriate. Enclose the PL/SQL block in a valid BEGIN/END block structure. Do not end the PL/SQL block with a slash. Line breaks, while permitted, are not required.
If you use FORMS_DDL to execute a single DDL statement: Omit the trailing semicolon to avoid an invalid character error.
To check whether the statement issued using FORMS_DDL executed correctly, use the FORM_SUCCESS or FORM_FAILURE Boolean functions. If the statement did not execute correctly, check the error code and error text using DBMS_ERROR_CODE and DBMS_ERROR_TEXT. Note that the values of DBMS_ERROR_CODE and DBMS_ERROR_TEXT are not automatically reset following successful execution, so their values should only be examined after an error has been detected by a call to FORM_SUCCESS or FORM_FAILURE.
FORMS_DDL Restrictions
The statement you pass to FORMS_DDL may not contain bind variable references in the string, but the values of bind variables can be concatenated into the string before passing the result to FORMS_DDL. For example, this statement is not valid:
Forms_DDL ('Begin Update_Employee (:emp.empno); End;');
However, this statement is valid, and would have the desired effect:
Forms_DDL ('Begin Update_Employee ('||TO_CHAR(:emp.empno)
||');End;');
However, you could also call a stored procedure directly, using Oracle's shared SQL area over multiple executions with different values for emp.empno:
Update_Employee (:emp.empno);
SQL statements and PL/SQL blocks executed using FORMS_DDL cannot return results to Oracle Forms directly. (See Example 4.)
In addition, some DDL operations cannot be performed using FORMS_DDL, such as dropping a table or database link, if Oracle Forms is holding a cursor open against the object being operated upon.
FORMS_DDL Examples
Example 1
/*
** Built-in: FORMS_DDL
** Example: The expression can be a string literal.
*/
BEGIN
Forms_DDL('create table temp(n NUMBER)');
IF NOT Form_Success THEN
Message ('Table Creation Failed');
ELSE
Message ('Table Created');
END IF;
END;
Example 2
/*
** Built-in: FORMS_DDL
** Example: The string can be an expression or variable.
** Create a table with n Number columns.
** TEMP(COL1, COL2, ..., COLn).
*/
PROCEDURE Create_N_Column_Number_Table (n NUMBER) IS
my_stmt VARCHAR2(2000);
BEGIN
my_stmt := 'create table tmp(COL1 NUMBER';
FOR I in 2..N LOOP
my_stmt := my_stmt||',COL'||TO_CHAR(i)||' NUMBER';
END LOOP;
my_stmt := my_stmt||')';
/*
** Now, create the table...
*/
Forms_DDL(my_stmt);
IF NOT Form_Success THEN
Message ('Table Creation Failed');
ELSE
Message ('Table Created');
END IF;
END;
Example 3:
/*
** Built-in: FORMS_DDL
** Example: The statement parameter can be a block
** of dynamically created PL/SQL code.
*/
DECLARE
procname VARCHAR2(30);
BEGIN
IF :global.flag = 'TRUE' THEN
procname := 'Assign_New_Employer';
ELSE
procname := 'Update_New_Employer';
END IF;
Forms_DDL('Begin '|| procname ||'; End;');
IF NOT Form_Success THEN
Message ('Employee Maintenance Failed');
ELSE
Message ('Employee Maintenance Successful');
END IF;
END;
Example 4:
/*
** Built-in: FORMS_DDL
** Example: Issue the SQL statement passed in as an argument,
** and return a number representing the outcome of
** executing the SQL statement.
** A result of zero represents success.
*/
FUNCTION Do_Sql (stmt VARCHAR2, check_for_locks BOOLEAN := TRUE)
RETURN NUMBER
IS
SQL_SUCCESS CONSTANT NUMBER := 0;
BEGIN
IF stmt IS NULL THEN
Message ('DO_SQL: Passed a null statement.');
RETURN SQL_SUCCESS;
END IF;
IF Check_For_Locks AND :System.Form_Status = 'CHANGED' THEN
Message ('DO_SQL: Form has outstanding locks pending.');
RETURN SQL_SUCCESS;
END IF;
Forms_DDL(stmt);
IF Form_Success THEN
RETURN SQL_SUCCESS;
ELSE
RETURN Dbms_Error_Code;
END IF;
END;