Oracle: add many columns

–Add new columns to <TABLE> if not already present
SET ECHO OFF
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK ON

DECLARE
P_TABLE_NAME VARCHAR(30 CHAR):='<TABLE>’;
BEGIN
FOR ICP IN (
SELECT
FLIST.ORDR,
FLIST.COLUMN_NAME,
FLIST.DATA_TYPE,
USER_TAB_COLS.COLUMN_NAME AS XCOL_NAME
FROM
(
SELECT 1 AS ORDR, ‘LOAN_CLASS’ AS COLUMN_NAME, ‘TIMESTAMP(6)’ AS DATA_TYPE FROM DUAL UNION
SELECT 2 AS ORDR, ‘LOAN_PURPOSE_CODE’ AS COLUMN_NAME, ‘TIMESTAMP(6)’ AS DATA_TYPE FROM DUAL UNION
SELECT 3 AS ORDR, ‘PRIMARY_BORROWER’ AS COLUMN_NAME, ‘NUMBER’ AS DATA_TYPE FROM DUAL UNION
SELECT 4 AS ORDR, ‘REVOLVING_FLAG’ AS COLUMN_NAME, ‘TIMESTAMP(6)’ AS DATA_TYPE FROM DUAL UNION
SELECT 5 AS ORDR, ‘REPORTING_ENTITY’ AS COLUMN_NAME, ‘TIMESTAMP(6)’ AS DATA_TYPE FROM DUAL UNION
SELECT 6 AS ORDR, ‘CURRENT_LVR’ AS COLUMN_NAME, ‘NUMBER’ AS DATA_TYPE FROM DUAL
) FLIST
LEFT JOIN USER_TAB_COLS
ON FLIST.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME AND USER_TAB_COLS.TABLE_NAME = P_TABLE_NAME
ORDER BY FLIST.ORDR ASC
)
LOOP
IF ICP.XCOL_NAME IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(‘ALTER TABLE ‘||P_TABLE_NAME||’ DROP (‘||ICP.COLUMN_NAME||’)’);
EXECUTE IMMEDIATE ‘ALTER TABLE ‘||P_TABLE_NAME||’ DROP (‘||ICP.COLUMN_NAME||’)’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘ALTER TABLE ‘||P_TABLE_NAME||’ ADD (‘||ICP.COLUMN_NAME||’ ‘||ICP.DATA_TYPE||’)’);
EXECUTE IMMEDIATE ‘ALTER TABLE ‘||P_TABLE_NAME||’ ADD (‘||ICP.COLUMN_NAME||’ ‘||ICP.DATA_TYPE||’)’;

END LOOP;
END;
/
SHOW ERRORS;