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
This package is not installed by default in version 20.3. Cloud types must be installed before the package.
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);
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);
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);
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;
/
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);
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;
/
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);
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;
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;
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);
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;
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);
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);
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);
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);