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;
/
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;
/
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;
/
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;
/
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
To delete all operation use DELETE_ALL_OPERATIONS procedure
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;
/
Thanks & Regards,
http://oracleracexpert.com, Oracle ACE
