Create Credentials and Copy Data into an Existing Table
For data
loading from files in the Cloud, you need to first store your object storage credentials in your
Autonomous Database and then use the
procedure DBMS_CLOUD.COPY_DATA
to load
data.
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
- Store your object store credentials using the procedure
DBMS_CLOUD.CREATE_CREDENTIAL
. For example:SET DEFINE OFF BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL
( credential_name => 'DEF_CRED_NAME', username => 'adb_user@example.com', password => 'password' ); END; /This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for all data loads.
For detailed information about the parameters, see CREATE_CREDENTIAL Procedure.
Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.
Note
Some tools like SQL*Plus and SQL Developer use the ampersand character (&
) as a special character. If you have the ampersand character in your password use theSET DEFINE OFF
command in those tools as shown in the example to disable the special character and get the credential created properly. - Load data into an existing table using the procedure
DBMS_CLOUD.COPY_DATA
. For example:CREATE TABLE CHANNELS (channel_id CHAR(1), channel_desc VARCHAR2(20), channel_class VARCHAR2(20) ); / BEGIN
DBMS_CLOUD.COPY_DATA
( table_name =>'CHANNELS', credential_name =>'DEF_CRED_NAME', file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/channels.txt', format => json_object('delimiter' value ',') ); END; /The parameters are:
-
table_name
: is the target table’s name. -
credential_name
: is the name of the credential created in the previous step. Thecredential_name
parameter must conform to Oracle object naming conventions, which do not allow spaces or hyphens. -
file_uri_list
: is a comma delimited list of the source files you want to load. -
format
: defines the options you can specify to describe the format of the source file, including whether the file is of type text, ORC, Parquet, or Avro.If the data in your source files is encrypted, decrypt the data by specifying the
format
parameter with theencryption
option. See Decrypt Data While Importing from Object Storage for more information on decrypting data.
In this example,
namespace-string
is the Oracle Cloud Infrastructure object storage namespace andbucketname
is the bucket name. See Understanding Object Storage Namespaces for more information.Note
Autonomous Database supports a variety of source file formats, including compressed data formats. See DBMS_CLOUD Package Format Options and theDBMS_CLOUD
compression
format option to see the supported compression types.For detailed information about the parameters, see COPY_DATA Procedure and COPY_DATA Procedure for Avro, ORC, or Parquet Files.
-
Parent topic: Load Data from Files in the Cloud