| to sql query output into text file [message #498325] |
Wed, 09 March 2011 11:16  |
 |
swapnabpnn
Messages: 96
Registered: December 2010
|
Member |
|
|
Hi ALL,
I am trying to get query output into textfile.The following procedure is working fine by creating directory as follows in sys user.The output is getting onto text file and it seen on server machine even.My question is I want to see the text file on local machine also instead of everytime connecting to server machine drive.How can I perform that?
create or replace directory INFO_DIR as 'D:\Swapna';
grant read, write on directory INFO_DIR to fairpoint;
CREATE OR REPLACE PROCEDURE FAIRPOINT.utl_file_test_write_xls(filename in VARCHAR2 )
IS
output_file utl_file.file_type;
v_path VARCHAR2(500);
v_string VARCHAR2(4000);
v_sqlstr VARCHAR2(2000);
BEGIN
BEGIN
output_file := UTL_FILE.FOPEN(location => 'INFO_DIR',
filename => filename,
open_mode => 'w',
max_linesize => 32767);
utl_file.put_line (output_file, 'XLS_NAMES');
FOR i IN (SELECT COPPER_CABLE_ID from COPPER_CABLE) LOOP
v_string := i.COPPER_CABLE_ID;
utl_file.put_line (output_file, v_string);
END LOOP ;
UTL_FILE.FCLOSE_ALL;
UTL_FILE.FCLOSE_ALL;
END utl_file_test_write_xls;
/
exec utl_file_test_write_xls('SP.txt');
Thanks........
|
|
|
|
|
|
|
|
|
|
| Re: to sql query output into text file [message #498488 is a reply to message #498438] |
Thu, 10 March 2011 02:54  |
sethumurugan
Messages: 61
Registered: June 2010
Location: Chennai
|
Member |
|
|
Hi swapna,
Hope you mean that the user should be able to take a print in a click without knowing anything about what is happening at the back. If that is the case please follow the following steps.
Create a sql file as follows [ print_test.sql ] in a folder
spool test.txt
select * from employee;
spool off
exit
Then create a batch file as follows [ print_test.bat ]
sqlplus -s ears/ears@td1 @print_test.sql
print test.txt
exit
Now create a shortcut for the batch file in your OS. So when the user clicks the short cut, automatically the sql output will be sent to the default printer. If you like to have formats, you can achive the same thru sql statement.
Hope this helps to what you want. Pls buzz us if you have any clarification.
Sethu Murugan
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
