以前写过类似的,给你看看:注意:确保init.ora 文件中的UTL_FILE_DIR被设置为 = D:\Test,否则将得到一个 USER_EXECPTION 错误。 1) 编写三个过程: CREATE OR REPLACE PROCEDURE yang_test1 AS BEGIN DBMS_OUTPUT.PUT_LINE('Hai test1 ...' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); END; / CREATE OR REPLACE PROCEDURE yang_test2 AS v_file UTL_FILE.FILE_TYPE; BEGIN v_file := UTL_FILE.FOPEN('D:\TEST', 'test.txt', 'A'); UTL_FILE.PUT_LINE(v_file, 'Hai test2 ...' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); UTL_FILE.FCLOSE(v_file); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM); NULL; END; / CREATE OR REPLACE PROCEDURE yang_test3 AS v_file UTL_FILE.FILE_TYPE; BEGIN v_file := UTL_FILE.FOPEN('D:\TEST', 'test.txt', 'A'); UTL_FILE.PUT_LINE(v_file, 'Hai test2 again ...' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); UTL_FILE.FCLOSE(v_file); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM); NULL; END; / 2) 编写提交作业给作业队列匿名包: DECLARE v_jobnum BINARY_INTEGER; BEGIN DBMS_OUTPUT.ENABLE; DBMS_JOB.SUBMIT(v_jobnum, 'YANG_TEST1;', SYSDATE, 'SYSDATE+(1/(24*60*60))'); DBMS_OUTPUT.PUT_LINE('Frist Job Number assigned to test1 is:'|| v_jobnum); DBMS_JOB.SUBMIT(v_jobnum, 'YANG_TEST2;', SYSDATE, 'SYSDATE+(1/(24*60*60))'); DBMS_OUTPUT.PUT_LINE('Second Job Number assigned to test2 is:' || v_jobnum); DBMS_JOB.ISUBMIT(110, 'YANG_TEST3;', SYSDATE, 'SYSDATE+(1/(24*60*60))'); DBMS_OUTPUT.PUT_LINE('Third Job Number assigned to test3 is: 110'); END; / 3) 编写立即运行作业的匿名包: DECLARE l_jobnum NUMBER; BEGIN SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test1;'); DBMS_JOB.RUN(l_jobnum); SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test2;'); DBMS_JOB.RUN(l_jobnum); SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test3;'); DBMS_JOB.RUN(l_jobnum); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM); NULL; END; / 4) 编写删除作业的匿名包: DECLARE l_jobnum NUMBER; BEGIN SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test1;'); --查询正在运行的job是否已经完成 SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r.JOB = j.JOB; --如果没有对应的job号,则查询job队列是否存在 SELECT SID, TYPE, ID1, ID2--对应job号 FROM V$LOCK WHERE TYPE = 'JQ'; --如果对应job号不存在,查询job信息表 SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS; --查看对应的job的FAILURES,是否为0,若为0则执行成功,移除job,否则继续运行job
----end 查询 DBMS_JOB.REMOVE(l_jobnum); SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test2;'); DBMS_JOB.REMOVE(l_jobnum); SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test3;'); DBMS_JOB.REMOVE(l_jobnum); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM); NULL; END; / 5)创建用于删除job的job,删除成功后,则将自己的broken设置为true
file_handle UTL_FILE.FILE_TYPE;file_handle:=utl_file.fopen(location=>'xxx',filename=>'xxx',open_mode=>'xxx');
utl_file.put_line(file_handle,'xxx');
utl_file.fclose(file_handle);
谢谢你的回复,但我还不明白,你能告诉我上哪里找关于utl_file的教程吗,这些东西没接触过
google
能找到相关的教程.呵呵
记得给分.
USER_EXECPTION 错误。
1) 编写三个过程:
CREATE OR REPLACE PROCEDURE yang_test1 AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hai test1 ...' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
CREATE OR REPLACE PROCEDURE yang_test2 AS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('D:\TEST', 'test.txt', 'A');
UTL_FILE.PUT_LINE(v_file, 'Hai test2 ...' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM);
NULL;
END;
/
CREATE OR REPLACE PROCEDURE yang_test3 AS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('D:\TEST', 'test.txt', 'A');
UTL_FILE.PUT_LINE(v_file, 'Hai test2 again ...' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM);
NULL;
END;
/
2) 编写提交作业给作业队列匿名包:
DECLARE
v_jobnum BINARY_INTEGER;
BEGIN
DBMS_OUTPUT.ENABLE;
DBMS_JOB.SUBMIT(v_jobnum, 'YANG_TEST1;', SYSDATE,
'SYSDATE+(1/(24*60*60))');
DBMS_OUTPUT.PUT_LINE('Frist Job Number assigned to test1 is:'|| v_jobnum);
DBMS_JOB.SUBMIT(v_jobnum, 'YANG_TEST2;', SYSDATE,
'SYSDATE+(1/(24*60*60))');
DBMS_OUTPUT.PUT_LINE('Second Job Number assigned to test2 is:' ||
v_jobnum);
DBMS_JOB.ISUBMIT(110, 'YANG_TEST3;', SYSDATE, 'SYSDATE+(1/(24*60*60))');
DBMS_OUTPUT.PUT_LINE('Third Job Number assigned to test3 is: 110');
END;
/
3) 编写立即运行作业的匿名包:
DECLARE
l_jobnum NUMBER;
BEGIN
SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test1;');
DBMS_JOB.RUN(l_jobnum);
SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test2;');
DBMS_JOB.RUN(l_jobnum);
SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test3;');
DBMS_JOB.RUN(l_jobnum);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM);
NULL;
END;
/
4) 编写删除作业的匿名包:
DECLARE
l_jobnum NUMBER;
BEGIN
SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test1;');
--查询正在运行的job是否已经完成
SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
FROM DBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB = j.JOB;
--如果没有对应的job号,则查询job队列是否存在
SELECT SID, TYPE, ID1, ID2--对应job号
FROM V$LOCK
WHERE TYPE = 'JQ';
--如果对应job号不存在,查询job信息表
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
FROM DBA_JOBS;
--查看对应的job的FAILURES,是否为0,若为0则执行成功,移除job,否则继续运行job
----end 查询
DBMS_JOB.REMOVE(l_jobnum);
SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test2;');
DBMS_JOB.REMOVE(l_jobnum);
SELECT job INTO l_jobnum FROM USER_JOBS WHERE what = UPPER('yang_test3;');
DBMS_JOB.REMOVE(l_jobnum);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || TO_CHAR(SQLCODE) || SQLERRM);
NULL;
END;
/
5)创建用于删除job的job,删除成功后,则将自己的broken设置为true