oracle-databaseoracle-autonomous-db

Create External Table in another schema using DBMS_CLOUD package from Oracle


Oracle has provided DBMS_CLOUD package to create external tables for Parquet and other type of files stored in cloud storage. I can create an external table for a parquet file in the same schema.

However if I put my code in a procedure in schema A and call this procedure in schema B, it will give insufficient permissions error. After adding AUTHID CURRENT USER in the procedure, I am able to create external table but it will be created in schema B.

I want to create external tables in schema A but I want to call the procedure from schema B. Is there any way to achieve this?

Code:

dbms_cloud.create_external_table(
    table_name      =>  l_table_name, 
    credential_name =>  p_credential_name, 
    file_uri_list   =>  l_obs_url, 
    format          =>  '{"type":"parquet", "schema": "first"}'
    );

Solution

  • This is not possible today. We may allow that in the future.