内容
oracle xeでキュー機能(AQ)を使ってみました。
やりたかったことはセッションAで実行した処理結果を自動でセッションBに返す
ということがしたくてchatGPTに質問したらAQを使えばできるとのことでした。
結局やりたかったことはできなかったのですが、データが登録されたら検知してデータを取得する
というのはできたので記載しておきます。
AQ使ってみた
親DB(CDB: systemユーザで接続できるDB)に接続してからメインで使用しているDBに対して権限を付与します。
alter session set container = サービス名;
grant execute on dbms_aq to ユーザ名; -- PDB: メインで使ってるDBのユーザ名
ペイロードのタイプを定義します。
CREATE OR REPLACE TYPE my_payload_type AS OBJECT (
column1 NUMBER,
column2 VARCHAR2(100)
);
キュー関連を作成します。
BEGIN
-- キューテーブルの作成
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'my_queue_table',
queue_payload_type => 'my_payload_type',
multiple_consumers => TRUE
);
-- キューの作成
DBMS_AQADM.CREATE_QUEUE(
queue_name => 'my_queue',
queue_table => 'my_queue_table'
);
-- キューの使用を開始
DBMS_AQADM.START_QUEUE('my_queue');
END;
/
サブスクライバを登録します。
サブスクライバとは受信者のことだと思います。
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER(
queue_name => 'my_queue',
subscriber => SYS.AQ$_AGENT('SUBSCR',NULL,NULL),
rule => NULL
);
END;
/
テスト用のテーブルを作成します。
CREATE TABLE test_table (
column1 NUMBER,
column2 VARCHAR2(100)
);
PLSQLでデキュー処理(キューから値を取り出す)を作成します。
AUTO_RECVに引数が5つありますがPLSQL内では使っていません。
ですが後述のPLSQLの自動化設定のときに引数がないと動かないです。
理由は不明です。
-- デキュー
CREATE OR REPLACE PROCEDURE AUTO_RECV(
context IN RAW,
reginfo IN SYS.AQ$_REG_INFO,
descr IN SYS.AQ$_DESCRIPTOR,
payload IN VARCHAR2,
payloadl IN NUMBER
) IS
dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
mesg my_payload_type;
message_handle RAW(16);
BEGIN
DBMS_OUTPUT.ENABLE;
-- メッセージをデキュー
dequeue_options.CONSUMER_NAME := 'SUBSCR';
DBMS_AQ.DEQUEUE(
queue_name => 'my_queue',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => mesg,
msgid => message_handle
);
DBMS_OUTPUT.PUT_LINE(mesg.column1 || ' ' || mesg.column2);
insert into test_table values (mesg.column1, mesg.column2);
COMMIT;
END AUTO_RECV;
/
デキューをするPLSQLを自動化します。
DECLARE
REGINFO SYS.AQ$_REG_INFO;
REGINFOS SYS.AQ$_REG_INFO_LIST;
BEGIN
REGINFO := SYS.AQ$_REG_INFO(
'my_queue:SUBSCR',
DBMS_AQ.NAMESPACE_AQ,
'plsql://AUTO_RECV?PR=1',
NULL
);
REGINFOS := SYS.AQ$_REG_INFO_LIST(REGINFO);
DBMS_AQ.REGISTER( REGINFOS,1);
END;
/
エンキュー(キューに値を入れる)をします。
DECLARE
enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
payload my_payload_type;
message_handle RAW(16);
BEGIN
payload := my_payload_type(123, 'Hello, World!');
DBMS_AQ.ENQUEUE(
queue_name => 'my_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => payload,
msgid => message_handle
);
COMMIT;
END;
/
作成したものを削除するには
指定したキュー関連を削除できます。
サブスクライバも消えますがタイプは消えないです。
BEGIN
DBMS_AQADM.DROP_QUEUE_TABLE(
queue_table => 'my_queue_table',
force => TRUE -- force:trueにすることでキューを強制的に一時停止→削除→キューテーブルを削除できます。
);
END;
/
指定したタイプを削除できます。
BEGIN
EXECUTE IMMEDIATE 'DROP TYPE MY_PAYLOAD_TYPE';
END;
/
トリガー機能について
以降はchatGPTに教えてもらったけど使っていないコードです。
下記コードを実行するとmy_queueというテーブルまたはビューがないためエラーになります。
結局使わなかったので深堀はしてないですが、トリガーという機能は
テーブルなどに何らかのアクションがあると自動で何らかの処理を実行する機能みたいです。
下記の場合だとmy_queueというテーブルにデータが登録されたらそれを検知して
my_queueというキューからデータを取得するという処理です。
キューテーブルの作成でできたAQ$MY_QUEUE_TABLEというキューのデータを保持してるビューを
対象にすべきだと思ったのですがこのタイプではトリガーの作成はできないとエラーがでました。
-- トリガーを作成する
-- DECLAREからはデキューの処理
CREATE OR REPLACE TRIGGER my_queue_trigger
AFTER INSERT ON my_queue
FOR EACH ROW
DECLARE
dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
payload my_payload_type;
message_handle RAW(16);
BEGIN
DBMS_OUTPUT.ENABLE;
-- メッセージをデキュー
DBMS_AQ.DEQUEUE(
queue_name => 'my_queue',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => payload,
msgid => message_handle
);
DBMS_OUTPUT.PUT_LINE(payload.column1 || ' ' || payload.column2);
END;
/
参考
デキューの自動化関連は下記サイトを参考にさせていただきました。
https://qiita.com/kjmtgm/items/bb31794a7e780c853e29