Home » SQL & PL/SQL » SQL & PL/SQL » how to write a file with PL/SQL
how to write a file with PL/SQL [message #36461] Thu, 29 November 2001 00:42 Go to next message
Gianni
Messages: 7
Registered: November 2001
Junior Member
Hi to everybody,
I want to know how to write a file using PL/SQL.
I have Oracle in a server and I use WIn2000 as client.
I've also found this code:

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp','myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
END;
/
But it raises the exception 'cause it don't find the folder in the server.
Thanks in advance,
Gianni.

----------------------------------------------------------------------
Re: how to write a file with PL/SQL [message #36464 is a reply to message #36461] Thu, 29 November 2001 02:31 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: precedure problem
Next Topic: cursor as a parameter in Stored Procedure??
Goto Forum:
  


Current Time: Sun Mar 29 23:56:40 CDT 2020