Hi Guys,
Hope you are doing well.
I was trying to get the file date time from multiple folders using below code but seems like it deosn't work when i swicth between QAS and UAT enviornments whereas am getting correct database date time while switching between the enviornments.
IF '$(v.Environment)' = 'QAS' THEN
LET v.ExtractFolder = '\\....QAS\Inputs\KK\';
// QAS CONNECTION STRING
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User I....);
ELSEIF '$(v.Environment)' = 'UAT' THEN
LET v.ExtractFolder = '\\......\UAT\Inputs\KK\';
// UAT CONNECTION STRING
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User I......);
ENDIF;
EXCEL_FILES:
LOAD * INLINE [
EXCEL_FILE_NAME, ID
DUMMY_MASTER, 1
DUMMY_MASTER_KK, 2];
KK_TABLES:
LOAD * INLINE [
KK_TBL_NAME, ID
DUMMY_MASTER, 1
DUMMY_MASTER_KK, 2];
For Each ExtractFile in 'DUMMY_MASTER.xlsx','DUMMY_MASTER_KK.xlsx',
DATA_EXCELS:
LOAD
FileBaseName() AS EXCEL_FILE_NAME,
COUNT(RowNo()) AS NUMBER_OF_RECORDS_IN_EXCEL,
filetime() AS EXCEL_FILE_DATE
FROM
[$(v.ExtractFolder)$(ExtractFile)]
(ooxml, embedded labels, table is DATA);
TEMP_DATA_TABLES:
SQL
SELECT TABLE_NAME AS KK_TBL_NAME,
TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) C FROM DUMMY'||TABLE_NAME)),'/ROWSET/ROW/C')) AS NUMBER_OF_RECORDS_IN_TABLE,
TO_CHAR(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT MAX(ROW_INSERT_DT) C FROM DUMMY'||TABLE_NAME)),'/ROWSET/ROW/C')) AS MAX_INSERT_DATE
FROM ALL_TABLES
WHERE OWNER = 'DUMMY_KK'
AND TABLE_NAME LIKE '%KK%';
Regards,
KK
