触发器中的 UTL_FILE.FCOPY 问题

发布于 2024-08-22 15:04:50 字数 1324 浏览 9 评论 0原文

我是触发器新手。我在下面创建了一个触发器：

CREATE OR REPLACE TRIGGER ac01_control_trigg
AFTER INSERT ON AC01_CONTROL_TEST 
FOR EACH ROW
DECLARE
    BEGIN
   IF :NEW.cur_pgm_name = 'LSN' AND :NEW.nxt_pgm_name ='MD' AND :NEW.file_status='RD' THEN 
   UTL_FILE.Fcopy (:NEW.FILE_PATH,:NEW.FILE_NAME,:NEW.FILE_PATH,'CP.txt');
    INSERT INTO AC1_CONTROL_TEST 
    ( FILE_NAME, FILE_PATH,CUR_PGM_NAME,NXT_PGM_NAME,FILE_STATUS )
    VALUES (:NEW.FILE_NAME, :NEW.FILE_PATH,:NEW.CUR_PGM_NAME,'MD_MPS',:NEW.FILE_STATUS);   
END IF;
END ac01_control_trigg;

创建触发器时没有编译错误。但是当我尝试在表中插入条目时，出现以下错误。

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 258
ORA-06512: at "SYS.UTL_FILE", line 1167
ORA-06512: at "CNGDB18.AC01_CONTROL_TRIGG", line 4
ORA-04088: error during execution of trigger 'CNGDB18.AC01_CONTROL_TRIGG'

有人可以帮忙吗？

编辑：插入语句如下：

INSERT INTO AC01_CONTROL_TEST
   (FILE_NAME, FILE_PATH, CUR_PGM_NAME, NXT_PGM_NAME, FILE_STATUS)
 VALUES
   ('SSMS_FSMS_ID000386_T20081224153437_OFWPAC_OMG011.DAT', '/dhiusr3/sub/cng/cngwrk18/var/m3g/projs/up/data/MAF_SMS/20091216/13/15490000/', 'LSN', 'MD', 'RD');

在unix上：

0> pwd
/dhiusr3/sub/cng/cngwrk18/var/m3g/projs/up/data/MAF_SMS/20091216/13/15490000

原文

I am new to triggers.
I created a trigger below:

CREATE OR REPLACE TRIGGER ac01_control_trigg
AFTER INSERT ON AC01_CONTROL_TEST 
FOR EACH ROW
DECLARE
    BEGIN
   IF :NEW.cur_pgm_name = 'LSN' AND :NEW.nxt_pgm_name ='MD' AND :NEW.file_status='RD' THEN 
   UTL_FILE.Fcopy (:NEW.FILE_PATH,:NEW.FILE_NAME,:NEW.FILE_PATH,'CP.txt');
    INSERT INTO AC1_CONTROL_TEST 
    ( FILE_NAME, FILE_PATH,CUR_PGM_NAME,NXT_PGM_NAME,FILE_STATUS )
    VALUES (:NEW.FILE_NAME, :NEW.FILE_PATH,:NEW.CUR_PGM_NAME,'MD_MPS',:NEW.FILE_STATUS);   
END IF;
END ac01_control_trigg;

there were no compile errors while creating the trigger.
but when i am trying to insert an entry in the table i am getting the following errors.

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 258
ORA-06512: at "SYS.UTL_FILE", line 1167
ORA-06512: at "CNGDB18.AC01_CONTROL_TRIGG", line 4
ORA-04088: error during execution of trigger 'CNGDB18.AC01_CONTROL_TRIGG'

could anybody please help?

EDIT:insert statement is below:

INSERT INTO AC01_CONTROL_TEST
   (FILE_NAME, FILE_PATH, CUR_PGM_NAME, NXT_PGM_NAME, FILE_STATUS)
 VALUES
   ('SSMS_FSMS_ID000386_T20081224153437_OFWPAC_OMG011.DAT', '/dhiusr3/sub/cng/cngwrk18/var/m3g/projs/up/data/MAF_SMS/20091216/13/15490000/', 'LSN', 'MD', 'RD');

On unix:

0> pwd
/dhiusr3/sub/cng/cngwrk18/var/m3g/projs/up/data/MAF_SMS/20091216/13/15490000

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

彼岸花似海 2024-08-29 15:04:50

有两种方法可以为 UTL_FILE 指定目标操作系统目录。您正在使用的方法 - 操作系统路径 - 是较旧的方法。当使用实际路径时，我们需要将它们包含在 INIT.ORA 文件（或 spfile）的 UTL_FILE 参数中。这是一个令人头疼的问题，因为每次我们更改参数时都必须弹回数据库。

另一种方法是使用目录对象。

create or replace directory whatever as 
  '/dhiusr3/sub/cng/cngwrk18/var/m3g/projs/up/data/MAF_SMS/20091216/13/15490000'
/
grant read, write on directory whatever to benjamin
/

这些语句必须由具有 CREATE ANY DIRECTORY 权限的用户（可能是 DBA）运行。

使用目录的好处是我们可以创建目录对象而无需弹回数据库。它还使维护变得更容易，因为我们只需要在一处更改路径（尽管这不适用于您的情况）。

处理路径的旧方法的一个优点是我们可以在 UTL_FILE_DIR 参数中使用 *，因此我们可以指定子树。我们必须为每个目录显式声明一个单独的 Directory 对象。许多人认为 UTL_FILE_DIR 在这方面的灵活性是一个安全错误，而不是一个优点。除此之外，UTL_FILE_DIR 公开的目录实际上被授予了 PUBLIC，而我们可以以更低的粒度级别授予 Directory 对象特定的权限。

无论我们选择哪种方法目标目录，oracle 操作系统用户拥有必要的访问级别都至关重要。换句话说，如果oracle操作系统用户无法在unix中的该目录中写入（或读取）文件，UTL_FILE将抛出ORA-29280：无效的目录路径。

不确定尾部斜杠是否会成为问题。 CREATE DIRECTORY 语句并不关心，命名路径方法可能更复杂。但这当然没有必要。

There are two ways of specifying target OS directories for UTL_FILE. The method you are using - the OS path - is the older way. When using actual paths we need to include them in the UTL_FILE parameter in the INIT.ORA file (or the spfile). This is a pain in the neck, because the database has to be bounced each time we change the parameter.

The alternative is to use a directory object instead.

create or replace directory whatever as 
  '/dhiusr3/sub/cng/cngwrk18/var/m3g/projs/up/data/MAF_SMS/20091216/13/15490000'
/
grant read, write on directory whatever to benjamin
/

These statements must be run by a user with the CREATE ANY DIRECTORY privilege (probably a DBA).

The advantage of using directories is that we can create directory objects without bouncing the database. It also makes maintenance easier, because we only have to change the path in one place (although that doesn't apply in your case).

The one advantage of the older method of handling paths is that we can use * in the UTL_FILE_DIR parameter, so we can specify sub-trees. We have to explicitly declare a separate Directory object for each directory. Many people regard the flexibility of UTL_FILE_DIR in this regard as a security bug rather than an advantage. Apart from anything else, directories exposed by UTL_FILE_DIR are effectively granted to PUBLIC, whereas we can grant specific privileges on Directory objects with a much lower level of granularity.

Regardless of which approach we choose the target directory it is crucial that the oracle OS user has the necessary level of access. In other words, if the oracle OS user cannot write (or read) a file in that directory in unix, UTL_FILE will hurl ORA-29280: invalid directory path.

Not sure whether the trailing slash would be a problem. The CREATE DIRECTORY statement doesn't care, the named path approach might be fussier. But it is certainly not necessary.

回复收藏 0 原文

梦在深巷 2024-08-29 15:04:50

您确定这

UTL_FILE.Fcopy (:NEW.FILE_PATH,:NEW.FILE_NAME,:NEW.FILE_PATH,'CP.txt');

为您提供了有效的路径吗？
:NEW.FILE_PATH 是根目录的完整路径吗？
它有领先/包括吗？

Oracle 并不是我真正的领域，但请查看此链接访问 oracle.com 获取一些指导。我无法判断您的触发器是否正确，但根据我对错误的阅读，FCOPY 函数抱怨路径不正确。

Are you sure that

UTL_FILE.Fcopy (:NEW.FILE_PATH,:NEW.FILE_NAME,:NEW.FILE_PATH,'CP.txt');

is giving you a valid path?
is :NEW.FILE_PATH the full path from the root dir?
does it have the leading / included?

Oracle isn't really my area but check out this link to oracle.com for some guidance. I can't tell whether your Trigger is correct or not but to my reading of the error its that the FCOPY function is complaining about the path not being correct.

回复收藏 0 原文

~没有更多了~