External Tables with Oracle Cloud Database- External Tables

Cloud databases let us easily use files from cloud storage, and this is just a quick example of leveraging files in your cloud environment with your database.

You don’t have to have a directory created to use external tables, but you need to know the URI for the file that is the namespace and tenancy information along with the filename.

DBMS_CLOUD is a package that allows you to load data. It also provides ways to do the following:

•     Access management

•     Objects and files

•     Bulk file management

•     REST APIs

I realize that this is a little detour of just talking about external tables, but this provides a powerful package to help you manage data with your cloud environment.

And as you can see, it has some useful REST APIs for management and ways to manage files.

It also allows you to set and manage credentials. We will look at more ways to administer cloud databases in the next couple of chapters, but let’s look at how we use DBMS_CLOUD to create external tables.

DBMS_CLOUD Create Table

You can leverage your JSON and CSV files in your cloud storage to perform SQL statements against external tables.

Here is an example of creating an external table for a CSV file. You will see the location is now file_uri_list and the type of the file is CSV:

begin

dbms_cloud.create_external_table (table_name => ‘salesdata_ext’

, file_uri_list => ‘https://objectstorage.us-ashburn-1.oraclecloud.com/n/ ten_namespace/b/bucket_name/o/example_sales_2023.csv’

, format => json_object (‘type’ value ‘csv’, ‘skipheaders’ value ‘1’) , column_list => ‘sale_id number, sale_type number, customer_id number

, device varchar2(30)

, payment_type varchar2(30) , sale_date date

, sale_price number’); end;

After the table is created, you can use the data in SQL statements or load data from the file.

Here is one more quick example with a JSON file:

begin

dbms_cloud.create_external_table ( table_name => ‘json_salesdata_ext’ , file_uri_list => ‘https://objectstorage.us-ashburn-1.oraclecloud.

com/n/ten_namespace/b/bucket_name/o/customersales.json’, column_list => ‘json_document clob’, field_list => ‘json_document’, format => json_object (‘delimiter’ value ‘\n’) );end;