Hello Experts ,
We are receiving a file in a server on a daily basis with the date appended in the file name. Now we have an external table which fetches data from the table. As the date keep on changing , we are trying to modify the external table script to take the file based on date.(we can get it done at OS level using a shell script). But if it can be done at the external table level, it would be much more easy.
CREATE TABLE TTDS.Test_EXTERNAL
(
FROM_CRY VARCHAR2(100 BYTE),
TO_CRY VARCHAR2(100 BYTE),
FROM_CNV VARCHAR2(100 BYTE),
TO_CNV VARCHAR2(100 BYTE),
USER_TYPE VARCHAR2(100 BYTE),
CON_VAL VARCHAR2(100 BYTE),
MODE_FLAG VARCHAR2(100 BYTE),
ATTRIBUTE15 VARCHAR2(100 BYTE),
RECORD_STATUS VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY GCP
ACCESS PARAMETERS
( RECORDS DELIMITED BY newline
CHARACTERSET AR8MSWIN1256
STRING SIZES ARE IN CHARACTERS
FIELDS TERMINATED BY '~' NOTRIM
(
From_CRY char(4000),
To_CRY char(4000),
From_CBN_Date char(4000) ,
To_CRY_Date char(4000),
User_Con_Type char(4000),
ConVAL char(4000),
Mode_Flag char(4000) ,
Attribute15 char(4000),
Record_Status char(4000)
)
)
LOCATION (GCP:'Test_File_20210308.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
We are using alter statement to change the file name manually.
alter table Test_EXTERNAL location (GCP:'Test_File_20210803.csv')
Thanks alot.