Oracle DBMS_CLOUD
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx