-
How to copy put data into text file, besides using "spool"?
-
try using supplied package UTL_FILE ...
-
Grateful if you could explain on its usage coz I'am quite new.
-
1) in init.ora file :
UTL_FILE_DIR=directory where you want to write to, or * for all directories
2) using PL/SQL, you can write in a file using :
DECLARE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
DBMS_OUTPUT.ENABLE(100000);
FILE_HANDLE:=UTL_FILE.FOPEN('directory','file','open mode (r,w,a)');
UTL_FILE.PUTF(FILE_HANDLE,'%s',variable);
UTL_FILE.FFLUSH(FILE_HANDLE);
UTL_FILE.FCLOSE_ALL;
END;
/
well, this is just an example, you'll have to personnalize it, and I recommend reading the docs about UTL_FILE for a better use of this package
-
Hi
here is an example:
create or replace procedure test
is
x utl_file.file_type; -- file handle of os flat file
begin
-- open file to write into and obtain its x
x := utl_file.fopen('here you write the utl_fiel directory','myfile.txt','w');
-- write a line of text out to the file.
utl_file.put_line(x, 'this is line test');
-- close the file.
utl_file.fclose(x);
-- open the same file to read from.
x := utl_file.fopen('here you write the utl_fiel directory','myfile.txt','r');
-- read a line from the file.
utl_file.get_line (x, retrieved_buffer);
-- close the file.
utl_file.fclose(file_handle);
exception
when utl_file.invalid_path then
when utl_file.read_error then
when utl_file.write_error then
when others then
end;
for more reading look at Note:33755.1 on yhr metalink
-
Let say I have a table with 200,000 rows and I want to copy out these records into a text-file, column deliminated by ie. a comma. How would I accomplish this ?
-
create or replace procedure test
is
cursor get_lines_data is
select column1,column2,...
from table_test
where ...;
-- cursore row type
rec_line_data get_lines_data%rowtype;
v_log_file utl_file.file_type; -- file handle of os flat file
v_error varchar2(200);
begin
-- open file to write into and obtain its x
v_log_file := utl_file.fopen('c:\temp','myfile.txt','w');
open get_line_data;
loop
fetch get_line_data into rec_line_data;
exit when get_line_data%notfound;
begin
-- chr(13), chr(10) for end of line
utl_file.putf(v_log_file, rec_line_data.column1||rec_line_data.column2.%s%s', chr(13), chr(10));
-- the utl_file utility has a roblem when in comes to writeing larg number of line in high speed.
-- the close open is the way to get over it, ei, work around
utl_file.fclose(v_log_file);
v_log_file := utl_file.fopen('c:\temp', 'myfile.txt' ,'a');
exception
when utl_file.invalid_path
then
v_error := 'invalid path for the utl_file. c:\temp is invalid. you can get the right value from v$parameters';
utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
utl_file.fclose(v_log_file); -- close log file
when utl_file.invalid_mode
then
v_error := 'invalid mode. the w (write) mode in not good.';
utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
utl_file.fclose(v_log_file);-- close log file
when utl_file.invalid_operation
then
v_error := 'invalid operation';
utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
utl_file.fclose(v_log_file);-- close log file
when others
then
v_error := 'when others';
utl_file.putf(v_log_file, 'The programm finished at: %s', to_char(sysdate, 'DD-MON-YYYY:HH24:MI:SS AM'));
utl_file.fclose(v_log_file);-- close log file
end;
end loop;
close get_line_data;
end;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|