FORMS_DDL
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.
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 DML or DDL statement:
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;
No comments:
Post a Comment