Home » SQL & PL/SQL » SQL & PL/SQL » how write the message into a file
how write the message into a file [message #250480] Tue, 10 July 2007 04:11 Go to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi

i want to write the output of this trigger into a external file say suppose c:\dir\emp.txt

i have created the trigger but i want to write to file how do i do this?

CREATE OR REPLACE TRIGGER trig1 after insert or update or delete
on employees
for each row
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';

BEGIN

IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;

END;
/

regards

sudharshan
Re: how write the message into a file [message #250493 is a reply to message #250480] Tue, 10 July 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
utl_file is the package you need.

Remember Oracle writes on the SERVER not on the CLIENT.

Regards
Michel
Re: how write the message into a file [message #250529 is a reply to message #250480] Tue, 10 July 2007 06:27 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi

this is what i tried but i am getting an error.can someone help me.

CREATE OR REPLACE TRIGGER scott.REFRESH_DATA_TRG
AFTER INSERT OR UPDATE OR DELETE
ON scott.file_p FOR EACH ROW
DECLARE
v_file varchar2(100);
--v_direc constant varchar2(80) := 'E:\TEST_DIR';
v_testfile UTL_FILE.FILE_TYPE;
VMSG VARCHAR(20) := 'SUCESS';
V_TEXT VARCHAR2 (30) := 'Statement Level Trigger Fired';
vInHandle utl_file.file_type;
BEGIN
v_file := 'REFRESH.TXT';
v_testfile := utl_file.fopen('ref_student','refer','W');
IF INSERTING
THEN
DBMS_OUTPUT.put_line (vmsg || ' When Inserting');
utl_file.put_line(v_testfile, v_text, TRUE);
utl_file.fclose(v_testfile);
END IF;
END;
/


regards

sudharshan
Re: how write the message into a file [message #250532 is a reply to message #250480] Tue, 10 July 2007 06:37 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
And we are supposed to guess what the error is ???
Re: how write the message into a file [message #250534 is a reply to message #250480] Tue, 10 July 2007 06:42 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
sorry i made a small mistake

I CHANGED REF.TXT TO REFRESH.TXT

CREATE OR REPLACE TRIGGER scott.REFRESH_DATA_TRG
AFTER INSERT OR UPDATE OR DELETE
ON scott.file_p FOR EACH ROW
DECLARE
v_file varchar2(100);
--v_direc constant varchar2(80) := 'E:\TEST_DIR';
v_testfile UTL_FILE.FILE_TYPE;
VMSG VARCHAR(20) := 'SUCESS';
V_TEXT VARCHAR2 (30) := 'Statement Level Trigger Fired';
vInHandle utl_file.file_type;
BEGIN
v_file := 'REFRESH.TXT';
v_testfile := utl_file.fopen('ref_student','REFRESH.TXT','W');
IF INSERTING
THEN
DBMS_OUTPUT.put_line (vmsg || ' When Inserting');
utl_file.put_line(v_testfile, v_text, TRUE);
utl_file.fclose(v_testfile);
END IF;
END;


THE ERRORS ARE

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "TWPCTS.REFRESH_DATA_TRG", line 10
ORA-04088: error during execution of trigger 'TWPCTS.REFRESH_DATA_TRG'



REGARDS

SUDHARSHAN
Re: how write the message into a file [message #250536 is a reply to message #250480] Tue, 10 July 2007 06:49 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member

Did you even try to look up the error message ?

http://download.oracle.com/docs/cd/B10501_01/server.920/a96525/e29250.htm#680135

And then have a look here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5007.htm

Re: how write the message into a file [message #250541 is a reply to message #250534] Tue, 10 July 2007 06:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Check the answer here
http://forums.oracle.com/forums/thread.jspa?threadID=529603&tstart=0
Re: how write the message into a file [message #250544 is a reply to message #250480] Tue, 10 July 2007 07:03 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
i am not unable to trobuleshoot the error,so i knidly request someone to help me.

Regards

sudharshan
Re: how write the message into a file [message #250545 is a reply to message #250480] Tue, 10 July 2007 07:05 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
follow the links posted and you will have your help.
Re: how write the message into a file [message #250549 is a reply to message #250480] Tue, 10 July 2007 07:09 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi

i checked it and i am not able to find a clue if someone can give a solution or similar solution to this i will be happy

regards

sudharshan
Re: how write the message into a file [message #250550 is a reply to message #250549] Tue, 10 July 2007 07:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
look at the post from blushadow in the other thread on OTN that you are working. That is most likely your problem.
Re: how write the message into a file [message #250551 is a reply to message #250480] Tue, 10 July 2007 07:17 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi

Yes,there are also getting the same problem but no solution.

regards
sudharshan
Re: how write the message into a file [message #250554 is a reply to message #250551] Tue, 10 July 2007 07:20 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
sudharshan wrote on Tue, 10 July 2007 13:17
hi

Yes,there are also getting the same problem but no solution.

regards
sudharshan

I'll bet that blushadow's point (if read properly and understood) points towards your solution. Please, please read his/ her post carefully, then look at your code, repeat until you understand the solution. IT IS There
Re: how write the message into a file [message #250556 is a reply to message #250554] Tue, 10 July 2007 07:42 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Execute this command. You should be able to spot the difference.

select directory_name from dba_directories;

Re: how write the message into a file [message #250558 is a reply to message #250551] Tue, 10 July 2007 07:46 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Before creation this trigger please run this three line from sys:

CREATE DIRECTORY TEST_DIR as 'E:\TEST_DIR';
grant read, write on directory TEST_DIR to <YOUR SCHEMA NAME>;
ALTER SYSTEM SET utl_file_dir='E:\TEST_DIR' SCOPE=SPFILE;


Now compile the below trigger.
CREATE OR REPLACE TRIGGER scott.REFRESH_DATA_TRG
AFTER INSERT OR UPDATE OR DELETE
ON scott.file_p FOR EACH ROW
DECLARE
v_file varchar2(100);
--v_direc constant varchar2(80) := 'E:\TEST_DIR';
v_testfile UTL_FILE.FILE_TYPE;
VMSG VARCHAR(20) := 'SUCESS';
V_TEXT VARCHAR2 (30) := 'Statement Level Trigger Fired';
vInHandle utl_file.file_type;
BEGIN
v_file := 'REFRESH.TXT';
v_testfile := utl_file.fopen('TEST_DIR','REFRESH.TXT','W');
IF INSERTING
THEN
DBMS_OUTPUT.put_line (vmsg || ' When Inserting');
utl_file.put_line(v_testfile, v_text, TRUE);
utl_file.fclose(v_testfile);
END IF;
END;


Regards
Sanka
Re: how write the message into a file [message #250562 is a reply to message #250480] Tue, 10 July 2007 08:19 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
hi


select directory_name from dba_directories;


output
----
TEST_DIR

AVAILABLE


ALTER SYSTEM SET utl_file_dir='E:\TEST_DIR' SCOPE=SPFILE;

when i run this command i get an error.


ORA-32001: write to SPFILE requested but no SPFILE specified at startup


what to do?

regards

sudharshan
Re: how write the message into a file [message #250565 is a reply to message #250562] Tue, 10 July 2007 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are not using a spfile, so modify your init.ora

Regards
Michel
Re: how write the message into a file [message #250568 is a reply to message #250565] Tue, 10 July 2007 08:53 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

sudharshan,
If you are using the lower version of oracle then you have to do what Michel suggested and restarting the database to reflect the changes.

Regards
Sanka
Re: how write the message into a file [message #250636 is a reply to message #250480] Tue, 10 July 2007 13:27 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It's bad idea to write into OS file from trigger:
-- What happens when a number of users invoke the same trigger?
-- Each time the trigger must open and close the file (bad for performance).

HTH.
Michael
Previous Topic: partition exchange
Next Topic: CASE not working
Goto Forum:
  


Current Time: Mon Dec 05 21:05:37 CST 2016

Total time taken to generate the page: 0.29281 seconds