はじめに
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スクリプトファイルをアップロードします。
INSERT INTO test
VALUES (100,'聖徳太子');
COMMIT;
UPDATE test
SET name = '伊藤博文';
COMMIT;
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;
/