For example:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
(
table_name =>'CHANNELS_EXT',
credential_name =>'DEF_CRED_NAME',
file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp01.dmp,
https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp02.dmp'
format => json_object('type' value 'datapump', 'rejectlimit' value '1'),
column_list => 'CHANNEL_ID NUMBER, CHANNEL_DESC VARCHAR2(20), CHANNEL_CLASS VARCHAR2(20)' );
END;
/
The parameters are:
-
table_name
: is the external table name.
-
credential_name
: is the name of the credential created in the previous step.
-
file_uri_list
: is a comma delimited list of the Data Pump dump
files you want to query.
-
format
: defines the options you can specify to describe the format
of the source file defines the options you can specify to describe the format of the
source file. When you specify the type 'datapump'
, the only other
valid format parameter is 'rejectlimit'
.
-
column_list
: is a comma delimited list of the column definitions in the source files.
In this example, namespace-string
is the Oracle
Cloud Infrastructure object storage namespace and
bucketname
is the bucket
name. See Understanding
Object Storage Namespaces for more
information.
You can now run queries on the external table you created in the previous step. For example:
SELECT count(*) FROM channels_ext;
By default the database expects all rows in the external data file to be valid and
match both the target data type definitions as well as the format definition of the
files. As part of validation, DBMS_CLOUD
makes sure all the necessary dump file parts are
there and also checks that the dump files are valid and not corrupt (for example
exp01.dmp
, exp02.dmp
, and so on). You can
use the DBMS_CLOUD
format option
rejectlimit
to suppress these errors. As an alternative, you can also
validate the external table you created to see the error messages and the rejected rows.
See Validate External Data for more information.
For detailed information about the parameters, see CREATE_EXTERNAL_TABLE Procedure.
See DBMS_CLOUD URI Formats for more information on the supported cloud object storage services.