Oracle | DBMS

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 No

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