Query Data that Uses Table Hyperlink Source

You can create an external table over an Autonomous Database Table Hyperlink.

Consider creating external tables over an Autonomous Database Table Hyperlink in the following scenarios:

When you need to use a REST client to create an external table and then utilize the SQL API to perform analytics on the datasets. This may include running SQL queries to analyze the data or aggregating results based on your requirements.
When you need to query data from databases across regions.

There are two ways to create an external table in an Autonomous Database using a Table Hyperlink:

Use a single Autonomous Database Table Hyperlink to create an external table. After the table is created, you can read data from the table using the SELECT statement. You can also apply filters and clauses when retrieving data from the table. For example, you can filter the data with the WHERE clause or sort it using the ORDER BY clause.
Example to create an external table over a single Table Hyperlink:
```
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name => 'ext_tab_parurl',
   file_uri_list => 'https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6XExample/data'
);
END;
/
```
After the table is created, you can query the table using the SELECT statement, and you can also filter or sort the output. Example to read data from the ext_tab_parurl table in descending order:
```
SELECT * FROM ext_tab_parurl 
   ORDER BY height DESC;

COUNTY    SPECIES      HEIGHT
------    ----------   -------
First     Chestnut      51
Main      Alder         45
Main      Douglas-fir   34
Main      Pine          29
Main      Cedar         21
```

Use a comma-delimited list of Autonomous Database Table Hyperlink URLs, you must ensure that all included Table Hyperlinks must have the same column names, column order, and column data types in the same schema.

Example to create an external table over a list of Table Hyperlinks:

DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name => 'ext_tab_parurl_1',
   file_uri_list => 'https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K6XExample/data',
                    'https://dataaccess.adb.us-chicago-1.oraclecloudapps.com/adb/p/K7XExample/data'
);
END;
/

Query the table using the SELECT statement:

SELECT * FROM ext_tab_parurl_1;

COUNTY   SPECIES      HEIGHT
------   ----------   -------
First     Chestnut      51
Main      Alder         45
Main      Douglas-fir   34
Main      Pine          29
Main      Cedar         21
Main      Aspen         35
First     Maple         16

The parameters are:

table_name: is the external table name.
file_uri_list: is either a single Table Hyperlinks or a comma-delimited list of Table Hyperlinks.

See CREATE_EXTERNAL_TABLE Procedure for more information.

Parent topic: Query External Data with Autonomous Database