| write in a file in a distant computer [message #36424] |
Tue, 27 November 2001 01:42  |
Vince
Messages: 11
Registered: December 2000
|
Junior Member |
|
|
hello,
I have a question:
I must export data in a file with a store procedure.
but the file can be anywhere in the network. the destination of the file is in a text file in the server.
how can I do?
I use the UTL_FILE to read and write in file, but the file is in the server.
thanks
----------------------------------------------------------------------
|
|
|
|
| Re: write in a file in a distant computer [message #36456 is a reply to message #36424] |
Wed, 28 November 2001 22:27  |
tinel
Messages: 42
Registered: November 2001
|
Member |
|
|
you have to set the utl_file_dir in the init<oracle_sid>.ora on the server, you can set more then one path in the file, then you use utl_file package like this:
the p_file_path argument must be the path you set in the init.ora file, not the path on your computer, because you run your stored procedure on the server.
CREATE OR REPLACE PROCEDURE open_file
(p_file_path IN VARCHAR2, P_file_name IN VARCHAR2, p_mode IN VARCHAR2) IS
f_id utl_file.file_type;
arg1 VARCHAR2(20);
arg2 VARCHAR2(20);
arg3 VARCHAR2(20);
p_read VARCHAR2(100);
BEGIN
arg1 := 'test';
arg2 := 'testing';
arg3 := 'and testing';
f_id := utl_file.fopen(p_file_path,p_file_name,p_mode);
IF (utl_file.is_open(f_id)) THEN
dbms_output.put_line('working!');
utl_file.put_line(f_id, 'string to be inserted in the file');
-- for formated writing %s is replaced by arg1 ..., and /n inert a line terminator
utl_file.putf(f_id, '%sn%sn%s', arg1, arg2, arg3);
-- for read from file use the next loop block, the file must be open in r mode
/*
LOOP
BEGIN
utl_file.get_line(f_id, p_read);
dbms_output.put_line(p_read);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
*/
utl_file.fclose(f_id);
ELSE
dbms_output.put_line('not working!');
END IF;
EXCEPTION
WHEN utl_file.invalid_path THEN
dbms_output.put_line('Wrong way!');
WHEN utl_file.INVALID_MODE THEN
dbms_output.put_line('Invalid mode!');
WHEN utl_file.INVALID_operation THEN
dbms_output.put_line('Invalid operation!');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No data found!');
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('Wrong file');
WHEN utl_file.write_error THEN
dbms_output.put_line('Write error!');
WHEN utl_file.read_error THEN
dbms_output.put_line('Read erro!');
WHEN utl_file.internal_error THEN
dbms_output.put_line('Pl/sql error!');
END;
this should be working
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
