Go to Qiita Advent Calendar Top
LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@500InternalServerErrorin日本オラクル株式会社

[OCI]リージョンとコンパートメントを指定して実行すると、対象となるObject Storageバケット内の全オブジェクトのURIを返すPL/SQLプロシージャ

Posted at

プロシージャのDDL
※）バケット内のオブジェクト数が1000を超える場合は、ページ処理の追加が必要

CREATE OR REPLACE PROCEDURE LIST_OBJECT_URI_IN_COMPARTMENT(
  region_id        IN VARCHAR2,  -- リージョン識別子 e.g. ap-tokyo-1
  compartment_ocid IN VARCHAR2)  -- コンパートメントのOCID
IS
  response_r       DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE_GET_NAMESPACE_RESPONSE_T;
  namespace        VARCHAR2(4000);
  response_b       DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE_LIST_BUCKETS_RESPONSE_T;
  response_body_b  DBMS_CLOUD_OCI_OBJECT_STORAGE_BUCKET_SUMMARY_TBL;
  response_o       DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE_LIST_OBJECTS_RESPONSE_T;
  response_body_o  DBMS_CLOUD_OCI_OBJECT_STORAGE_LIST_OBJECTS_T;

BEGIN
  -- ネームスペースの取得
  response_r := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.GET_NAMESPACE(
                  credential_name => 'OCI$RESOURCE_PRINCIPAL',
                  region          => region_id);
  namespace := REPLACE(response_r.response_body,'"','');
  -- 指定されたコンパートメント内にあるバケットのリストを取得
  response_b := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.LIST_BUCKETS(
                  credential_name => 'OCI$RESOURCE_PRINCIPAL',
                  namespace_name  => namespace,
                  compartment_id  => compartment_ocid,
                  region          => region_id);
  response_body_b := response_b.response_body;
  -- 取得したバケットのリストを元にしてループ
  FOR i IN response_body_b.first..response_body_b.last LOOP
     -- バケット名の表示
	 DBMS_OUTPUT.PUT_LINE('Bucket : '||response_body_b(i).name);
	 -- バケット内にあるオブジェクトのリストを取得
	 response_o := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.LIST_OBJECTS(
                     credential_name => 'OCI$RESOURCE_PRINCIPAL',
                     namespace_name  => namespace,
                     bucket_name     => response_body_b(i).name,
                     region          => region_id);
     response_body_o := response_o.response_body;
     --オブジェクトリストが空でない場合、オブジェクトにリスト内にあるオブジェクト名を表示
	 IF response_body_o.objects.count != 0 THEN
	   FOR j IN response_body_o.objects.first..response_body_o.objects.last LOOP
         DBMS_OUTPUT.PUT_LINE('https://objectstorage.'||region_id||'.oraclecloud.com/n/'||namespace||'/b/'||response_body_b(i).name||'/o/'||response_body_o.objects(j).name);
	   END LOOP;
	 END IF;
     DBMS_OUTPUT.PUT_LINE('---');
  END LOOP;
END;
/

実行結果の例

SQL> exec LIST_OBJECT_URI_IN_COMPARTMENT('ap-tokyo-1','ocid1.compartment.oc1..axxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxq')
Bucket : BucketForPartition
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/BucketForPartition/o/p2019_1_20220302T231102Z.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/BucketForPartition/o/p2020_1_20220302T234520Z.csv
---
Bucket : Data
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/channels.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/channels.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/fhv_tripdata_20221119_1.parquet
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/fhv_tripdata_20221120_1.parquet
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/fhv_tripdata_20221120_2.parquet
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/fhv_tripdata_20221120_3.parquet
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/fhv_tripdata_20221120_4.parquet
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Data/o/fhv_tripdata_20221121_1.parquet
---
Bucket : Dump
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Dump/o/exp01.dmp
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Dump/o/exp01.dmp_segments/aaaaaa
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Dump/o/newexp.dmp
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/Dump/o/newexp.dmp_segments/aaaaaa
---
Bucket : PipelineBucket
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/PipelineBucket/o/file1.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/PipelineBucket/o/file2.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/PipelineBucket/o/file3.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/PipelineBucket/o/file4.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/PipelineBucket/o/file5.csv
---
Bucket : SQL
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SQL/o/test1.sql
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SQL/o/test2.sql
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SQL/o/test3.sql
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SQL/o/test4.sql
---
Bucket : SendRequestBucket
---
Bucket : SourceBucket
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SourceBucket/o/testdata1.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SourceBucket/o/testdata2.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SourceBucket/o/testdata3.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SourceBucket/o/testdata4.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/SourceBucket/o/testdata5.txt
---
Bucket : TargetBucket
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/TargetBucket/o/testdata1.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/TargetBucket/o/testdata2.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/TargetBucket/o/testdata3.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/TargetBucket/o/testdata4.txt
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/TargetBucket/o/testdata5.txt
---
Bucket : images
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample1.jpg
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample2.jpg
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/images/o/Sample3.jpg
---
Bucket : test
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/csv_exportfile_1_20220419T090019Z.csv.gz
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/my_new_file
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/my_new_file20220419
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/sales/
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/sales/JAPAN/
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/sales/JAPAN/2022/
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/sales/JAPAN/2022/01/
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/sales/JAPAN/2022/01/file1.csv
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/sales/JAPAN/2022/02/
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/storage_usage_20220419
https://objectstorage.ap-tokyo-1.oraclecloud.com/n/n**********w/b/test/o/storage_usage_20220419.txt
---

PL/SQLプロシージャが正常に完了しました。

SQL>
0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?