はじめに

PL/SQL SDKを使用して、Object Storage内にあるSQLスクリプトをAutonomous Database上で実行するPL/SQLプロシージャを作成しました。

ここではObject Storageにアクセスする際のクレデンシャルとして、リソース・プリンシパルを使用しています。

1. 事前準備

日本語データの表示のため、SQL*Plusを実行するターミナルで以下のコマンドを実行し、NLS_LANGを設定します。

export NLS_LANG=JAPANESE_JAPAN.AL32UTF8

SQL*Plusで Autonomous Databaseに接続し、動作確認に使用する表「test」を作成します。

CREATE TABLE test (
	id		NUMBER,
	name	VARCHAR2(100)
);

Object Storageに、以下のような2つのSQLスクリプトファイルをアップロードします。

test1.sql（文字コードはUTF8、改行コードはLF：Linux/Mac）

INSERT INTO test
VALUES (100,'聖徳太子');

COMMIT;

UPDATE test
SET name = '伊藤博文';

COMMIT;

test2.sql（文字コードはUTF8、改行コードはCR+LF：Windows）

INSERT INTO test
VALUES (100,'聖徳太子');

COMMIT;

UPDATE test
SET name = '伊藤博文';

COMMIT;

2. PL/SQLプロシージャの作成

Object Storage上にあるSQLスクリプトの中のSQLを実行するPL/SQLプロシージャ「exec_sqlfile_in_os」を作成します。
以下のCREATE OR REPLACE PROCEDURE文をSQL*Plusから実行します。

CREATE OR REPLACE PROCEDURE exec_sqlfile_in_os (
	namespace 	IN VARCHAR2,
	bucket_name IN VARCHAR2,
	object_name IN VARCHAR2,
	region_id 	IN VARCHAR2
)
IS
	get_object_response 	DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE_GET_OBJECT_RESPONSE_T;
	clob_object				CLOB;
	nl_code					VARCHAR2(10);
	sql_text				VARCHAR2(4000);
BEGIN
-- Object Storageから指定したオブジェクト（ファイル）の内容を取得（BLOB型）
	get_object_response := 	
		DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.GET_OBJECT(
			namespace_name	=> namespace,
			bucket_name		=> bucket_name,
			object_name		=> object_name,
			region 			=> region_id,
			credential_name => 'OCI$RESOURCE_PRINCIPAL'
		);
-- 取得したオブジェクト（ファイル）の内容（BLOB型）をCLOB型に変換
	clob_object := TO_CLOB(get_object_response.response_body);
-- ファイルの改行コードを判別（Windows or Linux/Mac）	
	IF( REGEXP_COUNT(clob_object, CHR(13)||CHR(10)) > 0) 
		THEN
			nl_code := CHR(13)||CHR(10);
		ELSE
			nl_code := CHR(10);
	END IF;
-- ファイルの内容を";"でスプリットし、ファイル内の各SQL文を抽出してループ
	FOR result IN (
        SELECT column_value sql_text FROM apex_string.split(clob_object, ';')
    )
	LOOP
-- 改行コードをスペースに置換（ファイル内で複数行にまたがるSQL文への対応）
		sql_text :=replace(result.sql_text, nl_code, ' ');
-- デバッグのために抽出されたSQL文を表示
		DBMS_OUTPUT.PUT_LINE(sql_text);
		DBMS_OUTPUT.NEW_LINE;
-- 変数sql_testの内容が空でなければ、SQL文を実行	
    	IF ( LENGTH(sql_text) IS NOT NULL)
			THEN EXECUTE IMMEDIATE sql_text;
		END IF;
	END LOOP;
END;
/

3. PL/SQLプロシージャの動作確認

Object Storage上のtest1.sql（改行コードがLF）を指定して、作成したプロシージャを実行してみます。

SQL> set serveroutput on
SQL> EXECUTE exec_sqlfile_in_os ('nrhnlrqdttaw','SQL','test1.sql','ap-tokyo-1');
INSERT INTO test VALUES (100,'聖徳太子')
COMMIT
UPDATE test SET name = '伊藤博文'
COMMIT

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

SQL>

問題なく実行できました。
test表の内容を確認してみます。

SQL> col name for a20
SQL> SELECT * FROM test;

	    ID NAME
---------- --------------------
       100 伊藤博文

SQL>

test1.sql内のSQLが正しく実行されたことが確認できました。

TRUNCATEを実行し、test表を空にします。

SQL> TRUNCATE TABLE test;

表が切り捨てられました。

SQL>

次にObject Storage上のtest2.sql（改行コードがCR+LF）を指定して、作成したプロシージャを実行してみます。

SQL> set serveroutput on
SQL> EXECUTE exec_sqlfile_in_os ('nrhnlrqdttaw','SQL','test2.sql','ap-tokyo-1');
INSERT INTO test VALUES (100,'聖徳太子')
COMMIT
UPDATE test SET name = '伊藤博文'
COMMIT

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

SQL>

問題なくプロシージャが実行できました。
test表の内容を確認してみます。

SQL> col name for a20
SQL> SELECT * FROM test;

	    ID NAME
---------- --------------------
       100 伊藤博文

SQL>

test2.sql内のSQLが正しく実行されたことが確認できました。

まとめ

PL/SQL SDKを使用して、Object Storage内にあるSQLスクリプトをAutonomous Database上で実行するPL/SQLプロシージャを作成できました。
コメントとデバッグ用のコードを削除した最終版は以下のようになります。

CREATE OR REPLACE PROCEDURE exec_sqlfile_in_os (
	namespace 	IN VARCHAR2,
	bucket_name IN VARCHAR2,
	object_name IN VARCHAR2,
	region_id 	IN VARCHAR2
)
IS
	get_object_response 	DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE_GET_OBJECT_RESPONSE_T;
	clob_object				CLOB;
	nl_code					VARCHAR2(10);
	sql_text				VARCHAR2(4000);
BEGIN
	get_object_response :=
 		DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.GET_OBJECT(
			namespace_name	=> namespace,
			bucket_name		=> bucket_name,
			object_name		=> object_name,
			region 			=> region_id,
			credential_name => 'OCI$RESOURCE_PRINCIPAL'
		);

	clob_object := TO_CLOB(get_object_response.response_body);

	IF( REGEXP_COUNT(clob_object, CHR(13)||CHR(10)) > 0)
 		THEN
			nl_code := CHR(13)||CHR(10);
		ELSE
			nl_code := CHR(10);
	END IF;

	FOR result IN (
        SELECT column_value sql_text FROM apex_string.split(clob_object, ';')
    )
	LOOP
		sql_text :=replace(result.sql_text, nl_code, ' ');
    	IF ( LENGTH(sql_text) IS NOT NULL)
			THEN EXECUTE IMMEDIATE sql_text;
		END IF;
	END LOOP;
END;
/