Application users can able to do DML from the frontend side.
1. Create a region and create 7 item like below and a button call Convert
will be a radio group with Update-U and Delete-D
select COLUMN_NAME R, COLUMN_NAME D
TABLE_NAME='TABLE_NAME' AND
COLUMN_NAME NOT IN (COLUMNS YOU WANT TO AVOID);
Select list -> Equals = , Not Equals <> , Is Empty (IS NULL),Is No Empty (IS NOT NULL), Greater Than > , Less Than < ,
Greater Than Equals >=, Less Than Equals <= , IN , NOT IN
select COLUMN_NAME R, COLUMN_NAME D
TABLE_NAME='TABLE_NAME' AND
COLUMN_NAME NOT IN (COLUMNS YOU WANT TO AVOID);
6. Create a process on the Convert Button
V_QUERY VARCHAR2(4000):= NULL;
IF :P23_UPDATE_DELETE='U' THEN
V_QUERY:='UPDATE SCHEMA_NAME.TABLE_NAME SET '||:P23_UPDATE_COLUMN||' = '''||:P23_TARGET_DATE||'''
WHERE '||:P23_COLUMN_NAME||' '||:P23_CONDITION||'
'||CASE WHEN UPPER(:P23_CONDITION) ='IS NULL' THEN NULL
WHEN UPPER(:P23_CONDITION) ='IS NOT NULL' THEN NULL
WHEN UPPER(:P23_CONDITION) ='IN' THEN '('||:P23_CURRENT_DATE||')'
WHEN UPPER(:P23_CONDITION) ='NOT IN' THEN '('||:P23_CURRENT_DATE||')'
WHEN UPPER(:P23_CONDITION) ='BETWEEN' THEN :P23_CURRENT_DATE ||' AND '|| :P23_BETWEEN
ELSE :P23_CURRENT_DATE END ||'
AND UPPER(UPLOADED_BY)= '''||UPPER(:APP_USER)||'''';
ELSIF :P23_UPDATE_DELETE='D' THEN
V_QUERY:='DELETE FROM UPDATE SCHEMA_NAME.TABLE_NAME WHERE '||:P23_COLUMN_NAME||' '||:P23_CONDITION||'
'||CASE WHEN UPPER(:P23_CONDITION) ='IS NULL' THEN NULL
WHEN UPPER(:P23_CONDITION) ='IS NOT NULL' THEN NULL
WHEN UPPER(:P23_CONDITION) ='IN' THEN '('||:P23_CURRENT_DATE||')'
WHEN UPPER(:P23_CONDITION) ='NOT IN' THEN '('||:P23_CURRENT_DATE||')'
WHEN UPPER(:P23_CONDITION) ='BETWEEN' THEN :P23_CURRENT_DATE ||' AND '|| :P23_BETWEEN
ELSE :P23_CURRENT_DATE END ||'
AND UPPER(UPLOADED_BY)= '''||UPPER(:APP_USER)||'''';
EXECUTE IMMEDIATE V_QUERY ;
:P23_UPDATE_COUNT:=SQL%ROWCOUNT||' Records Successfully Updated!' ;
-----> Put it &P23_UPDATE_COUNT. on Process Success Message