Oracle | DBMS

Oracle DBMS_CLOUD Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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
AWS ARN Credential
AWS_ARN_CRED	dbms_id	'AWS$ARN'
AWS ARN Based Credential External ID Types
COMPARTMENT_OCID	dbms_id	'COMPARTMENT_OCID'
DATABASE_OCID	dbms_id	'DATABASE_OCID'
TENANT_OCID	dbms_id	'TENANT_OCID'
AWS ARN Based Credential Maximum Session Duration (in minutes)
AWS_DEFAULT_MAX_SESSION_ DURATION	NUMBER	60
AWS_MAXIMIM_MAX_SESSION_ DURATION	NUMBER	720
AWS_MINIMUM_MAX_SESSION_ DURATION	NUMBER	15
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'
DataPump
FORMAT_ENCRYPTION	dbms_id	'encryption'
FORMAT_VERSION	dbms_id	'version
DataPump Versions
VERSION_COMPATIBLE	dbms_id	'COMPATIBLE'
VERSION_LATEST	dbms_id	'LATEST'
FORMAT_CONVERSION_ERROR JSON Key
FORMAT_CONVERR_REJECT_RECORD	dbms_id	'REJECT_RECORD'
FORMAT_CONVERR_STORE_NULL	dbms_id	'STORE_NULL'
FORMAT Endian Json Keys
FORMAT_BIG_ENDIAN	dbms_id	'BIG'
FORMAT_LITTLE_ENDIAN	dbms_id	'LITTLE'
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_ENDIAN	dbms_id	'endian'
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'
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'
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'
Reject Limit
FORMAT_REJECT_LIMIT	dbms_id	'rejectlimit'
SODA
FORMAT_JSON_DOC_MAXSIZE	dbms_id	'maxdocsize'
FORMAT_JSON_PATH	dbms_id	'jsonpath'
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'
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_DATAPUMP	dbms_id	'DATAPUMP'
FORMAT_TYPE_EJSON	dbms_id	'EJSON'
FORMAT_TYPE_JSON	dbms_id	'JSON'
FORMAT_TYPE_ORC	dbms_id	'ORC'
FORMAT_TYPE_PARQUET	dbms_id	'PARQUET'
Special Value for FORMAT_FIELD_DELIMITER JSON Key
FORMAT_DELIMITER_WHITESPACE	dbms_id	'WHITESPACE'
OCI Resource Principal Credential
OCI_RPST_CRED	dbms_id	'OCI$RESOURCE_PRINCIPAL'
Additional Format Parameters
FORMAT_LOG_DIR	dbms_id	'logdir'

Dependencies

ALL_EXTERNAL_TABLES	DBMS_CLOUD_TYPES	DUAL
DBMS_ASSERT	DBMS_LOB	JSON_ARRAY_T
DBMS_CLOUD_ADMIN	DBMS_PRIV_CAPTURE	JSON_ELEMENT_T
DBMS_CLOUD_ADMIN_INTERNAL	DBMS_RANDOM	JSON_KEY_LIST
DBMS_CLOUD_CORE	DBMS_SESSION	JSON_OBJECT_T
DBMS_CLOUD_INTERNAL	DBMS_SQL	ODCIVARCHAR2LIST
DBMS_CLOUD_MACADM	DMS_STANDARD	PLITBLM
DBMS_CLOUD_REQUEST	DBMS_SYS_SQL	UTL_HTTP
DBMS_CLOUD_TASK	DBMS_UTILITY	UTL_RAW

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
ORA-20031	EXCP_RPST_ENABLED
ORA-20032	EXCP_INVALID_API_RESULT_CACHE_SIZE
ORA-20033	EXCP_INVALID_EXTERNAL_ID_TYPE
ORA-20034	EXCP_AWS_ARN_DISABLED
ORA-20035	EXCP_INVALID_MAX_SESSION_DURATION
ORA-20036	EXCP_INVALID_CRYPTO_ENDPOINT
ORA-20037	EXCP_MASTER_KEY_ID_NOT_EXIST
ORA-20038	EXCP_MASTER_KEY_ID_DISABLED
ORA-20039	EXCP_CUSTOMER_MANAGED_KEY_ERROR
ORA-20040	EXCP_ORACLE_MANAGED_KEY_ERROR
ORA-20041	EXCP_INVALID_CRED_PARAMS
ORA-20042	EXCP_INVALID_API_RESULT_CACHE_SCOPE

First Available 20c

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/dbms_cloud.sql

-- prerequisites
{ORACLE_HOME}/rdbms/admin/dbms_cloud_task_catalog.sql
{ORACLE_HOME}/rdbms/admin/dbms_cloud_task_views.sql
{ORACLE_HOME}/rdbms/admin/prvt_cloud_body.plb

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	DROP_CREDENTIAL	LIST_FILES
COPY_DATA	ENABLE_CREDENTIAL	LIST_OBJECTS
CREATE_CREDENTIAL	EXPORT_DATA	PUT_OBJECT
CREATE_EXTERNAL_PART_TABLE	EXPORT_ROWS_TABFUNC	RESIGNAL_USER_ERROR
CREATE_EXTERNAL_TABLE	GET_API_RESULT_CACHE_SIZE	SEND_REQUEST
CREATE_HYBRID_PART_TABLE	GET_METADATA	SET_API_RESULT_CACHE_SIZE
DELETE_ALL_OPERATIONS	GET_OBJECT	UPDATE_CREDENTIAL
DELETE_FILE	GET_RESPONSE_HEADERS	VALIDATE_EXTERNAL_PART_TABLE
DELETE_OBJECT	GET_RESPONSE_RAW	VALIDATE_EXTERNAL_TABLE
DELETE_OPERATION	GET_RESPONSE_STATUS_CODE	VALIDATE_HYBRID_PART_TABLE
DISABLE_CREDENTIAL	GET_RESPONSE_TEXT

COPY_COLLECTION

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;

      /

      

      PL/SQL procedure successfully completed.

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

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;

      /

      

      PL/SQL procedure successfully completed.

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;

      /

      

      PL/SQL procedure successfully completed.

CREATE_CREDENTIAL (new 23ai 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;

      /

      

      PL/SQL procedure successfully completed.

      
      

      -- 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 DEFAULT NULL, tenancy_ocid IN VARCHAR2 DEFAULT NULL, private_key IN VARCHAR2, fingerprint IN VARCHAR2 DEFAULT NULL, rpst IN VARCHAR2 DEFAULT NULL);

TBD

Overload 3 dbms_cloud.create_credential( credential_name IN VARCHAR2, params IN CLOB DEFAULT NULL);

TBD

CREATE_EXTERNAL_PART_TABLE

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;

      /

      

      PL/SQL procedure successfully completed.

CREATE_EXTERNAL_TABLE

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;

      /

      

      
      PL/SQL procedure successfully completed.

      

      BEGIN

  dbms_cloud.create_external_table(

   table_name      => 'notams_jobs',

   credential_name => 'OCI$RESOURCE_PRINCIPAL',

   file_uri_list   => 
      'https://objectstorage.us-nasa-1.oraclegovcloud.com/x/x.csv',

   format          => 
      '{"type": "csv", "skipheaders": 1, "timestampformat": "YYYY-MM-DD 
      HH:MI:SS.FF3", "delimiter": ","}',

   column_list => 'rid CHAR(3), sdate DATE, edate DATE, ldate DATE, 
      type VARCHAR(10)');

      END;

      /

      

      PL/SQL procedure successfully completed.

      

      data looks like this for second table

      "rid","sdate","edate","ldate","type"

      "100",2024-05-30 10:45:17.529,2024-05-30 10:45:20.652,,tfr

      "101",2024-05-30 10:47:20.800,2024-05-30 10:47:23.996,,tfr

      .....

CREATE_HYBRID_PART_TABLE

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;

      /

      

      PL/SQL procedure successfully completed.

DELETE_ALL_OPERATIONS

Deletes all Cloud Data Access operations dbms_cloud.delete_all_operations(type IN VARCHAR2 DEFAULT NULL);

exec dbms_cloud.delete_all_operations;

      

      PL/SQL procedure successfully completed.

DELETE_FILE

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

      

      PL/SQL procedure successfully completed.

DELETE_OBJECT

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;

      /

      

      PL/SQL procedure successfully completed.

DELETE_OPERATION

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;

      /

      

      PL/SQL procedure successfully completed.

DISABLE_CREDENTIAL

Disable a Credential object to access Object Store dbms_cloud.disable_credential(credential_name IN VARCHAR2);

exec dbms_cloud.disable_credential('UWOCI_CRED');

      

      PL/SQL procedure successfully completed.

DROP_CREDENTIAL

Drops a Credential object to access any Object Store dbms_cloud.drop_credential(credential_name IN VARCHAR2);

exec dbms_cloud.drop_credential('UWOCI_CRED');

      

      PL/SQL procedure successfully completed.

ENABLE_CREDENTIAL

Enables a Credential object to access Object Store dbms_cloud.enable_credential(credential_name IN VARCHAR2);

exec dbms_cloud.enable_credential('UWOCI_CRED');

      

      PL/SQL procedure successfully completed.

EXPORT_DATA

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;

      /

      

      PL/SQL procedure successfully completed.

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

EXPORT_ROWS_TABFUNC (new 23ai)

Undocumented dbms_cloud.export_rows_tabfunc( refcursor IN SYS_REFCURSOR, context IN CLOB) RETURN dbms_cloud_types.get_objet_ret_tab PIPELINED PARALLEL_ENABLE(PARTITION refcursor BY ANY);

TBD

GET_API_RESULT_CACHE_SIZE (new 23ai)

Returns the maximum cache size dbms_cloud.get_api_result_cache_size RETURN NUMBER;

SELECT dbms_cloud.get_api_result_cache_size;

      

      GET_API_RESULT_CACHE_SIZE

      -------------------------

                             10

GET_METADATA

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;

      /

      

      PL/SQL procedure successfully completed.

GET_OBJECT

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;

      /

      

      PL/SQL procedure successfully completed.

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;

      /

      

      PL/SQL procedure successfully completed.

GET_RESPONSE_HEADERS

Returns Response Headers dbms_cloud.get_response_headers(resp IN dbms_cloud_types.resp) RETURN json_object_t;

TBD

GET_RESPONSE_RAW

Get response body as raw dbms_cloud.get_response_raw(resp IN dbms_cloud_types.resp) RETURN BLOB;

TBD

GET_RESPONSE_STATUS_CODE

Get response status code dbms_cloud.get_response_status_code(resp IN dbms_cloud_types.resp) RETURN PLS_INTEGER;

TBD

GET_RESPONSE_TEXT

Get response body as text dbms_cloud.get_response_text(resp IN dbms_cloud_types.resp) RETURN CLOB;

TBD

LIST_FILES

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

SELECT * FROM TABLE(dbms_cloud.list_files('TEST_DIR');

      

      PL/SQL procedure successfully completed.

LIST_OBJECTS

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

SELECT * FROM TABLE(dbms_cloud.list_files('UW_AWSCRED', 'https://objectstore.com/bucket/bgfile.csv');

      

      PL/SQL procedure successfully completed.

PUT_OBJECT

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;

      /

      

      PL/SQL procedure successfully completed.

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;

      /

      

      PL/SQL procedure successfully completed.

RESIGNAL_USER_ERROR

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;

      

      PL/SQL procedure successfully completed.

SEND_REQUEST (new 23ai overload)

Send an HTTP request

Overload 1 dbms_cloud.send_request( credential_name IN VARCHAR2, uri IN VARCHAR2, method IN VARCHAR2, headers IN CLOB DEFAULT NULL, body IN BLOB DEFAULT NULL, async_request_url IN VARCHAR2 DEFAULT NULL, wait_for_states IN dbms_cloud_types.wait_for_states_t DEFAULT NULL, timeout IN NUMBER DEFAULT 0 cache IN BOOLEAN DEFAULT FALSE, cache_scope IN VARCHAR2, DEFAULT NULL) RETURN dbms_cloud_types.resp;

TBD

Overload 2 dbms_cloud.send_request( credential_name IN VARCHAR2, uri IN VARCHAR2, method IN VARCHAR2, headers IN CLOB DEFAULT NULL, body IN BLOB DEFAULT NULL, async_request_url IN VARCHAR2 DEFAULT NULL, wait_for_states IN dbms_cloud_types.wait_for_states_t DEFAULT NULL, timeout IN NUMBER DEFAULT 0 cache IN BOOLEAN DEFAULT FALSE, cache_scope IN VARCHAR2, DEFAULT NULL);

TBD

SET_API_RESULT_CACHE_SIZE (new 23ai)

Alos uses to configure the maximum number for the cache table dbms_cloud.set_api_result_cache_size(cache_size IN NUMBER);

exec dbms_cloud.set_api_result_cache_size(8);

      

      PL/SQL procedure successfully completed.

UPDATE_CREDENTIAL

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

      

      PL/SQL procedure successfully completed.

VALIDATE_EXTERNAL_PART_TABLE

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

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;

      /

      

      PL/SQL procedure successfully completed.

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;

      /

      

      PL/SQL procedure successfully completed.

VALIDATE_HYBRID_PART_TABLE

Validates Hybrid 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