The EXECUTE_DML stored procedure can be used to pass any valid, simple DML statement for immediate execution and committal with optional feedback on the affected row count and display of the SQL statement being executed.
When the verbose option is enabled (VERBOSE=YES) then the PRINT_SQL procedure is used to display the SQL (DML_COMMAND) being executed.
| Parameter | Description | |
|---|---|---|
| DML_SCHEMA | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | INUSER | |
| Description | The name of the schema containing the table to execute the DML against. | |
| DML_TABLE | Type: | INPUT |
| Datatype: | VARCHAR2(128) | |
| Default Value: | NONE | |
| Description | The name of the table to execute the DML against. | |
| Options | Any table for which the user has insert, update, and delete privileges. | |
| DML_COMMAND | Type: | INPUT |
| Datatype: | VARCHAR2(4000) | |
| Default Value: | NONE | |
| Description | The DML statement to execute. | |
| Options | Must be a basic INSERT, UPDATE, DELETE statement. | |
| IDENTIFIER | Type: | OUTPUT |
| Datatype | VARCHAR2(128) | |
| Default Value: | ALL | |
| Description | The identifier associated with the IN_CONTEXT context namespace. | |
| Options |
IN_DB_UTIL SYNC UPGRADE ALL |
|
| VERBOSE | Type: | INPUT |
| Datatype | VARCHAR2(3) | |
| Default Value: | Yes | |
| Description | ndicates if the SQL statement and number of affected rows should be displayed. | |
| Options |
Yes No |
|
Exceptions
Example
SET SERVEROUTPUT ON;
DECLARE
V_IDENTIFIER VARCHAR2(128);
V_EXEC_SQL VARCHAR2(4000);
BEGIN
V_IDENTIFIER := 'UPGRADE';
V_EXEC_SQL := 'INSERT INTO INUSER.IN_PRODUCT_MOD_HIST VALUES (''2100988013_1728017900'',
''7.9.0.0'', SYS_EXTRACT_UTC(SYSTIMESTAMP), ''UTC'')';
IN_DB_UTIL.EXECUTE_DML('INUSER', 'IN_PRODUCT_MOD_HIST', V_EXEC_SQL, V_IDENTIFIER, 'YES');
END;
/ INSERT INTO INUSER.IN_PRODUCT_MOD_HIST VALUES ('2100988013_1728017900', '7.9.0.0',
SYS_EXTRACT_UTC(SYSTIMESTAMP), 'UTC');
Inserted 1 row into the INUSER.IN_PRODUCT_MOD_HIST table.
PL/SQL procedure successfully completed.