如何让存储过程等待所有提交作业完成

Question

我必须在这里提交作业。

dbms_scheduler.create_job(job_name   => 'NewSkus',
job_type   => 'PLSQL_BLOCK',
job_action => 'begin IC_REFRESH_NEW_SKUS_P(0); end;',
start_date => sysdate,
enabled    => true,
comments   => 'temporary job,will be delete soon after complete!',
auto_drop  => true
);

dbms_scheduler.create_job(job_name   => 'LocationHeader',
job_type   => 'PLSQL_BLOCK',
job_action => 'begin IC_REFRESH_LOCATION_HEADER_P(0); end;',
start_date => sysdate,
enabled    => true,
comments   => 'temporary job,will be delete soon after complete!',
auto_drop  => true
);  

现在我有了另一个名为"ABCDEFG"的存储过程，这个存储过程需要在这两个作业完成后运行。我尝试在提交作业后编写此条件。

WHILE jobNumber > 0
LOOP
SELECT count(*) INTO jobNumber
FROM   user_scheduler_jobs srj
WHERE  srj.job_name IN ('NewSkus','LocationHeader');
DBMS_LOCK.SLEEP(10);
END LOOP;

不幸的是，最后存储过程完成了，但有一个作业仍在运行，所以我的条件块不起作用。

有人能帮我吗？欣赏

Answer 1

我创建了一个通用包，允许我使用多个数据库链接在多个作业中执行任意SQL。这使用Oracle DBMS_PIPE包来同步输出。

为将要使用的命名管道定义一个唯一的名称。每个作业的结束执行以下程序以通知完成：

PROCEDURE send_completedmessage( p_pipename   IN pipename_t
, p_synchid    IN synch_id_t
, p_errmsg     IN VARCHAR2 DEFAULT NULL ) AS
l_routine   CONSTANT oracleobj_t := 'send_completedmessage';
l_status             NUMBER;
BEGIN
DBMS_PIPE.pack_message( p_synchid );
DBMS_PIPE.pack_message( p_errmsg );
l_status   := DBMS_PIPE.send_message( pipename => p_pipename );
IF l_status <> 0
THEN
raise_application_error(
-20000
, 'Error writing p_synchid ' || p_synchid || ' to pipe ' || p_pipename );
END IF;
END send_completedmessage;

你需要收到通知，作业已经完成，如下所示：

PROCEDURE get_results( p_pipename   IN     pipename_t
, p_dblinks    IN OUT db_link_tt
, p_timeout    IN     INTEGER ) AS
l_routine   CONSTANT oracleobj_t := 'get_results';
l_result             INTEGER := 0;
l_db_link            VARCHAR2( 128 );
l_found              BOOLEAN;
l_nfound             PLS_INTEGER := 0;
BEGIN
--        l_result   := DBMS_PIPE.receive_message (pipename => p_pipename, timeout => 10);
WHILE l_result = 0 AND l_nfound < p_dblinks.COUNT
LOOP
l_result   := DBMS_PIPE.receive_message( pipename => p_pipename, timeout => p_timeout );
-- return if timed out
IF l_result = 1
THEN
make_log_entry( p_routine   => l_routine
, p_message   => 'receive message timed out for pipe ' || p_pipename );
EXIT;
END IF;
DBMS_PIPE.unpack_message( l_db_link );
l_found    := FALSE;
FOR i IN 1 .. p_dblinks.COUNT
LOOP
IF p_dblinks( i ).db_link = l_db_link
THEN
p_dblinks( i ).completed   := 1;
DBMS_PIPE.unpack_message( p_dblinks( i ).errmsg );
l_found                    := TRUE;
l_nfound                   := l_nfound + 1;
EXIT;
END IF;
END LOOP;
IF NOT l_found
THEN
raise_application_error(
-20000
, 'Non-matching values returned from pipe ' || p_pipename || ': ' || l_db_link );
END IF;
END LOOP;
l_result   := DBMS_PIPE.remove_pipe( pipename => p_pipename );
EXCEPTION
WHEN OTHERS
THEN
make_log_error_entry(
p_routine   => l_routine
, p_message   => p_pipename || ': ' || l_db_link || t.crlf || tochar( p_dblinks ) );
l_result   := DBMS_PIPE.remove_pipe( pipename => p_pipename );
RAISE;
END get_results;