# [26ai] DBMS_CLOUD — Chapter One *If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/26ai-dbms-cloud-capitulo-uno)*.* Today, we're going to talk about *DBMS\_CLOUD* on 26ai, and how we use it our daily tasks such as saviing Data Pump backups or generating reports, because this package connects to OCI *Object Storage* . By default, *DBMS\_CLOUD* isn't set up on 26ai, we need to configure it with a valid user from our **tenancy**, Before getting our hands dirty, we must be absolutely sure that *DBMS\_CLOUD* is working our database. Just in case, below, you can see the Oracle's note on how to set up in our database: [***How To Setup And Use DBMS\_CLOUD Package (Doc ID 2748362.1)***](https://support.oracle.com/support/?anchorId=&kmContentId=2748362&page=sptemplate&sptemplate=km-article) After that, we need to configure a **token** or an **API Key** for our OCI account\*\*.\*\* This step is neccessary because our *Bucket* is private. So we need to create a **credential**. If our *Bucket* were public, this step would not be required. ### **Token** First all, we're going to generate a token for our OCI account. Let's go to the top right of the console and click **My profile**: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771410019467/915ef3cc-14f9-41d5-9e8f-feb4566a6036.png align="center") Click **Tokens and keys tab**: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771329298121/73011e3f-f972-434c-9386-e50f55798bf7.png align="center") Let's look at three sections: *API Keys*, *Auth Tokens* and *Customer Secrets Keys*. Our option is *Auth Tokens*: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771329519186/02251fb9-d8c1-4ecb-b635-417e614f8d0a.png align="center") Here, click in *Generate token*. We just need tofill in a name and clicking *Generate token*: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771329590592/50419b9d-06d2-476b-b216-4b8fc0a8f0c0.png align="center") We musn't close the next window before copying the token vale. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771329749038/4ebb4308-6ad9-4092-a766-1e20ff09a5b9.png align="center") After completing the previous step, Let's define the credential in our database using the token we just created. ### **Creation of the Credential** Let's use **DBMS\_CLOUD.CREATE\_CREDENTIAL**. This procedure receives the following parameters: * CREDENTIAL\_NAME: Name of the credential. * USERNAME:*/*. * Password: Token value that we generated in the previous step. ```sql SQL>EXEC dbms_cloud.create_credential( credential_name => 'DBA_IN_THE_SHADOW', username => '/', password => ''); SQL> R 1 select credential_name, 2 username, 3 enabled 4 from DBA_credentials 5* order by credential_name CREDENTIAL_NAME USERNAME ENABLED ------------------------- -------------------------------------------------- -------------------- DBA_IN_THE_SHADOW / TRUE ``` Let's check our credential using **DBMS\_CLOUD.LIST\_OBJECTS**. With this function, we can list the contents of the *Bucket*\*\*.\*\* Thus, we need to verify the connectivity with OCI. This function needs several parameters, such as name of the credential and the URL in order to access our*Bucket*. How do you get the url of our *Bucket*? The easiest way is to access an object in our *Bucket* and then go to the details: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771411079006/0d136145-6a8c-487d-b28a-7edff7d3abda.png align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771411298002/5eee5747-6775-4097-9424-7e33298cdd84.png align="center") Here, we copy of the “*URL path (URI)*”. After that, let's check the contents of our *Bucket* using SQL: ```sql SQL> Select object_name From Dbms_cloud.list_objects('DBA_IN_THE_SHADOW',''); OBJECT_NAME -------------------------------------------------------------------------------- XXXXXXXX.zip XXXXXXXX.zip XXXXXXXX.zip ``` Perfect, the access was sucessful. ### **Uploading files** In the next step, on one hand, we're going to create a **csv** file, and on the other hand we'll upload it to our *Bucket*. Let's use **DBMS\_CLOUD.PUT\_OBJECTS** in order to upload it. Before using it, we need to create a directory object in Oracle. ```sql SQL> ! cat generate.sql set heading off set feedback off set echo off set verify off set termout off set markup csv on spool myfile.csv Select trunc(dbms_random.value(1, 1000)), dbms_random.string('x', 10) From dual connect by level <= 10000; spool off SQL> @generate.sql SQL> ! ls -lac *csv -rw-r--r-- 1 oracle oinstall 168924 Feb 18 12:42 myfile.csv SQL> CREATE DIRECTORY OCI_HOME as '/home/oracle'; SQL> GRANT READ, WRITE ON DIRECTORY OCI_HOME TO PUBLIC; SQL> begin dbms_cloud.put_object ( credential_name => 'DBA_IN_THE_SHADOW', object_uri => '/myfile.csv', directory_name => 'OCI_HOME', file_name => 'myfile.csv'); end; / SQL> SQL> Select object_name From dbms_cloud.list_objects('DBA_IN_THE_SHADOW',''); OBJECT_NAME -------------------------------------------------------------------------------- XXXXXXXX.zip XXXXXXXX.zip XXXXXXXX.zip myfile.csv ``` Perfect, we have uploaded the file to our *Bucket*. As the file is in our Bucket, we can create an external table in order to read the file. The easiest way is to use **DBMS\_CLOUD.CREATE\_EXTERNAL\_TABLE**, because it generates a table in our schema. This table reads the file directly. ```sql SQL> BEGIN DBMS_CLOUD.CREATE_EXTERNAL_TABLE( table_name => 'EXT_MYFILE_CSV', credential_name => 'DBA_IN_THE_SHADOW', file_uri_list => '/myfile.csv', format => JSON_OBJECT( 'type' VALUE 'csv', 'delimiter' VALUE ',', 'quote' VALUE '"', 'skipheaders' VALUE '0' ), column_list => ' id NUMBER, code VARCHAR2(50) ' ); END; / SQL> SELECT COUNT(*) FROM EXT_MYFILE_CSV; 1000 SQL> SELECT * FROM EXT_MYFILE_CSV FETCH FIRST 25 ROWS ONLY ID CODE ---------- -------------------------------------------------- 224 3LU82RWW27 654 LASYRZWUUE 17 UW34WB3O4Y 764 5Z2I01ITAX 132 V4XCFDS9GA 131 6PRK6G0VTC 650 NST09S6RY9 133 R8IIPLJZ95 477 WK8Y1XC5XK 930 O574M5RJK2 371 P8G6PF91A9 214 WRUYMY4BWY 273 YD5NG441MV 706 DQYEBYNF5H 699 J3T3RHNHU0 744 VVXATGVW0N 945 U55X9EATLH 371 RHW44MZ1DQ 595 B1RU6VN33B 864 P02I2A9Z8S 218 R02XSI9MLY 980 M0CSWHE4G7 396 UZYFEL7UKI 619 DYKCFI912X 881 2TZ0GEQ6MM 25 rows selected. ``` We can also export the information to our *Bucket* using **DBMS\_CLOUD.EXPORT\_DATA**. This procedure has the following paramaters: * CREDENTIAL\_NAME: Name of the credential. * URL: “*URL path (URI)*”. * Format: In our case, the format is **json**. * Query: A valid SQL query. ```sql SQL> BEGIN DBMS_CLOUD.EXPORT_DATA( credential_name =>'DBA_IN_THE_SHADOW', file_uri_list =>'/config_system_parameter', format => json_object( 'type' VALUE 'json' ), query => 'SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER' ); END; / SQL> Select object_name From dbms_cloud.list_objects('DBA_IN_THE_SHADOW',''); OBJECT_NAME -------------------------------------------------------------------------------- XXXXXXXX.zip XXXXXXXX.zip XXXXXXXX.zip config_system_parameter_1_1_1.json myfile.csv ``` And if we need to load the file into a table, we can use **DBMS\_CLOUD.COPY\_DATA**. ```sql SQL> Create table BACK_SYSTEM_PARAMETER ( INFO_PARAMETER CLOB CONSTRAINT INFO_PARAMETER_JSON CHECK ( INFO_PARAMETER IS JSON ) ); Table created. SQL> begin DBMS_CLOUD.COPY_DATA ( table_name => 'BACK_SYSTEM_PARAMETER', credential_name => 'DBA_IN_THE_SHADOW', file_uri_list => '/config_system_parameter_1_1_1.json' ); end; / PL/SQL procedure successfully completed. ``` Before using the procedure, we're going to create *BACK\_SYSTEM\_PARAMETER table with a single column* in order to load the file. Let's query the table in order to see the contents of the file: ```sql SQL> select JSON_VALUE(INFO_PARAMETER, '$.NAME') AS NAME, JSON_VALUE(INFO_PARAMETER, '$.VALUE') AS VALUE from BACK_SYSTEM_PARAMETER Where INSTR(UPPER(JSON_VALUE(INFO_PARAMETER, '$.NAME')),'OPTIMIZER') != 0 FETCH FIRST 5 ROWS ONLY; NAME VALUE ---------------------------------------- ---------------------------------------- optimizer_ignore_hints FALSE optimizer_secure_view_merging TRUE optimizer_use_pending_statistics FALSE optimizer_capture_sql_plan_baselines FALSE optimizer_use_sql_plan_baselines TRUE 10 rows selected. ``` In the next article, we're explore how to use *Data Pump* using *DBMS\_CLOUD* Looking forward to seeing you in the next article :)