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 is now availble. If you haven't you downloaded already: Why?

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