I am wondering if it is possible to modify the location for an external table dynamically.
one of the example
SELECT country_code, COUNT(*) AS amount
FROM tab_ext EXTERNAL MODIFY (
LOCATION ('gbr1.txt', 'gbr2.txt')
)
GROUP BY country_code
ORDER BY 1;
I am using external tables to read files from AWS S3 bucket. The files are partitioned by date ddate.
ddate = number of days since 01/01/1970.
WITH t AS (
SELECT /*+ materialize */
round(trunc(SYSDATE) - TO_DATE('01/01/1970', 'DD/MM/YYYY')) part_date
FROM
dual
)
SELECT
json_document
FROM
t
, lateral ( SELECT
*
FROM
t1_json external modify (location('https://*****' || part_date||'/*.json')) );
This doesn't seems to work. But if i have it hard coded location then it works.
Other option i have is to alter the table before i read from the external table.
"Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0"
Please advise if there is a way to achieve this.
Thanks