Oracle DBMS_CLOUD
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This package  contains procedures to import data from the Oracle Cloud Object Store to Oracle database.
AUTHID CURRENT_USER
Constants
Name Data Type Value
 Compression schemes supported for objects
COMPRESS_NONE dbms_id NULL
COMPRESS_AUTO dbms_id 'AUTO'
COMPRESS_BZIP2 dbms_id 'BZIP2'
COMPRESS_DETECT dbms_id 'DETECT'
COMPRESS_GZIP dbms_id 'GZIP'
COMPRESS_ZLIB dbms_id 'ZLIB'
 Data Pump Compression Values
COMPRESS_BASIC VARCHAR2(10) 'BASIC'
COMPRESS_LOW VARCHAR2(10) 'LOW'
COMPRESS_MEDIUM VARCHAR2(10) 'MEDIUM''
COMPRESS_HIGH VARCHAR2(10) 'HIGH'
 Format Option JSON keys in create_external_table / copy_data Record Parameters
FORMAT_CHARACTERSET dbms_id 'characterset'
FORMAT_COMPRESSION dbms_id 'compression'
FORMAT_ESCAPE dbms_id 'escape'
FORMAT_IGN_BLANK_LINES dbms_id 'ignoreblanklines'
FORMAT_LANGUAGE dbms_id 'language'
FORMAT_READSIZE dbms_id 'readsize'
FORMAT_RECORD_DELIMITER dbms_id 'recorddelimiter'
FORMAT_SKIP_HEADERS dbms_id 'skipheaders'
FORMAT_TERRITORY dbms_id 'territory'
 Format Option JSON keys in create_external_table / copy_data Field Parameters
FORMAT_BLANK_AS_NULL dbms_id 'blankasnull'
FORMAT_CONVERSION_ERRORS dbms_id 'conversionerrors'
FORMAT_DATE dbms_id 'dateformat'
FORMAT_END_QUOTE dbms_id 'endquote'
FORMAT_FIELD_DELIMITER dbms_id 'delimiter'
FORMAT_IGN_MISSING_COLS dbms_id 'ignoremissingcolumns'
FORMAT_NUMBER_FORMAT dbms_id 'numberformat'
FORMAT_NUMERIC_CHARS dbms_id 'numericcharacters'
FORMAT_QUOTE dbms_id 'quote'
FORMAT_REMOVE_QUOTES dbms_id 'removequotes'
FORMAT_TIMESTAMP dbms_id 'timestampformat'
FORMAT_TIMESTAMP_LTZ dbms_id 'timestampltzformat'
FORMAT_TIMESTAMP_TZ dbms_id 'timestamptzformat'
FORMAT_TRIM_SPACES dbms_id 'trimspaces'
FORMAT_TRUNCATE_COLUMNS dbms_id 'truncatecol'
FORMAT_TYPE dbms_id 'type'
 Format Option JSON keys in create_external_table / copy_data Big Data Parameters
FORMAT_BD_CRED_NAME dbms_id 'credential.name'
FORMAT_BD_FILE_FORMAT dbms_id 'fileformat'
FORMAT_BD_PREFIX dbms_id 'com.oracle.bigdata.'
FORMAT_BD_SCHEMA dbms_id 'schema'
FORMAT_BD_SCHEMA_ALL dbms_id 'all'
FORMAT_BD_SCHEMA_FIRST dbms_id 'first'
FORMAT_BD_SCHEMA_NONE dbms_id 'none'
 Reject Limit
FORMAT_REJECT_LIMIT dbms_id 'rejectlimit'
 Values for FORMAT_TYPE JSON Key
FORMAT_TYPE_AVRO dbms_id 'AVRO'
FORMAT_TYPE_CSV dbms_id 'CSV'
FORMAT_TYPE_CSV_WITH _EMBEDDED dbms_id 'CSV WITH EMBEDDED'
FORMAT_TYPE_CSV_WITHOUT _EMBEDDED dbms_id FORMAT_TYPE_CSV
FORMAT_TYPE_PARQUET dbms_id 'PARQUET'
 FORMAT_CONVERSION_ERROR JSON Key
FORMAT_CONVERR_REJECT_RECORD dbms_id 'REJECT_RECORD'
FORMAT_CONVERR_STORE_NULL dbms_id 'STORE_NULL'
 DataPump
FORMAT_ENCRYPTION dbms_id 'encryption'
FORMAT_VERSION dbms_id 'version
 DataPump Versions
VERSION_COMPATIBLE dbms_id 'COMPATIBLE'
VERSION_LATEST dbms_id 'LATEST'
 SODA
FORMAT_UNPACKARRAYS dbms_id 'unpackarrays'
 Trim
FORMAT_TRIM_LDRTRIM dbms_id 'LDRTRIM'
FORMAT_TRIM_LTRIM dbms_id 'LTRIM'
FORMAT_TRIM_LRTRIM dbms_id 'LRTRIM'
FORMAT_TRIM_NOTRIM dbms_id 'NOTRIM'
FORMAT_TRIM_RTRIM dbms_id 'RTRIM'
 List Object Fields
LIST_OBJ_FIELD_NAME dbms_id 'name'
LIST_OBJ_FIELD_BYTES dbms_id 'bytes'
LIST_OBJ_FIELD_CHECKSUM dbms_id 'checksum'
LIST_OBJ_FIELD_CREATED dbms_id 'created'
LIST_OBJ_FIELD_CREATED_FMT dbms_id 'created_fmt'
LIST_OBJ_FIELD_LASTMOD dbms_id 'last_modified'
LIST_OBJ_FIELD_LASTMOD_FMT dbms_id 'last_modified_fmt'
 HTTPS Request Methods
METHOD_GET dbms_id 'GET'
METHOD_PUT dbms_id 'PUT'
METHOD_HEAD dbms_id 'HEAD'
METHOD_POST dbms_id 'POST'
METHOD_DELETE dbms_id 'DELETE'
 Special Value for FORMAT_FIELD_DELIMITER JSON Key
FORMAT_DELIMITER_WHITESPACE dbms_id 'WHITESPACE'
Dependencies
DBMS_CLOUD_TYPES DBMS_STANDARD USER_LOAD_OPERATIONS
Documented Yes: Packages and Types Reference
Exceptions
Error Code Reason
ORA-20003 EXCP_REJECT_LIMIT
ORA-20004 EXCP_CRED_NOT_EXIST
ORA-20005 EXCP_TABLE_NOT_EXIST
ORA-20006 EXCP_UNSUPP_OBJ_STORE
ORA-20007 EXCP_INVALID_SQL_NAME
ORA-20008 EXCP_IDEN_TOO_LONG
ORA-20009 EXCP_INVALID_FORMAT
ORA-20010 EXCP_MISSING_CRED
ORA-20011 EXCP_INVALID_OBJ_URI
ORA-20012 EXCP_INVALID_PART_CLAUSE
ORA-20013 EXCP_UNSUPP_FEATURE
ORA-20014 EXCP_PART_NOT_EXIST
ORA-20015 EXCP_INVALID_QUAL_SQL_NAME
ORA-20016 EXCP_INVALID_TABLE_NAME
ORA-20017 EXCP_INVALID_SCHEMA_NAME
ORA-20018 EXCP_INVALID_DIR_NAME
ORA-20019 EXCP_INVALID_FILE_NAME
ORA-20020 EXCP_INVALID_CRED_ATTR
ORA-20021 EXCP_TABLE_EXIST
ORA-20022 EXCP_CRED_EXIST
ORA-20023 EXCP_INVALID_REQ_METHOD
ORA-20024 EXCP_INVALID_REQ_HEADER
ORA-20025 EXCP_FILE_NOT_EXIST
ORA-20026 EXCP_INVALID_RESPONSE
ORA-20027 EXCP_INVALID_OPERATION
ORA-20028 EXCP_INVALID_USER_NAME
ORA-20029 EXCP_INVALID_CHAR_SET
ORA-20030 EXCP_INVALID_ENC_KEY_ATTR
First Available 20c
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbms_cloud.sql
SQL> @?/rdbms/admin/dbms_cloud.sql

Session altered.

SP2-0808: Package created with compilation warnings

Errors for PACKAGE DBMS_CLOUD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
431/9 PLW-06010: keyword "ATTRIBUTE" used as a defined name
432/9 PLW-06010: keyword "VALUE" used as a defined name
482/9 PLW-06010: keyword "FORMAT" used as a defined name
542/9 PLW-06010: keyword "FORMAT" used as a defined name
606/9 PLW-06010: keyword "FORMAT" used as a defined name
909/9 PLW-06010: keyword "FORMAT" used as a defined name
918/9 PLW-06010: keyword "FORMAT" used as a defined name
980/5 PLW-06010: keyword "FORMAT" used as a defined name
987/5 PLW-06010: keyword "FORMAT" used as a defined name
1041/5 PLW-06010: keyword "FORMAT" used as a defined name
1048/5 PLW-06010: keyword "FORMAT" used as a defined name
1130/9 PLW-06010: keyword "TYPE" used as a defined name
1495/5 PLW-06010: keyword "BODY" used as a defined name

Synonym created.

Session altered.
Subprograms
COPY_COLLECTION DISABLE_CREDENTIAL LIST_FILES
COPY_DATA DROP_CREDENTIAL LIST_OBJECTS
CREATE_CREDENTIAL ENABLE_CREDENTIAL PUT_OBJECT
CREATE_EXTERNAL_PART_TABLE EXPORT_DATA RESIGNAL_USER_ERROR
CREATE_EXTERNAL_TABLE GET_METADATA SEND_REQUEST
CREATE_HYBRID_PART_TABLE GET_OBJECT UPDATE_CREDENTIAL
DELETE_ALL_OPERATIONS GET_RESPONSE_HEADERS VALIDATE_EXTERNAL_PART_TABLE
DELETE_FILE GET_RESPONSE_RAW VALIDATE_EXTERNAL_TABLE
DELETE_OBJECT GET_RESPONSE_STATUS_CODE VALIDATE_HYBRID_PART_TABLE
DELETE_OPERATION GET_RESPONSE_TEXT  
 
COPY_COLLECTION (new 21c)
Load Data from Object Store to Oracle SODA Collection

Overload 1		 dbms_cloud.copy_collection(
collection_name IN VARCHAR2,
credential_name IN VARCHAR2 DEFAULT NULL,
file_uri_list   IN CLOB,
format          IN CLOB     DEFAULT NULL);
BEGIN
  dbms_cloud.copy_collection(
   collection_name => 'MyCollection',
   credential_name => 'MY_CRED',
   file_uri_list => 'https://objectstore.com/bucket/myjson.json',
   format => json_object('unpackarrays' value 'true'));
END;
/
Overload 2 dbms_cloud.copy_collection(
collection_name IN         VARCHAR2,
credential_name IN         VARCHAR2 DEFAULT NULL,
file_uri_list   IN         CLOB,
format          IN         CLOB DEFAULT NULL,
operation_id    OUT NOCOPY NUMBER);
TBD
 
COPY_DATA (new 20c)
Copies data from the Object Store to an Oracle Database

Overload 1		 dbms_cloud.copy_data(
table_name      IN VARCHAR2,
credential_name IN VARCHAR2 DEFAULT NULL,
file_uri_list   IN CLOB,
schema_name     IN VARCHAR2 DEFAULT NULL,
field_list      IN CLOB     DEFAULT NULL,
format          IN CLOB     DEFAULT NULL);
BEGIN
  dbms_cloud.copy_data(
   table_name => 'EMPLOYEES',
   credential_name => 'UW_AWSCRED',
   file_uri_list => 'https://objectstore.com/bucket/bgfile.csv',
   format => '{"type" : "CSV"}');
END;
/
Overload 2 dbms_cloud.copy_data(
table_name      IN         VARCHAR2,
credential_name IN         VARCHAR2 DEFAULT NULL,
file_uri_list   IN         CLOB,
schema_name     IN         VARCHAR2 DEFAULT NULL,
field_list      IN         CLOB     DEFAULT NULL,
format          IN         CLOB     DEFAULT NULL,
operation_id    OUT NOCOPY NUMBER);
DECLARE
 outVal NUMBER;
BEGIN
  dbms_cloud.copy_data(
   table_name => 'EMPLOYEES',
   credential_name => 'UW_AWSCRED',
   file_uri_list => 'https://objectstore.com/bucket/bgfile.csv',
   format => '{"type" : "CSV"}',
   outVal);
  dbms_output.put_line(TO_CHAR(outVal));
END;
/
 
CREATE_CREDENTIAL (new 21c overload)
Creates a credential object to access any Object Store

The examples, at right, are based on the ones in the installation file.

If you are paying attention this procedure is a gross security violation and will, therefore, be written up at dbsecworx.

Overload 1		 dbms_cloud.create_credential(
credential_name IN VARCHAR2,
username        IN VARCHAR2,
password        IN VARCHAR2 DEFAULT NULL);
-- Swift ObjectStore

BEGIN
  dbms_cloud.create_credential('UW_OCICRED', 'C##UWCLASS', 'Yy53$x7dpf6c');
END;
/

-- Amazon S3

BEGIN
  dbms_cloud.create_credential('UW_AWSCRED', '<access_key_value>', '<secret_key_value>');
END;
/
Overload 2 dbms_cloud.create_credential(
credential_name IN VARCHAR2,
user_ocid       IN VARCHAR2,
tenancy_ocid    IN VARCHAR2,
private_key     IN VARCHAR2,
fingerprint     IN VARCHAR2);
TBD
 
CREATE_EXTERNAL_PART_TABLE (new 21c)
Create External Partitioned Table on file in Object Store

Demo code, at right, from Oracle		 dbms_cloud.create_external_part_table(
table_name          IN VARCHAR2,
credential_name     IN VARCHAR2 DEFAULT NULL,
partitioning_clause IN CLOB,
column_list         IN CLOB     DEFAULT NULL,
field_list          IN CLOB     DEFAULT NULL,
format              IN CLOB     DEFAULT NULL);
BEGIN
  dbms_cloud.create_external_part_table(
    table_name => 'FOO',
    credential_name => 'MY_AWS_CRED_OBJECT',
    partitioning_clause => 'partition by range (col1)
   (partition p1 values less than (1000) location
   (''https://objectstore.com/bucket/bgfile.csv''))',
   column_list => 'p1 SALARY, emp_name VARCHAR2(128)',
   format => '{"type" : "CSV"}');
END;
/
 
CREATE_EXTERNAL_TABLE (new 20c)
Creates an External Table on an Object Store file

Demo code, at right, from Oracle		 dbms_cloud.create_external_table(
table_name      IN VARCHAR2,
credential_name IN VARCHAR2 DEFAULT NULL,
file_uri_list   IN CLOB,
column_list     IN CLOB     DEFAULT NULL,
field_list      IN CLOB     DEFAULT NULL,
format          IN CLOB     DEFAULT NULL);
BEGIN
  dbms_cloud.create_external_table(
   table_name => 'FOO',
   credential_name => 'UW_AWSCRED',
   file_uri_list => 'https://objectstore.com/bucket/bgfile.csv',
   column_list => 'emp_no NUMBER, emp_name VARCHAR2(128)',
   format => '{"type" : "CSV"}');
END;
/
 
CREATE_HYBRID_PART_TABLE (new 21c)
Create Hybrid Partitioned Table on file in Object Store

Demo code, at right, from Oracle		 dbms_cloud.create_hybrid_part_table(
table_name          IN VARCHAR2,
credential_name     IN VARCHAR2 DEFAULT NULL,
partitioning_clause IN CLOB,
column_list         IN CLOB     DEFAULT NULL,
field_list          IN CLOB     DEFAULT NULL,
format              IN CLOB     DEFAULT NULL);
BEGIN
  dbms_cloud.create_hybrid_part_table(
   table_name => 'FOO',
   credential_name => 'MY_AWS_CRED_OBJECT',
   partitioning_clause => 'partition by range (col1)
   (partition p1 values less than (1000) location
   (''https://objectstore.com/bucket/bgfile.csv''))',
   column_list => 'p1 SALARY, emp_name VARCHAR2(128)',
   format => '{"type" : "CSV"}');
END;
/
 
DELETE_ALL_OPERATIONS (new 20c)
Deletes all Cloud Data Access operations dbms_cloud.delete_all_operations(type IN VARCHAR DEFAULT NULL);
exec dbms_cloud.delete_all_operations;
 
DELETE_FILE (new 20c)
Deletes a directory object file

Demo from installation file		 dbms_cloud.delete_file(
directory_name IN VARCHAR2,
file_name      IN VARCHAR2);
exec dbms_cloud.delete_file('TEST_DIR', 'bgfile.csv');
 
DELETE_OBJECT (new 20c)
Deletes a Cloud Store object dbms_cloud.delete_object(
credential_name IN VARCHAR2 DEFAULT NULL,
object_uri      IN VARCHAR2);
exec dbms_cloud.delete_object(
   credential_name => 'MY_AWS_CRED_OBJECT',
   object_uri => 'https://objectstore.com/bucket/bgfile.csv');
END;
/
 
DELETE_OPERATION (new 20c)
Deletes an operation for cloud data access dbms_cloud.delete_operation(id IN NUMBER);
DECLARE
 opid NUMBER;
BEGIN
  SELECT MIN(id) INTO opid FROM user_load_operations;
  dbms_cloud.delete_operation(id);
END;
/
 
DISABLE_CREDENTIAL (new 20c)
Disable a Credential object to access Object Store dbms_cloud.disable_credential(credential_name IN VARCHAR2);
exec dbms_cloud.disable_credential('UWOCI_CRED');
 
DROP_CREDENTIAL (new 20c)
Drops a Credential object to access any Object Store dbms_cloud.drop_credential(credential_name IN VARCHAR2);
exec dbms_cloud.drop_credential('UWOCI_CRED');
 
ENABLE_CREDENTIAL (new 20c)
Enables a Credential object to access Object Store dbms_cloud.enable_credential(credential_name IN VARCHAR2);
exec dbms_cloud.enable_credential('UWOCI_CRED');
 
EXPORT_DATA (new 21c)
Export Data from Oracle Database to Object Store

Demo code, at right, from Oracle

Overload 1		 dbms_cloud.export_data(
credential_name IN VARCHAR2 DEFAULT NULL,
file_uri_list   IN CLOB,
query           IN CLOB,
format          IN CLOB DEFAULT NULL);
BEGIN
  dbms_cloud.export_data(
   credential_name => 'MY_CRED',
   file_uri_list => 'https://objectstore.com/bucket/myjson.json',
   query => 'select * from dept',
   format => json_object('compression' value 'basic'));
END;
/
Overload 2 dbms_cloud.export_data(
PROCEDURE export_data(
credential_name IN         VARCHAR2 DEFAULT NULL,
file_uri_list   IN         CLOB,
query           IN         CLOB,
format          IN         CLOB DEFAULT NULL,
operation_id    OUT NOCOPY NUMBER);
TBD
 
GET_METADATA (new 20c)
Returns metadata for a Cloud Store object

Example from the installation file and was corrected by dropping the final comma		 dbms_cloud.get_metadata(
credential_name IN VARCHAR2 DEFAULT NULL,
object_uri      IN VARCHAR2)
RETURN CLOB;
DECLARE
 l_metadata CLOB;
BEGIN
  l_metadata := dbms_cloud.get_metadata(
   credential_name => 'UW_AWSCRED',
   object_uri => 'https://objectstore.com/bucket/bgfile.csv');
END;
/
 
GET_OBJECT (new 20c)
Returns the contents of an object in the Cloud Store

Overload 1

Example from the installation file		 dbms_cloud.get_object(
credential_name IN VARCHAR2 DEFAULT NULL,
object_uri      IN VARCHAR2,
startOffset     IN NUMBER   DEFAULT 0,
endOffset       IN NUMBER   DEFAULT 0,
compression     IN VARCHAR2 DEFAULT NULL)
RETURN BLOB;
DECLARE
 l_contents BLOB;
BEGIN
 l_contents := dbms_cloud.get_object(credential_name => 'UW_AWSCRED',
  object_uri => 'https://objectstore.com/bucket/bgfile.csv',);
END;
/
Overload 2

The example in the installation file appears to be invalid and has been corrected here		 dbms_cloud.get_object(
credential_name IN VARCHAR2 DEFAULT NULL,
object_uri      IN VARCHAR2,
directory_name  IN VARCHAR2,
file_name       IN VARCHAR2 DEFAULT NULL,
startOffset     IN NUMBER   DEFAULT 0,
endOffset       IN NUMBER   DEFAULT 0,
compression     IN VARCHAR2 DEFAULT NULL)
RETURN BLOB;
BEGIN
  dbms_cloud.get_object(credential_name => 'UW_AWSCRED',
   object_uri     => 'https://objectstore.com/bucket/bgfile.csv',
   directory_name => 'TEST_DIR',
   compression    => dbms_cloud.compress_auto);
END;
/
 
GET_RESPONSE_HEADERS (new 21c)
Returns Response Headers dbms_cloud.get_response_headers(resp IN dbms_cloud_types.resp) RETURN json_object_t;
TBD
 
GET_RESPONSE_RAW (new 21c)
Get response body as raw dbms_cloud.get_response_raw(resp IN dbms_cloud_types.resp) RETURN BLOB;
TBD
 
GET_RESPONSE_STATUS_CODE (new 21c)
Get response status code dbms_cloud.get_response_status_code(resp IN dbms_cloud_types.resp)
RETURN PLS_INTEGER;
TBD
 
GET_RESPONSE_TEXT (new 21c)
Get response body as text dbms_cloud.get_response_text(resp IN dbms_cloud_types.resp) RETURN CLOB;
TBD
 
LIST_FILES (new 20c)
List files at a given directory object location dbms_cloud.list_files(directory_name IN VARCHAR2)
RETURN dbms_cloud_types.list_object_ret_tab PIPELINED;
SELECT * FROM TABLE(dbms_cloud.list_files('TEST_DIR');
 
LIST_OBJECTS (new 20c)
List objects at a given location in Cloud Store dbms_cloud.list_objects(
credential_name IN VARCHAR2 DEFAULT NULL,
location_uri    IN VARCHAR2)
RETURN dbms_cloud_types.list_object_ret_tab PIPELINED;
SELECT * FROM TABLE(dbms_cloud.list_files('UW_AWSCRED', 'https://objectstore.com/bucket/bgfile.csv');
 
PUT_OBJECT (new 20c)
Puts the contents in an object into the Cloud Store

Demo from installation file

Overload 1		 dbms_cloud.put_object(
credential_name IN VARCHAR2 DEFAULT NULL,
object_uri      IN VARCHAR2,
contents        IN BLOB,
compression     IN VARCHAR2 DEFAULT NULL);
DECLARE
 l_contents BLOB;
BEGIN
  l_contents := EMPTY_BLOB();
  dbms_cloud.put_object(
   credential_name => 'UW_AWSCRED',
   object_uri      => 'https://objectstore.com/bucket/bgfile.csv',
   contents        => l_contents,
   compression     => DBMS_CLOUD.COMPRESS_AUTO);
END;
/
Overload 2

The installation file demo appears to be invalid and has been corrected here		 dbms_cloud.put_object(
credential_name IN VARCHAR2 DEFAULT NULL,
object_uri      IN VARCHAR2,
directory_name  IN VARCHAR2,
file_name       IN VARCHAR2,
compression     IN VARCHAR2 DEFAULT NULL);
BEGIN
  dbms_cloud.put_object(credential_name => 'UW_AWSCRED',
   object_uri     => 'https://objectstore.com/bucket/bgfile.csv',
   directory_name => 'TEST_DIR',
   file_name      => 'bgfile.csv'
   compression    => OCA.COMPRESS_AUTO
END;
/
 
RESIGNAL_USER_ERROR (new 20c)
Resignals a user error from the error stack dbms_cloud.resignal_user_error(log_table IN VARCHAR2 DEFAULT NULL);
exec dbms_cloud.resignal_user_error;
 
SEND_REQUEST (new 21c)
Send an HTTP request dbms_cloud.update_send_request(
credential_name IN VARCHAR2,
uri             IN VARCHAR2,
method          IN VARCHAR2,
headers         IN CLOB DEFAULT NULL,
body            IN BLOB DEFAULT NULL)
RETURN dbms_cloud_types.resp;
TBD
 
UPDATE_CREDENTIAL (new 20c)
Updates a Credential object to access Object Store

Attribute arguments:
-- 1. username
-- 2. tenancy_ocid
-- 3. user_ocid
-- 4. private_key
-- 5. public_key
-- 6. region		 dbms_cloud.update_credential(
credential_name IN VARCHAR2,
attribute       IN VARCHAR2,
value           IN VARCHAR2);
exec dbms_cloud.update_credential('UWOCI_CRED', 'USERNAME', 'SCOTT');
 
VALIDATE_EXTERNAL_PART_TABLE (new 21c)
Validates the data of a partitioned external table over object store file by querying the data in the external table and generating a logfile and badfile tables to review the results

Overload 1		 dbms_cloud.validate_external_part_table(
table_name               IN VARCHAR2,
partition_name           IN VARCHAR2 DEFAULT NULL,
subpartition_name        IN VARCHAR2 DEFAULT NULL,
schema_name              IN VARCHAR2 DEFAULT NULL,
rowcount                 IN NUMBER   DEFAULT 0,
partition_key_validation IN BOOLEAN  DEFAULT FALSE,
stop_on_error            IN BOOLEAN  DEFAULT TRUE);
TBD
Overload 2 dbms_cloud.validate_external_part_table(
table_name               IN         VARCHAR2,
operation_id             OUT NOCOPY NUMBER,
partition_name           IN         VARCHAR2 DEFAULT NULL,
subpartition_name        IN         VARCHAR2 DEFAULT NULL,
schema_name              IN         VARCHAR2 DEFAULT NULL,
rowcount                 IN         NUMBER   DEFAULT 0,
partition_key_validation IN         BOOLEAN  DEFAULT FALSE,
stop_on_error            IN         BOOLEAN  DEFAULT TRUE);
TBD
 
VALIDATE_EXTERNAL_TABLE (new 20c)
Validates an External Table on an Object Store file

Demo is from the installation file

Overload 1		 dbms_cloud.validate_external_table(
table_name    IN VARCHAR2,
schema_name   IN VARCHAR2 DEFAULT NULL,
rowcount      IN NUMBER   DEFAULT 0,
stop_on_error IN BOOLEAN  DEFAULT TRUE);
BEGIN
  dbms_cloud.validate_external_table(
   table_name  => 'FOO',
   schema_name => 'SCOTT',
   rowcount => 100);
END;
/
Overload 2 dbms_cloud.validate_external_table(
table_name    IN         VARCHAR2,
operation_id  OUT NOCOPY NUMBER,
schema_name   IN         VARCHAR2 DEFAULT NULL,
rowcount      IN         NUMBER   DEFAULT 0,
stop_on_error IN         BOOLEAN  DEFAULT TRUE);
DECLARE
 outVal NUMBER;
BEGIN
  dbms_cloud.validate_external_table(
   table_name   => 'FOO',
   operation_id => outVal,
   schema_name  => 'SCOTT',
   rowcount     => 100);

  dbms_output.put_line(TO_CHAR(outVal));
END;
/
 
VALIDATE_HYBRID_PART_TABLE (new 21c)
ValidatesHybrid Partitioned Table on file in Object Store

Overload 1		 dbms_cloud.validate_hybrid_part_table(
table_name               IN VARCHAR2,
partition_name           IN VARCHAR2 DEFAULT NULL,
subpartition_name        IN VARCHAR2 DEFAULT NULL,
schema_name              IN VARCHAR2 DEFAULT NULL,
rowcount                 IN NUMBER   DEFAULT 0,
partition_key_validation IN BOOLEAN  DEFAULT FALSE,
stop_on_error            IN BOOLEAN  DEFAULT TRUE);
TBD
Overload 2 dbms_cloud.validate_hybrid_part_table(
table_name               IN         VARCHAR2,
operation_id             OUT NOCOPY NUMBER,
partition_name           IN         VARCHAR2 DEFAULT NULL,
subpartition_name        IN         VARCHAR2 DEFAULT NULL,
schema_name              IN         VARCHAR2 DEFAULT NULL,
rowcount                 IN         NUMBER   DEFAULT 0,
partition_key_validation IN         BOOLEAN  DEFAULT FALSE,
stop_on_error            IN         BOOLEAN  DEFAULT TRUE);
TBD

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_CLOUD
DBMS_CLOUD_ADMIN_INTERNAL
DBMS_CLOUD_CAPABILITY
DBMS_CLOUD_CORE
DBMS_CLOUD_INTERNAL
DBMS_CLOUD_MACADM
DBMS_CLOUD_REQUEST
DBMS_CLOUD_TASK
DBMS_CLOUD_TYPES
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx