DBMS_CLOUDサブプログラムおよびREST API

DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name   IN VARCHAR2,
      username          IN VARCHAR2,
      password          IN VARCHAR2 DEFAULT NULL,
      comments          IN VARCHAR2 DEFAULT NULL);


DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name   IN VARCHAR2,
      user_ocid         IN VARCHAR2,
      tenancy_ocid      IN VARCHAR2,
      private_key       IN VARCHAR2,
      fingerprint       IN VARCHAR2,
      comments          IN VARCHAR2 DEFAULT NULL);


DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name  IN VARCHAR2,
      params           IN CLOB DEFAULT);

 BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com',
    password => 'password' );
 END;
/

oracleidentitycloudservice/adb_user@example.com

adb_user@example.com

 BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
     credential_name => 'OCI_KEY_CRED',
     user_ocid       => 'ocid
      1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa',
     tenancy_ocid    => 'ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a',
     private_key     => 'MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=',
     fingerprint     => 'f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27');
  END;
  /

 BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
      credential_name      => 'MY_CRED',
      params               => JSON_OBJECT(
             'aws_role_arn'      value 'arn:aws:iam::123456:role/AWS_ROLE_ARN',
             'external_id_type'  value 'database_ocid'));
 END;
 /

  BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'MY_GITHUB_CRED',
     username => 'user@example.com',
     password => 'your_personal_access_token' );
  END;
  /

  BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'GOOGLE_BIGQUERY_CRED',
    params => JSON_OBJECT('gcp_oauth2' value
                                JSON_OBJECT(
                                     'client_id'     value 'client_id',
                                     'client_secret' value 'client_secret',
                                     'refresh_token' value 'refresh_token' )));
   END;
   /

BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_SECRET_CRED',
    params => JSON_OBJECT(
                'username'   value 'scott',
                'region'     value 'us-ashburn-1',
                'secret_id'  value 'ocid1.vaultsecret.co1.ap-mumbai-1.example..aaaaaaaauq5ok5nq3bf2vwetkpqsoa'));
END;
/

   DBMS_CLOUD.DROP_CREDENTIAL (
     credential_name     IN VARCHAR2);

 DBMS_CLOUD.REFRESH_VAULT_CREDENTIAL (
   credential_name   IN VARCHAR2);

 DBMS_CLOUD.UPDATE_CREDENTIAL (
    credential_name   IN VARCHAR2,
    attribute         IN VARCHAR2,
    value             IN VARCHAR2);

 BEGIN
    DBMS_CLOUD.UPDATE_CREDENTIAL(
      credential_name => 'OBJ_STORE_CRED',
      attribute => 'PASSWORD',
      value => 'password');
 END;
 /

  BEGIN
    DBMS_CLOUD.UPDATE_CREDENTIAL(
      credential_name => 'ARN_CRED',
      attribute => 'aws_role_arn',
      value => 'NEW_AWS_ARN');
  END;
  /

  DBMS_CLOUD.COPY_COLLECTION (
     collection_name   IN VARCHAR2,
     credential_name   IN VARCHAR2 DEFAULT NULL,
     file_uri_list     IN CLOB,
     format            IN CLOB     DEFAULT NULL
  );

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

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
            credential_name => 'OBJ_STORE_CRED',
            username        => 'user_name@oracle.com',
            password        => 'password'
            );

    DBMS_CLOUD.COPY_COLLECTION(
            collection_name => 'myCollection',
            credential_name => 'OBJ_STORE_CRED',
            file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adbexample/b/json/o/myCollection.json'
            );
END;
/

DBMS_CLOUD.COPY_DATA (
    table_name        IN VARCHAR2,
    credential_name   IN VARCHAR2 DEFAULT NULL,
    file_uri_list     IN CLOB,
    schema_name       IN VARCHAR2,
    field_list        IN CLOB,
    format            IN CLOB);

DBMS_CLOUD.COPY_DATA (
    table_name        IN VARCHAR2,
    credential_name   IN VARCHAR2 DEFAULT NULL,
    file_uri_list     IN CLOB DEFAULT NULL,
    schema_name       IN VARCHAR2 DEFAULT NULL,
    field_list        IN CLOB DEFAULT NULL,
    format            IN CLOB DEFAULT NULL
    operation_id      OUT NOCOPY NUMBER);

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
            credential_name => 'DEF_CRED_NAME',
            username        => 'user_name@oracle.com',
            password        => 'password'
            );
END;
/

BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'CHANNELS',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/channels.txt',
    format => json_object('delimiter' value ',')
 );
END;
/

BEGIN
    DBMS_CLOUD.COPY_DATA(
            table_name      => 'ORDERS',
            schema_name     => 'TEST_SCHEMA',
            credential_name => 'DEF_CRED_NAME',
	     file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adbexample/b/json/o/orde[r]s.tbl.1'
            format          =>  json_object('ignoreblanklines' value TRUE,
                                            'rejectlimit' value '0',
                                            'dateformat' value 'yyyy-mm-dd',
                                            'regexuri' value TRUE)
            );
END;
/

DBMS_CLOUD.COPY_DATA (
    table_name        IN VARCHAR2,
    credential_name   IN VARCHAR2 DEFAULT NULL,
    file_uri_list     IN CLOB,
    schema_name       IN VARCHAR2 DEFAULT,
    field_list        IN CLOB DEFAULT,
    format            IN CLOB DEFAULT);

DBMS_CLOUD.COPY_OBJECT (
    source_credential_name  IN  VARCHAR2 DEFAULT NULL,
    source_object_uri       IN  VARCHAR2,
    target_object_uri       IN  VARCHAR2,
    target_credential_name  IN  VARCHAR2 DEFAULT NULL
);

BEGIN
DBMS_CLOUD.COPY_OBJECT (
    source_credential_name => 'OCI_CRED',
    source_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/bgfile.csv',
    target_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/myfile.csv'
);
END;
/

DBMS_CLOUD.CREATE_CLOUD_TABLE (
    table_name       IN VARCHAR2,
    column_list      IN CLOB,
    params           IN CLOB);

EXEC DBMS_CLOUD.CREATE_CLOUD_TABLE( 'CLOUD_TAB1', 'I INTEGER, J INTEGER' );

BEGIN
  DBMS_CLOUD.CREATE_CLOUD_TABLE(
  table_name  => 'CLOUD_TABLE_WITH_DEFAULT',
  column_list => 'I INTEGER,
                  A VARCHAR2(32) DEFAULT ''ABC''' );
END;
/

DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
    table_name           IN VARCHAR2,
    credential_name      IN VARCHAR2,
    partitioning_clause  IN CLOB,
    column_list          IN CLOB,
    field_list           IN CLOB DEFAULT,
    format               IN CLOB DEFAULT);


DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
    table_name           IN VARCHAR2,
    credential_name      IN VARCHAR2,
    file_uri_list        IN VARCHAR2,
    column_list          IN CLOB,
    field_list           IN CLOB DEFAULT,
    format               IN CLOB DEFAULT);

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
      table_name =>'PET1',
      credential_name =>'OBJ_STORE_CRED',
      format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'characterset' value 'us7ascii'),
      column_list => 'col1 number, col2 number, col3 number',
      partitioning_clause => 'partition by range (col1)
                                (partition p1 values less than (1000) location
                                    ( ''&base_URL//file_11.txt'')
                                ,
                                partition p2 values less than (2000) location
                                    ( ''&base_URL/file_21.txt'')
                                ,
                                partition p3 values less than (3000) location
                                    ( ''&base_URL/file_31.txt'')
                                )'
    );
  END;
/


BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
      table_name          => 'PET',
      format              => json_object('delimiter'value ','),
      column_list         => 'name varchar2(20), gender varchar2(10), salary number',
      partitioning_clause => 'partition by range (salary)
              (   -- Use test1.csv in the DEFAULT DIRECTORY DATA_PUMP_DIR
                  partition p1 values less than (100) LOCATION (''test1.csv''),
                  -- Use test2.csv in a specified directory MY_DIR
                  partition p2 values less than (300) DEFAULT DIRECTORY MY_DIR LOCATION (''test2.csv'')        )'   );
END;
/

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
  table_name => 'MYSALES',
  credential_name => 'DEF_CRED_NAME',
  file_uri_list     => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/*.csv',
  column_list       => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)',
  field_list        => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
  format            => '{"type":"csv", "partition_columns":["country","year","month"]}');
END;
/

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
  table_name => 'MYSALES',
  credential_name => 'DEF_CRED_NAME',
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
    table_name      => 'MYSALES',
    credential_name => 'DEF_CRED_NAME',
    file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/*.parquet',
    format          =>
        json_object('type' value 'parquet', 'schema' value 'first',
                    'partition_columns' value
                          json_array(
                                json_object('name' value 'country', 'type' value 'varchar2(100)'),
                                json_object('name' value 'year', 'type' value 'number'),
                                json_object('name' value 'month', 'type' value 'varchar2(2)')
                          )
        )
    );
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,
    field_list       IN CLOB DEFAULT,
    format           IN CLOB DEFAULT);

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
      table_name =>'WEATHER_REPORT_DOUBLE_DATE',
      credential_name =>'OBJ_STORE_CRED',
      file_uri_list =>'&base_URL/Charlotte_NC_Weather_History_Double_Dates.csv',
      format => json_object('type' value 'csv', 'skipheaders' value '1'),
      field_list => 'REPORT_DATE DATE''mm/dd/yy'',
                    REPORT_DATE_COPY DATE ''yyyy-mm-dd'',
                    ACTUAL_MEAN_TEMP,
                    ACTUAL_MIN_TEMP,
                    ACTUAL_MAX_TEMP,
                    AVERAGE_MIN_TEMP,
                    AVERAGE_MAX_TEMP,
                    AVERAGE_PRECIPITATION',
      column_list => 'REPORT_DATE DATE,
                    REPORT_DATE_COPY DATE,
                    ACTUAL_MEAN_TEMP NUMBER,
                    ACTUAL_MIN_TEMP NUMBER,
                    ACTUAL_MAX_TEMP NUMBER,
                    AVERAGE_MIN_TEMP NUMBER,
                    AVERAGE_MAX_TEMP NUMBER,
                    AVERAGE_PRECIPITATION NUMBER');
  END;
/

SELECT * FROM WEATHER_REPORT_DOUBLE_DATE where
  actual_mean_temp > 69 and actual_mean_temp < 74

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

format => json_object('access_protocol' value
      json_object('protocol_type' value 'iceberg',
                  'protocol_config' value
                    json_object('iceberg_catalog_type' value 'aws_glue',
                                'iceberg_glue_region'  value 'glue region',
                                'iceberg_table_path'   value 'database_name.table_name')));

format => json_object('access_protocol' value
      json_object('protocol_type' value 'iceberg')

format => json_object('access_protocol' value
      json_object('protocol_type'   value 'iceberg',
                  'protocol_config' value
                  json_object('iceberg_catalog_type'  value 'hadoop',
                              'iceberg_warehouse'     value '<OCI folder URI>',
                              'iceberg_table_path'    value 'database_name.table_name')));

format => json_object('access_protocol' value
      json_object('protocol_type' value 'iceberg')

{
  "access_protocol": {
    "protocol_type": "iceberg",
    "protocol_config": {
      "iceberg_catalog_type": "unity",
      "rest_catalog_endpoint": "https://adb-...azuredatabricks.net/api/
2.1/unity-catalog/iceberg",
      "rest_authentication": {
        "rest_auth_cred": "PDB_ADMIN_UNITY_PRINC_CRED",
        "rest_auth_endpoint": "https://adb-...azuredatabricks.net/oidc/v1/token"
      },
      "table_path": [
        "spapadom-catalog",
        "default",
        "people_10m"
      ]
    }
  }
}

begin    dbms_cloud.create_external_table (
        table_name       => 'people_10m',
        credential_name  => 'AZURE_CRED',
        file_uri_list    => '',
        format           =>'{
  "access_protocol": {
    "protocol_type": "iceberg",
    "protocol_config": {
      "iceberg_catalog_type": "unity",
      "rest_catalog_endpoint": "https://adb-...azuredatabricks.net/api/2.1/unity-catalog/iceberg",
      "rest_authentication":{
        "rest_auth_cred":"UNITY_CRED"
      },
      "table_path":["spapadom-catalog", "default","people_10m"]
    }
  }
}'    );
end;

BEGIN
    dbms_cloud.create_external_table (
        table_name       => 'dbu_table_complex_type',
        credential_name  => 'AWS_CRED',
        file_uri_list    => '',
        format           =>'{
  "access_protocol": {
    "protocol_type": "iceberg",
    "protocol_config": {
      "iceberg_catalog_type": "polaris",
      "rest_catalog_prefix":"spapadom-catalog",
      "rest_catalog_endpoint": "https://...snowflakecomputing.com/polaris/api/catalog",
      "rest_authentication":{
        "rest_auth_cred":"PDB_ADMIN_OPEN_CRED"
      },
      "table_path":["SPAPADOM_ICEBERG", "PUBLIC","DBU_TABLE_COMPLEX_TYPE"]
    }
  }
}'
    );
END;
/

BEGIN
    dbms_cloud.create_external_table (
        table_name       => 'dbu_table_complex_type',
        credential_name  => 'AWS_CRED',
        file_uri_list    => '',
        format           =>'{
  "access_protocol": {
    "protocol_type": "iceberg",
    "protocol_config": {
      "iceberg_catalog_type": "polaris",
      "rest_catalog_prefix":"spapadom-catalog",
      "rest_catalog_endpoint": "https://...snowflakecomputing.com/polaris/api/catalog",
      "rest_authentication":{
        "rest_auth_cred":"PDB_ADMIN_OPEN_CRED"
      },
      "table_path":["SPAPADOM_ICEBERG", "PUBLIC","DBU_TABLE_COMPLEX_TYPE"]
    }
  }
}'
    );
END;
/

BEGIN
    dbms_cloud.create_external_table (
        table_name       => 'dbu_table_complex_type',
        credential_name  => 'AWS_CRED',
        file_uri_list    => '',
        format           =>'{
  "access_protocol": {
    "protocol_type": "iceberg",
    "protocol_config": {
    "iceberg_catalog_type": "polaris",
      "rest_catalog_prefix":"spapadom-catalog",
      "rest_catalog_endpoint": "https://...snowflakecomputing.com/polaris/api/catalog",
      "rest_authentication":{
        "rest_auth_cred":"PDB_ADMIN_OPEN_PRINC_CRED",
        "rest_auth_endpoint":"https://...snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens"
      },
      "table_path":["SPAPADOM_ICEBERG", "PUBLIC", "DBU_TABLE_COMPLEX_TYPE"]
    }
  }
}'
    );
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,
    field_list       IN CLOB DEFAULT,
    format           IN CLOB DEFAULT);

format => '{"type":"orc", "schema": "all"}'

format => json_object('type' value 'orc', 'schema' value 'first')

format => '{"type":"avro", "schema": "all"}'

format => json_object('type' value 'avro', 'schema' value 'first')

format => '{"type":"parquet", "schema": "all"}'

format => json_object('type' value 'parquet', 'schema' value 'first')

DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
      credential_name  IN  VARCHAR2 DEFAULT NULL,
      location_uri     IN  VARCHAR2,
      index_name       IN  VARCHAR2,
      format           IN  CLOB     DEFAULT NULL
);

BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
        credential_name => 'DEFAULT_CREDENTIAL',
        location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/ts_data/'
        index_name      => 'EMP',
        format          => JSON_OBJECT ('refresh_rate' value 10)
);
END;
/

DBMS_CLOUD.CREATE_HYBRID_PART_TABLE (
    table_name           IN VARCHAR2,
    credential_name      IN VARCHAR2,
    partitioning_clause  IN CLOB,
    column_list          IN CLOB,
    field_list           IN CLOB DEFAULT,
    format               IN CLOB DEFAULT);

BEGIN
  DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
      table_name =>'HPT1',
      credential_name =>'OBJ_STORE_CRED',
      format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'characterset' value 'us7ascii'),
      column_list => 'col1 number, col2 number, col3 number',
      partitioning_clause => 'partition by range (col1)
                                (partition p1 values less than (1000) external location
                                    ( ''&base_URL/file_11.txt'')
                                ,
                                partition p2 values less than (2000) external location
                                    ( ''&base_URL/file_21.txt'')
                                ,
                                partition p3 values less than (3000)
                                )'
    );
  END;
/


BEGIN
  DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
    table_name  => 'HPT1',
    format      => json_object('delimiter'value ',', 'recorddelimiter'value 'newline'),
    column_list => 'NAME VARCHAR2(30), GENDER VARCHAR2(10), BALANCE number',
    partitioning_clause => 'partition by range (B  2  ALANCE)
              (partition p1 values less than (1000) external DEFAULT DIRECTORY DATA_PUMP_DIR LOCATION (''Scott_male_1000.csv''),
                partition p2 values less than (2000) external DEFAULT DIRECTORY DATA_PUMP_DIR LOCATION (''Mary_female_3000.csv''),
                partition p3 values less than (3000))' );
END;
/

DBMS_CLOUD.DELETE_ALL_OPERATIONS (
  type      IN VARCHAR DEFAULT NULL);

DBMS_CLOUD.DELETE_FILE (
      directory_name     IN VARCHAR2,
      file_name          IN VARCHAR2,
      force              IN BOOLEAN DEFAULT FALSE);

   BEGIN
   DBMS_CLOUD.DELETE_FILE(
   directory_name =>  'DATA_PUMP_DIR',
   file_name => 'exp1.dmp' );
END;
/

DBMS_CLOUD.DELETE_OBJECT (
      credential_name      IN VARCHAR2,
      object_uri           IN VARCHAR2,
      force                IN BOOLEAN DEFAULT FALSE);

BEGIN
  DBMS_CLOUD.DELETE_OBJECT(
      credential_name => 'DEF_CRED_NAME',
      object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp1.dmp' );
  END;
/

  DBMS_CLOUD.DELETE_OPERATION (
        id      IN NUMBER);

SELECT id FROM user_load_operations WHERE type LIKE '%BAD%';
EXEC DBMS_CLOUD.DELETE_OPERATION(id);

DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
      index_name       IN  VARCHAR2,
);

BEGIN
DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
        index_name => 'EMP',
);
END;
/

DBMS_CLOUD.EXPORT_DATA (
      credential_name   IN VARCHAR2 DEFAULT NULL,
      file_uri_list     IN CLOB,
      format            IN CLOB,
      query             IN CLOB);

DBMS_CLOUD.EXPORT_DATA (
      credential_name   IN VARCHAR2 DEFAULT NULL,
      file_uri_list     IN CLOB DEFAULT NULL,
      format            IN CLOB DEFAULT NULL,
      query             IN CLOB DEFAULT NULL,
      operation_id      OUT NOCOPY NUMBER);

  BEGIN
      DBMS_CLOUD.EXPORT_DATA(
          credential_name =>'OBJ_STORE_CRED',
          file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp1.dmp',
          format => json_object('type' value 'datapump', 'compression' value 'basic', 'version' value 'latest'),
          query => 'SELECT lakehouse_id, quantity FROM inventories'
      );
  END;
  /

  BEGIN
    DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp1.json',
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'json', 'compression' value 'gzip'));
    );
  END;
/

 BEGIN
   DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp1.xml',
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'xml', 'compression' value 'gzip'));
    );
  END;
/

 BEGIN
   DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp.csv',
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'csv', 'delimiter' value '|', 'compression' value 'gzip', 'header' value true, 'encryption' value ('user_defined_function' value 'ADMIN.decryption_callback')));
    );
  END;
/

  BEGIN
    DBMS_CLOUD.EXPORT_DATA(
      file_uri_list => 'export_dir:sales.dmp',
      format        => json_object('type' value 'datapump'),
      query         => 'SELECT * FROM sales'
    );
  END;
/

 DBMS_CLOUD.GET_OBJECT (
      credential_name      IN VARCHAR2,
      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);


 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;

 BEGIN
   DBMS_CLOUD.GET_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file.txt',
     directory_name => 'DATA_PUMP_DIR');
 END;
 /

  SELECT to_clob(
     DBMS_CLOUD.GET_OBJECT(
       credential_name => 'OBJ_STORE_CRED',
       object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file.txt'))
  FROM DUAL;

   DECLARE
     l_blob BLOB := NULL;
   BEGIN
     l_blob := DBMS_CLOUD.GET_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/MyImage.gif' );
   END;
  /

   DBMS_CLOUD.LIST_FILES (
      directory_name      IN VARCHAR2)
       RETURN TABLE;

   SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

   OBJECT_NAME       BYTES   CHECKSUM      CREATED              LAST_MODIFIED
   ------------ ---------- ----------    ---------------------  ---------------------
   cwallet.sso        2965               2018-12-12T18:10:47Z   2019-11-23T06:36:54Z

  SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR:f*.csv');

  SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR:f1.csv');

 DBMS_CLOUD.LIST_OBJECTS (
      credential_name      IN VARCHAR2,
      location_uri         IN VARCHAR2)
  RETURN TABLE;

  SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OBJ_STORE_CRED',
    'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/');


   OBJECT_NAME   BYTES              CHECKSUM                       CREATED         LAST_MODIFIED
   ------------ ---------- -------------------------------- --------------------- --------------------
   cwallet.sso   2965      2339a2731ba24a837b26d344d643dc07 2019-11-23T06:36:54Z

 DBMS_CLOUD.MOVE_OBJECT (
    source_credential_name  IN  VARCHAR2 DEFAULT NULL,
    source_object_uri       IN  VARCHAR2,
    target_object_uri       IN  VARCHAR2,
    target_credential_name  IN  VARCHAR2 DEFAULT NULL
);

 BEGIN
 DBMS_CLOUD.MOVE_OBJECT (
    source_credential_name => 'OCI_CRED',
    source_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/bgfile.csv',
    target_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/myfile.csv'
 );
 END;
 /

 DBMS_CLOUD.PUT_OBJECT (
      credential_name      IN VARCHAR2,
      object_uri           IN VARCHAR2,
      directory_name       IN VARCHAR2,
      file_name            IN VARCHAR2
      compression          IN VARCHAR2 DEFAULT  NULL);


 DBMS_CLOUD.PUT_OBJECT (
      credential_name      IN VARCHAR2,
      object_uri           IN VARCHAR2,
      contents             IN BLOB
      compression          IN VARCHAR2 DEFAULT  NULL);

 DECLARE
      my_blob_data BLOB;
 BEGIN
/* Some processing producing BLOB data and populating my_blob_data */
 DBMS_CLOUD.PUT_OBJECT(
    credential_name => 'OBJ_STORE_CRED',
    object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/my_new_file',
    contents => my_blob_data));
 END;
 /

 DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE (
  table_name        IN VARCHAR2,
  schema_name       IN VARCHAR2 DEFAULT,
  update_columns    IN BOOLEAN DEFAULT);

 DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE (
      table_name                 IN VARCHAR2,
      partition_name             IN CLOB DEFAULT,
      subpartition_name          IN CLOB DEFAULT,
      schema_name                IN VARCHAR2 DEFAULT,
      rowcount                   IN NUMBER DEFAULT,
      partition_key_validation   IN BOOLEAN DEFAULT,
      stop_on_error              IN BOOLEAN DEFAULT);


 DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE (
      table_name                 IN VARCHAR2,
      operation_id               OUT NUMBER,
      partition_name             IN CLOB DEFAULT,
      subpartition_name          IN CLOB DEFAULT,
      schema_name                IN VARCHAR2 DEFAULT,
      rowcount                   IN NUMBER DEFAULT,
      partition_key_validation   IN BOOLEAN DEFAULT,
      stop_on_error              IN BOOLEAN DEFAULT);

 DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
  table_name      IN VARCHAR2,
  schema_name     IN VARCHAR2 DEFAULT,
  rowcount        IN NUMBER DEFAULT,
  stop_on_error   IN BOOLEAN DEFAULT);


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

 DBMS_CLOUD.VALIDATE_HYBRID_PART_TABLE (
      table_name                 IN VARCHAR2,
      partition_name             IN CLOB DEFAULT,
      subpartition_name          IN CLOB DEFAULT,
      schema_name                IN VARCHAR2 DEFAULT,
      rowcount                   IN NUMBER DEFAULT,
      partition_key_validation   IN BOOLEAN DEFAULT,
      stop_on_error              IN BOOLEAN DEFAULT);


 DBMS_CLOUD.VALIDATE_HYBRID_PART_TABLE (
      table_name                 IN VARCHAR2,
      operation_id               OUT NUMBER,
      partition_name             IN CLOB DEFAULT,
      subpartition_name          IN CLOB DEFAULT,
      schema_name                IN VARCHAR2 DEFAULT,
      rowcount                   IN NUMBER DEFAULT,
      partition_key_validation   IN BOOLEAN DEFAULT,
      stop_on_error              IN BOOLEAN DEFAULT);

 DBMS_CLOUD.BULK_COPY (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL
);

 DBMS_CLOUD.BULK_COPY (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL,
      operation_id            OUT NUMBER
);

 BEGIN
 DBMS_CLOUD.BULK_COPY (
    source_credential_name => 'OCI_CRED',
    source_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/o',
    target_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/o',
    format       => JSON_OBJECT ('logretention' value 7, 'logprefix' value 'BULKOP')
 );
 END;
 /

 DBMS_CLOUD.BULK_DELETE(
    credential_name  IN  VARCHAR2 DEFAULT NULL,
    location_uri     IN  VARCHAR2,
    regex_filter     IN  VARCHAR2 DEFAULT NULL,
    format           IN  CLOB     DEFAULT NULL
 );

 DBMS_CLOUD.BULK_DELETE (
    credential_name  IN  VARCHAR2 DEFAULT NULL,
    location_uri     IN  VARCHAR2,
    regex_filter     IN  VARCHAR2 DEFAULT NULL,
    format           IN  CLOB     DEFAULT NULL,
    operation_id     OUT NUMBER
 );

BEGIN
DBMS_CLOUD.BULK_DELETE (
     credential_name => 'OCI_CRED',
     location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
     format          => JSON_OBJECT ('logretention' value 5, 'logprefix' value 'BULKDEL')
);
END;
/

 DBMS_CLOUD.BULK_DOWNLOAD (
    credential_name  IN  VARCHAR2 DEFAULT NULL,
    location_uri     IN  VARCHAR2,
    directory_name   IN  VARCHAR2,
    regex_filter     IN  VARCHAR2 DEFAULT NULL,
    format           IN  CLOB     DEFAULT NULL
 );

 DBMS_CLOUD.BULK_DOWNLOAD (
    credential_name  IN  VARCHAR2 DEFAULT NULL,
    location_uri     IN  VARCHAR2,
    directory_name   IN  VARCHAR2,
    regex_filter     IN  VARCHAR2 DEFAULT NULL,
    format           IN  CLOB     DEFAULT NULL,
    operation_id     OUT NUMBER
 );

 BEGIN
 DBMS_CLOUD.BULK_DOWNLOAD (
    credential_name => 'OCI_CRED',
    location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
    directory_name  => 'BULK_TEST',
    format          => JSON_OBJECT ('logretention' value 7, 'logprefix' value 'BULKOP')
 );
 END;
 /

 DBMS_CLOUD.BULK_MOVE (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL
 );

 DBMS_CLOUD.BULK_MOVE (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL,
      operation_id            OUT NUMBER
 );

BEGIN
DBMS_CLOUD.BULK_MOVE (
    source_credential_name => 'OCI_CRED',
    source_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/o',
    target_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/o',
    format                 => JSON_OBJECT ('logretention' value 7, 'logprefix' value 'BULKMOVE')
);
END;
/

DBMS_CLOUD.BULK_UPLOAD (
    credential_name  IN  VARCHAR2 DEFAULT NULL,
    location_uri     IN  VARCHAR2,
    directory_name   IN  VARCHAR2,
    regex_filter     IN  VARCHAR2 DEFAULT NULL,
    format           IN  CLOB     DEFAULT NULL
);

DBMS_CLOUD.BULK_UPLOAD (
    credential_name  IN  VARCHAR2 DEFAULT NULL,
    location_uri     IN  VARCHAR2,
    directory_name   IN  VARCHAR2,
    regex_filter     IN  VARCHAR2 DEFAULT NULL,
    format           IN  CLOB     DEFAULT NULL,
    operation_id     OUT NUMBER
);

BEGIN
DBMS_CLOUD.BULK_UPLOAD (
    credential_name => 'OCI_CRED',
    location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
    directory_name  => 'BULK_TEST',
    format          => JSON_OBJECT ('logretention' value 5, 'logprefix' value 'BULKUPLOAD')
);
END;
/

SELECT timestamp FROM SESSION_CLOUD_API_RESULTS;

DBMS_CLOUD.GET_RESPONSE_HEADERS(
      resp          IN DBMS_CLOUD_TYPES.resp)
  RETURN JSON_OBJECT_T;

DBMS_CLOUD.GET_RESPONSE_RAW(
      resp          IN DBMS_CLOUD_TYPES.resp)
  RETURN BLOB;

DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(
      resp          IN DBMS_CLOUD_TYPES.resp)
  RETURN PLS_INTEGER;

DBMS_CLOUD.GET_RESPONSE_TEXT(
      resp          IN DBMS_CLOUD_TYPES.resp)
  RETURN CLOB;

DBMS_CLOUD.GET_API_RESULT_CACHE_SIZE()
  RETURN NUMBER;

DBMS_CLOUD.SEND_REQUEST(
      credential_name    IN VARCHAR2,
      uri                IN VARCHAR2,
      method             IN VARCHAR2,
      headers            IN CLOB 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 PL/SQL BOOLEAN DEFAULT FALSE,
      cache_scope        IN VARCHAR2 DEFAULT 'PRIVATE',
      body               IN BLOB DEFAULT NULL)
  RETURN DBMS_CLOUD_TYPES.resp;

DBMS_CLOUD.SEND_REQUEST(
      credential_name    IN VARCHAR2,
      uri                IN VARCHAR2,
      method             IN VARCHAR2,
      headers            IN CLOB 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 PL/SQL BOOLEAN DEFAULT FALSE,
      cache_scope        IN VARCHAR2 DEFAULT 'PRIVATE',
      body               IN BLOB DEFAULT NULL);

DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE(
      cache_size          IN NUMBER);

EXEC DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE(101);

 SET SERVEROUTPUT ON
 DECLARE
  resp DBMS_CLOUD_TYPES.resp;
 BEGIN
   -- Send request
   resp := DBMS_CLOUD.send_request(
           credential_name => 'OCI_KEY_CRED',
           uri => 'https://objectstorage.region.oraclecloud.com/n/namespace-string/b/',
           method => DBMS_CLOUD.METHOD_POST,
           body => UTL_RAW.cast_to_raw(
                       JSON_OBJECT('name' value 'bucketname',
                                   'compartmentId' value 'compartment_OCID'))
         );

   -- Response Body in TEXT format
   dbms_output.put_line('Body: ' || '------------' || CHR(10) ||
   DBMS_CLOUD.get_response_text(resp) || CHR(10));

   -- Response Headers in JSON format
   dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
   DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));

   -- Response Status Code
   dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
   DBMS_CLOUD.get_response_status_code(resp));

  END;
 /

SET SERVEROUTPUT ON
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
BEGIN
  -- Send request
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname',
            method => DBMS_CLOUD.METHOD_DELETE
          );

  -- Response Body in TEXT format
  dbms_output.put_line('Body: ' || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_text(resp) || CHR(10));

  -- Response Headers in JSON format
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));

  -- Response Status Code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_status_code(resp));

END;
/

--
-- List compartments
--
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
  root_compartment_ocid VARCHAR2(512) := '&1';
BEGIN
  -- Send request
  dbms_output.put_line('Send Request');
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://identity.region.oraclecloud.com/20160918/compartments?compartmentId=' || root_compartment_ocid,
            method => DBMS_CLOUD.METHOD_GET,
            headers => JSON_OBJECT('opc-request-id' value 'list-compartments')
          );
  dbms_output.put_line('Body: ' || '------------' || CHR(10) || DBMS_CLOUD.get_response_text(resp) || CHR(10));
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_status_code(resp));
  dbms_output.put_line(CHR(10));
END;
/

--
-- Sent Work Request Autonomous AI Database Stop Request with Wait for Status
DECLARE
    l_resp DBMS_CLOUD_TYPES.resp;
    l_resp_json JSON_OBJECT_T;
    l_key_shape JSON_OBJECT_T;
    l_body JSON_OBJECT_T;
    status_array DBMS_CLOUD_TYPES.wait_for_states_t;
BEGIN
  status_array := DBMS_CLOUD_TYPES.wait_for_states_t('SUCCEEDED');
  l_body := JSON_OBJECT_T('{}');
  l_body.put('autonomousDatabaseId', 'ocid');
-- Send request
  dbms_output.put_line(l_body.to_clob);
  dbms_output.put_line('Send Request');
  l_resp := DBMS_CLOUD.send_request(
                      credential_name    => 'NATIVE_CRED_OCI',
                      uri                => 'https://database.region.oraclecloud.com/20160918/autonomousDatabases/ocid/actions/stop',
                      method             => DBMS_CLOUD.METHOD_POST,
                      body               => UTL_RAW.cast_to_raw(l_body.to_clob),
                      async_request_url  => 'https://iaas.region.oraclecloud.com/20160918/workRequests',
                      wait_for_states    => status_array,
                      timeout            => 600
                  );
  dbms_output.put_line('resp body: '||DBMS_CLOUD.get_response_text(l_resp));
  dbms_output.put_line('resp headers: '||DBMS_CLOUD.get_response_headers(l_resp).to_clob);
END;
/