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