スケジュールジョブのジョブ名を変数化したい

OracleDBのトリガーで、スケジュールジョブを作成しているのですが
初回インサート処理は成功するも
２回目のインサート処理では

ORA-27478 ジョブが実行中です。

とエラーで終了します。

スケジュールジョブを作成する時に、一意のジョブ名ではなく変数をつけてユニークなジョブ名にしたいです。

create or replace trigger embulk_tr

after insert on INPUT_TEST for each row

DECLARE pragma autonomous_transaction;

begin

dbms_output.put_line('-------------------------');
dbms_output.put_line('OracleDB to BigQuery Bulk Insert Start');

	IF INSERTING THEN
		DBMS_SCHEDULER.CREATE_JOB (
		JOB_NAME   => 'Embulk_Test',
		JOB_TYPE   => 'EXECUTABLE',
		JOB_ACTION => 'C:\Users\xxxxxx\Desktop\embulk\cmd.exe /c C:\Users\xxxxxx\Desktop\embulk\oracle_to_bigquery.bat',
		start_date => TO_DATE('2018/11/13 00:00:00','yyyy/mm/dd hh24:mi:ss'),
		end_date   => TO_DATE('2999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'),
		auto_drop => TRUE,
		enabled => TRUE
		);
	END IF;

dbms_output.put_line('OracleDB to BigQuery Bulk Insert End');
dbms_output.put_line('-------------------------');

end;
/

更新箇所

SQL> create sequence Embulk_Test;

順序が作成されました。

SQL> select Embulk_Test.nextval from dual;

   NEXTVAL
----------
         1

SQL> select Embulk_Test.nextval from dual;

   NEXTVAL
----------
         2



<<pl/sql>>
create or replace trigger embulk_tr

after insert on INPUT_TEST for each row

DECLARE pragma autonomous_transaction;

begin

dbms_output.put_line('-------------------------');
dbms_output.put_line('OracleDB to BigQuery Bulk Insert Start');

	IF INSERTING THEN
		DBMS_SCHEDULER.CREATE_JOB (
		JOB_NAME   => 'Embulk_Test.nextval',
		JOB_TYPE   => 'EXECUTABLE',
		JOB_ACTION => 'C:\Users\yazaki\Desktop\embulk\cmd.exe /c C:\Users\yazaki\Desktop\embulk\oracle_to_bigquery.bat',
		start_date => TO_DATE('2018/11/13 00:00:00','yyyy/mm/dd hh24:mi:ss'),
		end_date   => TO_DATE('2999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'),
		auto_drop => TRUE,
		enabled => TRUE
		);
	END IF;

dbms_output.put_line('OracleDB to BigQuery Bulk Insert End');
dbms_output.put_line('-------------------------');

DBMS_SCHEDULER.DROP_JOB('Embulk_Test.nextval', force => true);

end;
/



<<業務処理を実行>>
SQL> exec CRE_SAMPLE_DATA;
BEGIN CRE_SAMPLE_DATA; END;

*
行1でエラーが発生しました。:
ORA-23306: スキーマEMBULK_TESTが存在しません。
ORA-06512: "SYS.DBMS_ISCHED", 行175
ORA-06512: "SYS.DBMS_SCHEDULER", 行288
ORA-06512: "SYSTEM.EMBULK_TR", 行9
ORA-04088: トリガー'SYSTEM.EMBULK_TR'の実行中にエラーが発生しました
ORA-06512: "SYSTEM.CRE_SAMPLE_DATA", 行23
ORA-06512: 行1

行動規範の内容に同意します

回答1件

ベストアンサー

JOB_NAMEの後ろにSEQUENCE を使って連番を追加してみては？

SQL
1-- ジョブ名と同じSEQUENCE名というのも混乱するでしょうから、
2CREATE SEQUENCE SEQ_Embulk_Test MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 39 NOCACHE ORDER NOCYCLE ;

SQL
1create or replace trigger embulk_tr
2
3after insert on INPUT_TEST for each row
4
5DECLARE
6    pragma autonomous_transaction;
7    L_SEQ_Embulk_Test  NUMBER ;
8begin
9    -- 意味のある字下げをしないから、コードを読みにくい
10    dbms_output.put_line('-------------------------');
11    dbms_output.put_line('OracleDB to BigQuery Bulk Insert Start');
12    -- ↓ 無名ブロック
13    SELECT SEQ_Embulk_Test.NEXTVAL INTO L_SEQ_Embulk_Test FROM DUAL ;
14
15    IF INSERTING THEN
16        DBMS_SCHEDULER.CREATE_JOB (                                -- ↓桁あふれは考慮してない
17        JOB_NAME   => 'Embulk_Test_' || TO_CHAR(L_SEQ_Embulk_Test, 'FM0000000000000000000'),
18        JOB_TYPE   => 'EXECUTABLE',
19        JOB_ACTION => 'C:\Users\xxxxxx\Desktop\embulk\cmd.exe /c C:\Users\xxxxxx\Desktop\embulk\oracle_to_bigquery.bat',
20        start_date => TO_DATE('2018/11/13 00:00:00','yyyy/mm/dd hh24:mi:ss'),
21        end_date   => TO_DATE('2999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'),
22        auto_drop => TRUE,
23        enabled => TRUE
24        );
25    END IF;
26
27    dbms_output.put_line('OracleDB to BigQuery Bulk Insert End');
28    dbms_output.put_line('-------------------------');
29end;
30/