Friday, November 10, 2017

FORMS_DDL in Oracle Forms



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

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)