Query External Data
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
.
You can also use the procedure DBMS_CLOUD.CREATE_EXTERNAL_TABLE
to query external data in attached
file systems or in the local file system.
The procedure DBMS_CLOUD.CREATE_EXTERNAL_TABLE
supports external files in the
supported cloud object storage services, including:
-
Oracle Cloud Infrastructure Object Storage
-
Azure Blob Storage
-
Amazon S3
-
Amazon S3-Compatible, including: Oracle Cloud Infrastructure Object Storage, Google Cloud Storage, and Wasabi Hot Cloud Storage.
-
GitHub Repository
The source file in this example, channels.txt
, has the
following data:
S,Direct Sales,Direct T,Tele Sales,Direct C,Catalog,Indirect I,Internet,Indirect P,Partners,Others
- External Table Metadata Columns
The external table metadata helps you determine where data is coming from when you perform a query.
Parent topic: Query External Data with Autonomous Database
External Table Metadata Columns
The external table metadata helps you determine where data is coming from when you perform a query.
The external tables you create with DBMS_CLOUD.CREATE_EXTERNAL_TABLE
, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
, or DBMS_CLOUD.CREATE_HYBRID_PART_TABLE
include two invisible columns file$path
and file$name
.
These columns help identify which file a record is coming from.
-
file$path
: Specifies the file path text up to the beginning of the object name. -
file$name
: Specifies the object name, including all the text that follows the final "/
".
For example:
SELECT genre_id, name, file$name, file$path FROM ext_genre
WHERE rownum <= 2;
genre_id name file$name file$path
-------- --------- ----------- ----------------------
1 Action genre.csv https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre
2 Adventure genre.csv https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_gold/o/genre
See Invisible Columns for more information on invisible columns.
Parent topic: Query External Data