自动任务的问题
我写了个存储过程,是用来从一个Oracle数据库向本数据库导数据用的,手工execute没有问题,但是如果作为自动活动,老是会出错....
Oracle那提示信息完全没有参考意义...大家帮我看看是什么问题...
另外,关于job的二个参数我已经检查过了,现在的问题不是不运行,而是发现不断发生失败(Failure)
存储过程如下:
CREATE OR REPLACE PROCEDURE AutoUpdate AS
v_LastCreateTime DATE;
v_creation_date DATE;
v_Memo import_log.memo%TYPE;
v_tag_number asset_info.tag_number%TYPE;
CURSOR c_FAAsset IS
SELECT * FROM faview
WHERE v_LastCreateTime IS NULL
OR
creation_date>v_LastCreateTime;
BEGIN
--只取以前从FA中导入的记录的创建时间
SELECT MAX(creation_date)
INTO v_LastCreateTime
FROM asset_info
WHERE is_import='Y';
FOR v_FaAssinfo IN c_FAAsset LOOP
BEGIN
--执行插入动作,记录import_log
v_tag_number := v_FaAssinfo.tag_number;
v_creation_date := v_FaAssinfo.creation_date;
INSERT INTO asset_info(
db_asset_id,
asset_id,
tag_number,
fa_description,
attribute3,
attribute4,
attribute5,
date_placed_in_service,
original_cost,
employee_number,
name,
segment2,
book_type_code,
description,
last_update_date,
last_updated_by,
creation_date,
created_by,
is_fa,
is_import
)
VALUES (
seq_asset.NextVal,
v_FaAssinfo.asset_id,
v_tag_number,
v_FaAssinfo.fa_description,
v_FaAssinfo.attribute3,
v_FaAssinfo.attribute4,
v_FaAssinfo.attribute5,
v_FaAssinfo.date_placed_in_service,
v_FaAssinfo.original_cost,
v_FaAssinfo.employee_number,
v_FaAssinfo.name,
v_FaAssinfo.segment2,
v_FaAssinfo.book_type_code,
v_FaAssinfo.description,
v_FaAssinfo.creation_date,
v_FaAssinfo.created_by,
v_creation_date,
v_FaAssinfo.created_by,
'是',
'是'
);
COMMIT;
AddImport_Log(v_tag_number,v_creation_date,'成功','');
EXCEPTION
WHEN OTHERS THEN
v_Memo := SUBSTR(SQLERRM,1,128);
AddImport_Log(v_tag_number,v_creation_date,'失败',v_Memo);
END;
END LOOP;
END AutoUpdate;
/
/*记录导入时候的Log记录*/
CREATE OR REPLACE PROCEDURE AddImport_Log(
v_Tag_Number import_log.tag_number%TYPE,
v_creation_date import_log.creation_date%TYPE,
v_Import_Status import_log.import_status%TYPE,
v_Memo import_log.memo%TYPE
) AS
BEGIN
INSERT INTO import_log(
tag_number,
creation_date,
import_status,
memo
)
VALUES(
v_Tag_Number,
v_creation_date,
v_Import_Status,
v_Memo
);
COMMIT;
END AddImport_Log;
/