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
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
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);