RishOraDev's Oracle Blogs

I have shown in my previous post, how you can create an Oracle ATP Database 23ai on Oracle Cloud free Tier. In this post let us understand, how we can create Upload a File containing data from OCI Object Storage to a table in the database, using the DBMS_CLOUD Package.

We have created a table EMP and we will load data into this table.

DROP TABLE EMP;

CREATE TABLE "EMP" 
   (	"EMPNO" NUMBER(4,0), 
        "ENAME" VARCHAR2(10) , 
        "JOB" VARCHAR2(20) , 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DATE, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2), 
        "DEPTNO" NUMBER(2,0)
    );

As you can see below, the table contains no records.

Let us see how we can insert data in the table.

We have the data in the form of a CSV file.

Create Bucket and Upload file

The first step is to login to your OCI account. From main menu navigate to Storage > Buckets.

Click on Create Bucket, if you don’t have one created already.

Enter a valid Bucket Name and click Create. Select the remaining options as required. I have left everything with the default settings. Click Create.

Once the bucket is created, click on the Bucket Name.

Scroll down and click on Upload to upload the csv file that you want to Import.

Browse and Select the csv file and click on Upload.

Close the page. Now you should be able to see the .csv file under Objects.

Once the file is uploaded, the next step is to load the data in the EMP table that we have created.

Data Load

As mentioned before, we’ll use the DBMS_CLOUD package to load the data. It’s a two step process. We will create the credentials first using the DBMS_CLOUD.CREATE_CREDENTIAL Procedure.

DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name   IN VARCHAR2,
	username          IN VARCHAR2,
	password          IN VARCHAR2 DEFAULT NULL);


DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name IN VARCHAR2,
	user_ocid       IN VARCHAR2,
	tenancy_ocid    IN VARCHAR2,
	private_key     IN VARCHAR2,
	fingerprint     IN VARCHAR2);

Where credential_name = The name of the credential to be stored. This is just a reference for the credentials, you can you any name here.

username = your cloud service username. To find the username, open the navigation menu and click Identity & Security. Under Identity, click Domains. Then click the name of the identity domain that you want to work in. You might need to change the compartment to find the domain that you want. Then, click Users. Click the username of a user to see their details.

password = your cloud service password. You can generate a password/token from Users page. Scroll down and click on Auth tokens. Click Generate token.

Provide a valid Description and click Generate token.

Once the token is generated, copy the token.

Now you have all the information you need to for calling the DBMS_CLOUD.CREATE_CREDENTIAL Procedure. Execute the procedure from a SQL window.

Next step, is to call the DBMS_CLOUD.COPY_DATA Procedure to load data into existing Oracle Cloud Database tables from files in the Cloud.

BMS_CLOUD.COPY_DATA (
	table_name        IN VARCHAR2,
	credential_name   IN VARCHAR2,		
	file_uri_list     IN CLOB,	
	schema_name       IN VARCHAR2,
	field_list        IN CLOB,
	format            IN CLOB);

DBMS_CLOUD.COPY_DATA (
	table_name        IN VARCHAR2,
	credential_name   IN VARCHAR2 DEFAULT NULL,		
	file_uri_list     IN CLOB DEFAULT NULL,	
	schema_name       IN VARCHAR2 DEFAULT NULL,
	field_list        IN CLOB DEFAULT NULL,
	format            IN CLOB DEFAULT NULL
	operation_id      OUT NOCOPY NUMBER);

Where table_name = The name of the target table on the database. 

credential_name = The name of the credential to we had created earlier, to access the Cloud Object Storage.

file_uri_list = Comma-delimited list of source file URL. To find the URL of the file that we had created, navigate back to the object storage, click on the details and then click on View Object Details.

Copy the URL.

schema_name = The name of the schema where the target table resides. The default value is NULL meaning the target table is in the same schema as the user running the procedure.

field_list = Identifies the fields in the source files and their data types. 

format = The options describing the format of the source files.

With the above information, let us call the DBMS_CLOUD procedure.

And voila!!! The data has been successfully inserted. Query the EMP table to confirm.

Hope this helps.

References

• DBMS_CLOUD