I'm using an Oracle 19c ADW environment. I am working on a project to convert data for 36 organizations. I extract their legacy data to a CSV file and put it in their respective folder in Object Storage. I want to create an external table for each data set under its own schema using DBMS_CLOUD.CREATE_EXTERNAL_TABLE. I then run my data conversion programs against each data set separately.
I have everything working except for one aspect. I can't figure out how to create the external table with the DBMS_CLOUD.CREATE_EXTERNAL_TABLE procedure in each of the respective schemas.
All of my data conversion code is under one schema (DATACONV). Each set of data will be the same table names but stored under its own schema. I granted my schema CREATE ANY TABLE. I can create a table from my schema in another schema without issue by qualifying the other schema. I've changed the session CURRENT_SCHEMA and created a table in the other schema without having to qualify it. When I use DBMS_CLOUD.CREATE_EXTERNAL_TABLE, it only creates the external table in the DATACONV schema, regardless of other settings. I tried to qualify the table name with {schema.table_name}, that that reports an error on the table name.
My fallback is to actually log into each of the other schemas and create the external tables, but I would rather do it all from one schema. Does anyone have any idea how to do this?