DBMS_CLOUD Package in Oracle 21c

In order to work with data in object stores you can either use DBMS_CLOUD package or manually define external table. The package was DBMS_CLOUD package introduced in Oracle Autonomous Database to work with an object store, we can also use in on-prem for versions 19c and 21c but you need to install manually. You can refer Oracle note How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1) for setup and other details.

You can run below command to create directory, grant access on directory and DBMS_CLOUD package to emp_usr user.

CREATE OR REPLACE DIRECTORY EMP_DIR AS '/oracle/emp_dir';

GRANT EXECUTE ON DBMS_CLOUD TO emp_usr;

GRANT READ, WRITE ON DIRECTORY emp_dir TO emp_usr, C##CLOUD$SERVICE;

Note DBMS_CLOUD is owned by a separate schema C##CLOUD$SERVICE, this user locked by default. Both emp_usr, C##CLOUD$SERVICE user needs to have access.

Credential - You can create credential using the CREATE_CREDENTIAL procedure for your object store.

GRANT CREATE CREDENTIAL TO emp_usr;

BEGIN

DBMS_CLOUD.CREATE_CREDENTIAL (

CREDENTIAL_NAME => 'cloud_obj_cred',

USERNAME => '<username>,

PASSWORD => '<user auth token>'

) ;

END;

/

You can use UPDATE_CREDENTIALS procedure to make changes to the credentials

BEGIN

DBMS_CREDENTIAL.UPDATE_CREDENTIAL(

CREDENTIAL_NAME => 'cloud_obj_cred',

ATTRIBUTE => 'username',

VALUE => 'emp_usr');

END;

/

To drop credential you can use DROP_CREDENTIAL procedure.

BEGIN

DBMS_CLOUD.DROP_CREDENTIAL(CREDENTIAL_NAME => 'cloud_obj_cred’);

END;

/

You can disable and enable the credentials using DISABLE_CREDENTIAL and ENABLE_CREDENTIAL

BEGIN

DBMS_CREDENTIAL.DISABLE_CREDENTIAL('cloud_obj_cred’);

END;

/

BEGIN

DBMS_CREDENTIAL.ENABLE_CREDENTIAL('cloud_obj_cred’);

END;

/

Object store - For managing the objects in the cloud object store, please use below commands

To transfer a file from direct to cloud object store use the PUT_OBJECT procedure

BEGIN

DBMS_CLOUD.PUT_OBJECT (

CREDENTIAL_NAME => 'cloud_obj_cred’,

OBJECT_URI => 'https://xxxxxxxxxxxx/emp_file.txt',

DIRECTORY_NAME => 'emp_dir',

FILE_NAME => 'emp_file.txt');

END;

/

In order to transfer a object from cloud object store to the directory use the GET_OBJECT procedure

BEGIN

DBMS_CLOUD.GET_OBJECT (

CREDENTIAL_NAME => 'cloud_obj_cred’,

OBJECT_URI => 'https://xxxxxxxxxxxx/emp_file.txt',

DIRECTORY_NAME => 'emp_dir',

FILE_NAME => 'emp_file.txt');

END;

/

You can use dbms_cloud.list_objects to lists objects in the object store and use GET_METADATA function In order to get specific object metadata

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS(CREDENTIAL_NAME => 'cloud_obj_cred’,LOCATION_URI => 'https://xxxxxxxxxxx');

SELECT * FROM DBMS_CLOUD.GET_METADATA(CREDENTIAL_NAME => 'cloud_obj_cred’,LOCATION_URI => 'https://xxxxxxxxxxx/emp_file.txt') AS METADATA FROM DUAL;

You can delete objects from the cloud object store using DELETE_OBJECT procedure

 

BEGIN

DBMS_CLOUD.DELETE_OBJECT(

CREDENTIAL_NAME => 'cloud_obj_cred’,

OBJECT_URI => 'https://xxxxxxxx/emp_file.txt');

END;

/

You can delete files from the directory using DELETE_FILE procedure

 

BEGIN

DBMS_CLOUD.DELETE_FILE(

DIRECTORY_NAME => 'emp_dir',

FILE_NAME => 'emp_file.txt');

END;

/

To list the files in a directory use LIST_FILES function

SELECT * FROM DBMS_CLOUD.LIST_FILES(DIRECTORY_NAME => 'emp_dir');

To export data into a cloud object store in the required format use EXPORT_DATA procedure

BEGIN

DBMS_CLOUD.EXPORT_DATA (

CREDENTIAL_NAME => 'cloud_obj_cred’,

FILE_URI_LIST => 'https://xxxxxxxxxxx/emp_file.csv',

QUERY => 'select * from emp',

FORMAT => '{"TYPE" : "csv"}');

END;

/

To delete all operation use DELETE_ALL_OPERATIONS procedure

BEGIN

DBMS_CLOUD.DELETE_ALL_OPERATIONS;

END;

/

To delete specific operation use DELETE_OPERATIONS procedure , but you need to provide operation ID

BEGIN

DBMS_CLOUD.DELETE_OPERATION(<id>);

END;

/

If you enter problems with DBMS_CLOUD with the user or role you can test the configuration using the same sample code that was used for the DBMS_CLOUD setup .

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE