DBMS_CLOUD Package File URI Formats
Describes the format of the
source file URIs in operations with DBMS_CLOUD
. The format depends on the object storage service you are
using.
DBMS_CLOUD
guarantees
secure communication and any URI that you specify must use HTTPS, with
https://
as the prefix for the URI.
Oracle Cloud Infrastructure Object Storage Native URI Format
If your source files reside on the Oracle Cloud Infrastructure Object Storage you can use Oracle Cloud Infrastructure native URIs, with the format:
https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucket/o/filename
For example, the Native URI for the file channels.txt
in
the bucketname
bucket in the
Phoenix
data center is:
https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/channels.txt
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 find the URI from the Oracle Cloud Infrastructure Object Storage "Object Details" in the right hand side ellipsis menu in the Object Store:
- Open the Oracle Cloud Infrastructure Console by clicking the
next to Oracle Cloud.
- From the Oracle Cloud Infrastructure left navigation menu click Core Infrastructure. Under Object Storage, click Object Storage.
- Under List Scope, select a Compartment.
- From the Name column, select a bucket.
- In the Objects area, click View Object Details.
- On the Object Details page, the URL Path (URI) field shows the URI to access the object.
Note:
The source files need to be stored in an Object Storage tier bucket. Autonomous Database does not support buckets in the Archive Storage tier. See Overview of Object Storage for more information.
Oracle Cloud Infrastructure Object Storage Swift URI Format
If your source files reside on the Oracle Cloud Infrastructure Object Storage you can use Oracle Cloud Infrastructure Swift URIs with the format:
https://swiftobjectstorage.region.oraclecloud.com/v1/namespace-string/bucket/filename
For example, the Swift URI for the file channels.txt
in
the bucketname
bucket in the
Phoenix
data center is:
https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/namespace-string/bucketname/channels.txt
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.
Note:
The source files need to be stored in an Object Storage tier bucket. Autonomous Database does not support buckets in the Archive Storage tier. See Overview of Object Storage for more information.
Oracle Cloud Infrastructure Object Storage URI Format Using Pre-Authenticated Request URL
If your source files reside on the Oracle Cloud Infrastructure Object Storage you can use Oracle Cloud Infrastructure pre-authenticated URIs. When you create a pre-authenticated request, a unique URL is generated. You can then provide the unique URL to users in your organization, partners, or third parties to access the Object Storage resource target identified in the pre-authenticated request.
Note:
Carefully assess the business requirement for and the security ramifications of pre‑authenticated access. When you create the pre-authenticated request URL, note the Expiration and the Access Type to make sure they are appropriate for your use.A pre-authenticated request URL gives anyone who has the URL access to the targets identified in the request for as long as the request is active. In addition to considering the operational needs of pre-authenticated access, it is equally important to manage its distribution.
The format for pre-authenticated request URLs is:
https://objectstorage.region.oraclecloud.com/p/encrypted_string/n/namespace-string/b/bucket/o/filename
For example, a sample pre-authenticated URI for the file
channels.txt
in the bucketname bucket in the Phoenix
data center is:
https://objectstorage.us-phoenix-1.oraclecloud.com/p/2xN-uDtWJNsiD910UCYGue/n/namespace-string/b/bucketname/o/channels.txt
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 use a pre-authenticated URL in any DBMS_CLOUD
procedure that takes a URL to access files in Oracle Cloud
Infrastructure object store, without the need to create a credential. You need to either specify the
credential_name
parameter as NULL
or not supply a
credential_name
parameter.
For example:
BEGIN
DBMS_CLOUD.COPY_DATA
(
table_name =>'CHANNELS',
file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/p/unique-pre-authenticated-string/n/namespace-string/b/bucketname/o/channels.txt',
format => json_object('delimiter' value ',') );
END;
/
Note:
A list of mixed URLs is valid. If the URL list contains both pre-authenticated URLs and URLs that require authentication,DBMS_CLOUD
uses the specified credential_name
to
access the URLs that require authentication and for the pre-authenticated URLs the
specified credential_name
is ignored.
See Using Pre-Authenticated Requests for more information.
URI Format Using Public URL
If your source files reside on an Object Store that provides public URLs,
you can use public URLs with DBMS_CLOUD
procedures. Public means the
Object Storage service supports anonymous, unauthenticated access to the Object Store
files. See your Cloud Object Storage service for details on how to make an object public
in a supported Object Store.
Note:
Carefully assess the business requirement for and the security ramifications of using public URLs. When you use public URLs, due to the file content not being authenticated, make sure this is appropriate for your use.You can use a public URL in any DBMS_CLOUD
procedure that
takes a URL to access files in your object store, without the need to create a
credential. You need to either specify the credential_name
parameter as
NULL
or not supply a credential_name
parameter.
For example the following uses DBMS_CLOUD.COPY_DATA
without a
credential_name
:
BEGIN
DBMS_CLOUD.COPY_DATA
(
table_name =>'CHANNELS',
file_uri_list =>'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/chan_v3.dat',
format => json_object('delimiter' value ',') );
END;
/
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.
Note:
A list of mixed URLs is valid. If the URL list contains both public URLs and URLs that require authentication,DBMS_CLOUD
uses the specified credential_name
to
access the URLs that require authentication and for the public URLs the specified
credential_name
is ignored.
See Public Buckets for information on using Oracle Cloud Infrastructure public buckets.
Oracle Cloud Infrastructure Object Storage Classic URI Format
If your source files reside in Oracle Cloud Infrastructure Object Storage Classic, see the REST page for a description of the URI format for accessing your files: About REST URLs for Oracle Cloud Infrastructure Object Storage Classic Resources.
Amazon S3 URI Format
If your source files reside in Amazon S3, see the following for a description of the URI format for accessing your files: Accessing a bucket.
For example the following refers to the file
channels.txt
in the adb
bucket in
the us-west-2
region.
https://s3-us-west-2.amazonaws.com/adb/channels.txt
You can use a presigned URL in any DBMS_CLOUD
procedure that
takes a URL to access files in Amazon S3 object
store, without the need to create a credential. To use a presigned URL in
any DBMS_CLOUD
procedure, either specify the
credential_name
parameter as NULL
,
or do not supply a credential_name
parameter.
See Share an Object with Others for more information.
Note:
DBMS_CLOUD
supports the standard Amazon S3 endpoint syntax to access your buckets.
DBMS_CLOUD
does not support Amazon S3 legacy endpoints. See Legacy Endpoints for more
information.
Azure Blob Storage URI Format
If your source files reside in Azure Blob Storage, see the following for a description of the URI format for accessing your files: Resource URI Syntax.
For example the following refers to the file
channels.txt
in the adb
container in the storage account adb_user
:
https://adb_user.blob.core.windows.net/adb/channels.txt
Note:
You can use Shared Access Signatures (SAS) URL in anyDBMS_CLOUD
procedure that takes
a URL to access files in Azure Blob Storage,
without the need to create a credential. To use a Shared Access Signature
(SAS) URL, either specify the credential_name
parameter as
NULL
, or do not supply a
credential_name
parameter.
See Grant Limited Access to Azure Storage Resources Using Shared Access Signatures (SAS) for more information.
Amazon S3 Compatible URI Format
DBMS_CLOUD
supports
object storage service implementations that support Amazon S3
compatible URLs, including the following services:
- Oracle Cloud Infrastructure Object Storage with Amazon S3 compatible URL
- Google Cloud Storage with Amazon S3 compatible URL
- Wasabi Hot Cloud Storage with Amazon S3 compatible URL
Note:
To useDBMS_CLOUD
with
an Amazon S3 compatible object store you need to provide valid
credentials. See CREATE_CREDENTIAL Procedure for more information.
If your source files reside on a service that supports Amazon S3 compatible URIs, use the following URI format to access your files:
-
Oracle Cloud Infrastructure Object Storage S3 Compatible URL
Object URL Format:
https://mynamespace.compat.objectstorage.region.oraclecloud.com/bucket_name/object_name
Bucket URL Format:
https://mynamespace.compat.objectstorage.region.oraclecloud.com/bucket_name
See Amazon S3 Compatibility and Object Storage Service API for more information.
-
Google Cloud Storage S3 Compatible URL
Object URL Format:
https://bucketname.storage.googleapis.com/object_name
Bucket URL Format:
https://bucketname.storage.googleapis.com/
See Migrating from Amazon S3 to Cloud Storage and Request Endpoints for more information.
-
Wasabi S3 Compatible URL
Object URL Format:
https://bucketname.s3.region.wasabisys.com/object_name
Bucket URL Format:
https://bucketname.s3.region.wasabisys.com/
See S3-compatible API Connectivity for Wasabi Hot Cloud Storage and What are the service URLs for Wasabi's different regions? for more information.
GitHub Raw URL Format
DBMS_CLOUD
supports GitHub Raw URLs to access data from a GitHub Repository.
Note:
ForDBMS_CLOUD
access with GitHub Raw URLs, repository access
is limited to read-only functionality. The DBMS_CLOUD
APIs such as DBMS_CLOUD.PUT_OBJECT
that write data are not supported
with DBMS_CLOUD
APIs on a GitHub Repository.
As an alternative, use DBMS_CLOUD_REPO.PUT_FILE
to upload
data to a GitHub Repository.
Use GitHub Raw URLs with DBMS_CLOUD
APIs to access source files that reside on a
GitHub Repository. When you browse to a file on GitHub and
click the Raw link, this shows the GitHub Raw URL. See the
following for a description of GitHub Raw URL format: What do raw.githubusercontent.com URLs
represent?.
For example, using DBMS_CLOUD.GET_OBJECT
:
BEGIN
DBMS_CLOUD.GET_OBJECT
(
credential_name => 'MY_CRED',
object_uri => 'https://raw.githubusercontent.com/myaccount/myrepo/master/data-management-library/autonomous-database/adb-loading.csv',
directory_name => 'DATA_PUMP_DIR'
);
END;
/
For example, using DBMS_CLOUD.CREATE_EXTERNAL_TABLE
:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
(
credential_name => 'MY_CRED',
table_name => 'EMPLOYEES_EXT',
file_uri_list => 'https://raw.githubusercontent.com/myaccount/myrepo/master/data-management-library/autonomous-database/*.csv',
column_list => 'name varchar2(30), gender varchar2(30), salary number',
format => JSON_OBJECT('type' value 'csv')
);
END;
/
SELECT * FROM employees_ext;
DBMS_CLOUD
procedures that take a URL to access a GitHub Repository do not require credentials with public
visibility GitHub repositories. To use a public visibility URL you can specify the
credential_name
parameter as NULL
or not supply a
credential_name
parameter. See Setting repository visibility for
more information.