Friday, August 26, 2022

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  

No comments:

Post a Comment