Problem with UTL_FILE.FCOPY in a trigger

Question

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

Answer 1

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.

Answer 2

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.