Insert Data into Oracle Autonomous Data Warehouse

Provision an Oracle Autonomous Data Warehouse Instance

Provision an instance of Oracle Autonomous Data Warehouse in the Oracle Cloud Infrastructure service console.

Sign in to the Oracle Cloud Infrastructure service console.
Click the Autonomous Data Warehouse menu and select Create Autonomous Database.
On the Create Autonomous Database page, select the appropriate Compartment.
Enter a Display Name for the data warehouse.
Enter a Database Name.
Click Data Warehouse for workload type.
Specify the CPU Core Count and Storage (in terabytes) that are appropriate for your workload based on the number of concurrent user and queries, the volume of data, and so on. For a small data sample (1 MB file) for example, specify a CPU Core Count of 2 and 1 TB of storage.
If you want to automatically scale CPU Core Count and Storage values based on changes in your workload, click Auto Scaling.
Specify a password for the Admin account.
Click a Bring Your Own License if you want to use an existing on-premise license or click License Included to provision a license with the data warehouse.

Obtain Wallet Credentials

Integration services can use a wallet that contains client credentials to access Oracle Autonomous Data Warehouse. To create a wallet, you must have an administrator account for Oracle Autonomous Data Warehouse.

Navigate to the Service Console for Oracle Autonomous Data Warehouse.
Click Administration.
On the Administration page click Download Client Credentials.
In the Client Credentials dialog, enter a wallet password and confirm the password.
Click Download to save the client security credentials zip file.

You must protect this file to prevent unauthorized database access.

Connect using Oracle SQL Developer Web

Oracle SQL Developer Web is a cloud-based development environment that simplifies the development and management of Oracle Autonomous Data Warehouse.

Oracle SQL Developer Web is a browser-based interface of Oracle SQL Developer and provides a subset of the features of the desktop version, including the ability to run SQL statements and scripts in the worksheet.

Oracle SQL Developer Web connects to Oracle Autonomous Data Warehouse using the credentials in an Oracle Wallet.

Navigate to the Service Console for Oracle Autonomous Data Warehouse.
Click Development and then click SQL Developer Web.
Enter the admin user name and password for the Oracle Autonomous Data Warehouse instance and click Sign In.

Create an Auth Token

Before you can move data from Oracle Cloud Infrastructure Object Storage into Oracle Autonomous Data Warehouse you must store your object storage credentials in the data warehouse.

Sign in to the Oracle Cloud Infrastructure console, click the User icon, and select User Settings.
To create a new auth token, click Auth Tokens in the Resources section and then click Generate Token.
Provide a description for the Auth Token and click Generate Token. Copy the token when it is displayed.
Click the Copy link and store the token information in a secure location because when you close the dialog, you will not be able see the token in the console again.
Click Close.

Insert Data from Storage into Oracle Autonomous Data Warehouse

Use Oracle SQL Developer Web to insert the data into the data warehouse.

You must connect from Oracle SQL Developer Web to Oracle Autonomous Data Warehouse as the Admin user before you can execute the steps in this procedure.

In Oracle SQL Developer Web, execute the following SQL statements to create object store credentials substituting the indicated parameter and flag.
```
SET DEFINE OFF
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'ADW_CREDENTIALS',
        username => 'adwc_user@example.com',
        password => '<auth token>'
        );
END;
/
```
Set the following parameter and flag values:
- credential_name: The name of the credential to be stored.
- username: The user name listed under User Settings. If you are using a federated account, make sure to provide the complete username for example: <service_name>/<user_name>@example.com.
- password: The auth token you created earlier.

Validate the user name and password by listing the files in the object store bucket.

select object_name, bytes from dbms_cloud.list_objects('<Credential Name>','https://objectstorage.<region>.oraclecloud.com/n/<tenancy>/b/<Bucket name>/');

Use Oracle SQL Developer Web to create the table (EMP in this example) in the user schema with the following structure:
```
CREATE TABLE EMP(
  EMPNO  NUMBER(4,0),
  NAME   VARCHAR2(60),
  SAL    NUMBER(7,2),
);
```
You can copy the following data sample to a text file, upload it to object storage, and insert it into the data warehouse:
```
1001,John Dunbar,51000
1002,Larry Miller,52000
1003,Morgan Zhu,53000
1004,Robin Marlow,54000
1005,Sandy Sayers,55000
```

Use Oracle SQL Developer Web to load data to the table you just created.

BEGIN
   DBMS_CLOUD.COPY_DATA(
      table_name =>'EMP',
      credential_name =>'ADW_CREDENTIALS',
      file_uri_list =>'https://example.com/datafiles/emp.txt,
      format => json_object('delimiter' value ',')
   );
 END;
/

COMMIT;

Verify that the data was loaded into the table.
```
SELECT count(*) FROM emp
```