Oracle Forms的四种提交方式

一共有四种提交方式：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;