Hello All,
I am trying to write a PL/SQL code for ETL(Extract,Transform&Load). I have a mapping table where we have target table name, target table column names , target column metadata, source table name and source table column name. See below screenshot.
Now there are rules for loading the target table as below :
(i) Target columns for which MANDATORY column is marked as 'Y' and if <Source_Column> in Source table is NULL, then that source record should be written in an Exception table mentioning that <Source_Column> is NULL.
(ii) Target Columns for which MANDATORY is marked as 'Y' and we have DEFAULT value, then if the <Source_Column> in Source table is NULL then the record is written in Target table with DEFAULT value for that column and at the same time record should be written in Exception table mentioning that <Source_Column> is NULL
(iii) If <Source_Column> value cannot be inserted in <Target_Column>, record should be written in Exception table mentioning that <Source_Column> is incompatible with <Target_Column>.
(iv) If <Source_Column> datatype and <Target_Column> datatype are different and cannot be converted then, the record should be written in Exception table mentioning <Source_Column> incompatible data types.
How can we achieve the above (iii) & (iv) rule ? Please help me out with a performant solution.
Thanks,