Oracle 调度程序作业日志输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4505033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle scheduler job log output
提问by JOTN
I'm using dbms_scheduler to execute a PL/SQL stored procedure. I would like to be able to have that code create some text logging output and associate it with the run to verify it's working, but I can't find anything to do that in the docs. Is there a facility to do this that I'm missing? This is an 11g database running under Unix. It would be real nice if I could use dbms_output so I could also run it from sqlplus and get output.
我正在使用 dbms_scheduler 来执行 PL/SQL 存储过程。我希望能够让该代码创建一些文本日志输出并将其与运行相关联以验证它是否正常工作，但我在文档中找不到任何可以执行此操作的内容。有没有设施可以做到这一点，我失踪了？这是一个运行在 Unix 下的 11g 数据库。如果我可以使用 dbms_output 这样我也可以从 sqlplus 运行它并获得输出，那就太好了。
回答by Eddie Awad
There are a bunch of Oracle Scheduler data dictionary views that will help you monitor jobs. Here are two documentation pages related to that:
有一堆 Oracle Scheduler 数据字典视图可以帮助您监控作业。这是与此相关的两个文档页面：
Moreover, Oracle Scheduler declares some internal Scheduler variables that you can use like any other PL/SQL identifier in your PL/SQL stored procedure. Here is the listof these variables.
此外，Oracle 调度程序声明了一些内部调度程序变量，您可以像使用 PL/SQL 存储过程中的任何其他 PL/SQL 标识符一样使用它们。这是这些变量的列表。
If you want to log application specific information, I suggest you create your own log table. You can then insert into this table from within your stored procedure. You can even insert any of the Scheduler's internal variables there, like job_name and job_scheduled_start.
如果您想记录特定于应用程序的信息，我建议您创建自己的日志表。然后，您可以从存储过程中插入到该表中。您甚至可以在其中插入任何调度程序的内部变量，例如 job_name 和 job_scheduled_start。
回答by Randy
i make a table JOB_LOG insert into that table from inside your procedure...
我从你的过程中将一个表 JOB_LOG 插入到那个表中......
回答by Joshua Huber
I agree with what the others have said. Here's the actual nuts and bolts, but with a nice interface, too. The way I typically do this:
我同意其他人所说的。这是实际的螺母和螺栓，但也有一个很好的界面。我通常这样做的方式：
Make a logging table:
制作一个日志表：
CREATE TABLE job_log (
ts TIMESTAMP DEFAULT SYSTIMESTAMP PRIMARY KEY
, message VARCHAR2(255)
);
Make a stored proc that easily writes into your log table:
制作一个可以轻松写入日志表的存储过程：
CREATE OR REPLACE PROCEDURE job_logger (v_message VARCHAR2)
IS
BEGIN
INSERT INTO job_log(message) VALUES (v_message);
COMMIT;
END;
/
Then within your job, you are probably running a stored procedure. Within your own stored procedure, simply add lines that call the job_logger()procedure to write to your log. This keeps the ugly INSERT ... COMMIT clutter out of your interesting stored proc code.
然后在您的工作中，您可能正在运行一个存储过程。在您自己的存储过程中，只需添加调用job_logger()过程以写入日志的行。这使丑陋的 INSERT ... COMMIT 杂乱无章地远离您有趣的存储过程代码。
CREATE OR REPLACE PROCEDURE foo
IS
BEGIN
job_logger('Starting job foo.');
...
{your code here}
...
job_logger('Another message that will be logged.');
...
job_logger('Completed running job foo.');
EXCEPTION
...
job_logger('Oops, something bad happened!');
...
END;
/
Your log table is automatically timestamped and indexed by the primary key. To view the log, you might run this
您的日志表会自动加时间戳并由主键索引。要查看日志，您可以运行此
SELECT * FROM job_log ORDER BY ts DESC;
Now if would rather not use the Oracle scheduler, and want instead to use the DBMS_OUTPUT way of writing output, and want to run this under a Unix shell, that is possible also.
现在，如果不想使用 Oracle 调度程序，而是想使用 DBMS_OUTPUT 写入输出的方式，并且想在 Unix shell 下运行它，那也是可能的。
You would make a script that calls sqlplus, somewhat like this. If your user is SCOTT and the stored proc is called FOO,
您将制作一个调用 sqlplus 的脚本，有点像这样。如果您的用户是 SCOTT 并且存储的过程称为 FOO，
#!/bin/sh
. /whatever/script/that/sets/your/oracle/environment
echo "
set serveroutput on feedback off
exec foo
" | sqlplus -s -l scott/tiger@orcl
Note, the -s flag suppresses the Oracle SQL Plus banner for cleaner output. The -l flag makes it so that sqlplus will abort if the password is bad or something else wrong, rather than try to prompt for username. Feedback off suppresses the PL/SQL "Anonymous block completed" message.
请注意，-s 标志会抑制 Oracle SQL Plus 横幅以获得更清晰的输出。-l 标志使得 sqlplus 将在密码错误或其他错误时中止，而不是尝试提示输入用户名。反馈关闭抑制 PL/SQL“匿名块已完成”消息。
If you want to schedule this, you can call it from cron like this:
如果你想安排这个，你可以像这样从 cron 调用它：
00 00 * * * /path/to/the/above/script.sh > /where/you/want/your/output/saved.log 2>&1
