Database DML from frontend in Oracle Apex

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