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
P23_UPDATE_DELETE
P23_COLUMN_NAME
P23_CONDITION
P23_CURRENT_DATE
P23_UPDATE_COLUMN
P23_TARGET_DATE
P23_UPDATE_COUNT
Convert
2. P23_UPDATE_DELETE
will be a radio group with Update-U and Delete-D
3. P23_COLUMN_NAME
select COLUMN_NAME R, COLUMN_NAME D
from all_tab_columns col
WHERE
OWNER='SCHEMA_NAME' AND
TABLE_NAME='TABLE_NAME' AND
COLUMN_NAME NOT IN (COLUMNS YOU WANT TO AVOID);
4.P23_CONDITION
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
5. P23_UPDATE_COLUMN
select COLUMN_NAME R, COLUMN_NAME D
from all_tab_columns col
WHERE
OWNER='SCHEMA_NAME' AND
TABLE_NAME='TABLE_NAME' AND
COLUMN_NAME NOT IN (COLUMNS YOU WANT TO AVOID);
6. Create a process on the Convert Button
DECLARE
V_QUERY VARCHAR2(4000):= NULL;
BEGIN
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)||'''';
END IF ;
EXECUTE IMMEDIATE V_QUERY ;
IF SQL%ROWCOUNT > 0 THEN
:P23_UPDATE_COUNT:=SQL%ROWCOUNT||' Records Successfully Updated!' ;
ELSE
:P23_UPDATE_COUNT:=NULL;
END IF;
END ;
-----> Put it &P23_UPDATE_COUNT. on Process Success Message
