PL/SQL procedures and triggers

I have to create an empty file in one of the directories on unix server. This event should be triggered by a trigger attached to one table. So whenever there is an insert or update to that table it should invoke the trigger which should inturn create the blank file.
Can anyone help me out.... i am not very comfortable in PL/SQL.

One option:
Create a java method that creates a directory.
Load this java file as a Java Stored Procedure
Create an ON UPDATE INSERT TRIGGER on this table that calls the java stored procedure passing the name of the new directory.
...
Another option using Oracle Pipes is discussed here:
http://www.oreview.com/9704flet.htm

Hi Victor,

That is how its currently setup in my project except for that the directory is hardcoded in the java program. For some reason the java program is throwing an exception :

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException

ORA-04088: error during execution of trigger 'MAPPER.INSUPDACCTMONTHTRG'



Also can you explain how a compiled java class is loaded into oracle installed on a unix server.

Still you have another option to do so. look at the following code:

DECALRE
v_file UTL_FILE.FILE_TYPE;
v_filename varchar2(40):='emp.log';
v_locat varchar2(200):='/orant/bin';
BEGIN
if utl_file.is_open(v_file) then
dbms_output.put_line('file opened');
else
dbms_output.put_line('file closed');
end if;
v_file:=SYS.UTL_FILE.FOPEN(v_locat,v_filename,'w');
UTL_FILE.FCLOSE(v_file);
dbms_output.put_line('File has been created');
EXCEPTION
when UTL_FILE.invalid_path then
raise_application_error(-2000, '****Exception caught : Invalid path defined');
when UTL_FILE.invalid_mode then
raise_application_error(-2001, '****Exception caught : Invalid mode');
END;
/

Before run the above code, Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. For example:

UTL_FILE_DIR = <directory name>

OR

UTL_FILE_DIR = *

'*' has a special meaning this entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

Regards

Hi Shafique,

I tried you suggestions and its working when i try to update the table in sqlplus.
However the same setup when tried through the toad tool or a GUI running on windows gives me the following error:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "ITSTMAPPER.TESTPROC", line 12
ORA-06512: at "ITSTMAPPER.TESTCTRIG", line 1
ORA-04088: error during execution of trigger 'ITSTMAPPER.TESTCTRIG'

Can you help me out.




Here is my procedure code:

CREATE OR REPLACE PROCEDURE testproc
IS
v_file UTL_FILE.FILE_TYPE;
v_filename varchar2(40):='emp.log';
v_locat varchar2(200):='/var/opt/intf/data/stage';
BEGIN
if UTL_FILE.is_open(v_file) then
dbms_output.put_line('file opened');
else
dbms_output.put_line('file closed');
end if;
v_file:=SYS.UTL_FILE.FOPEN(v_locat,v_filename,'w');
UTL_FILE.FCLOSE(v_file);
dbms_output.put_line('File has been created');
EXCEPTION
when UTL_FILE.invalid_path then
raise_application_error(-20000, '****Exception caught : Invalid path defined');
when UTL_FILE.invalid_mode then
raise_application_error(-20001, '****Exception caught : Invalid mode');
END;
/




Here is my trigger code:

CREATE OR REPLACE TRIGGER testctrig
AFTER UPDATE OR INSERT ON ControlData
FOR EACH ROW
CALL testproc
;