| Output to file [message #137257] |
Wed, 14 September 2005 02:53  |
isolapojken
Messages: 26
Registered: August 2005
|
Junior Member |
|
|
Hi!
I am using toad and have written a procedure in PL/SQL which generates output. So far I have been using the "dbms_output.put_line(output)" to display my output, but now I would like to get the output in a ".dot" file (Microsoft Word Template format).
I have been looking at the utl_file commands, but my output file has to be located locally on my local client computer and NOT on the server computer. As I have understood it, this is not doable using utl_file. Or is it?
I have also been trying to use the SPOOL command, but I can´t get it to work. Might that be because I am using toad and PL/SQL instead of SQL*Plus?
Can anyone help me? When my procedure is done. The output file should be a .dot file, located locally on my client computer.
Thanks!
/e
|
|
|
|
| Re: Output to file [message #137262 is a reply to message #137257] |
Wed, 14 September 2005 03:21  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
In TOAD:
Open the file in the SQL Editor.
Enable the dbms_output (tab in SQL Editor).
"Execute As Script"
Look at the output in the DBMS output tab.
Click the 'floppy' icon on the tab. Save as..any file.
I don't see the issue.
[EDIT: assuming you're using DBMS_OUTPUT.PUT_LINE of course]
MHE
[Updated on: Wed, 14 September 2005 03:22] Report message to a moderator |
|
|
|
| Re: Output to file [message #137295 is a reply to message #137262] |
Wed, 14 September 2005 07:31 |
isolapojken
Messages: 26
Registered: August 2005
|
Junior Member |
|
|
Hi!
Yes, I am using dbms_output.put_line, however the program is thought to be as user friendly as possible. And the user shouldn't have to click the "save dbms_output"-icon (this is what I have been doing whilst developing the program). In fact, it is not even sure that the user will use toad to run the final version at all.
Still grateful for all the help I can get?
Thanks!
|
|
|
|
| Re: Output to file [message #137304 is a reply to message #137295] |
Wed, 14 September 2005 08:00 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
I don't think the user should be using TOAD at all. It is a tool for developers, not a query tool for end users. Give a developer tool to an end user and all hell breaks loose, even if you have all the security privileges in place.
If you do not think they will be using TOAD, what will they be using? SQL*Plus? Then spool is your best option.
|
|
|
|
|
|
| Re: Output to file [message #137424 is a reply to message #137308] |
Thu, 15 September 2005 02:53 |
isolapojken
Messages: 26
Registered: August 2005
|
Junior Member |
|
|
As I have my PL/SQL program now, everything is running on the server side. Since I am the developer I have so far only ran the program via toad. I am not sure yet of how the user will run it.
To work around the problem with getting the file to the client computer, my program now put all the output in a table in the database and when the program has finished I run SQL*Plus in the MS-DOS prompt on the client computer and there spooling the results from the query: "select * from new_table" to a file locally.
This works, however it is a bit messy. Ideally the user should only need to run the program and then get the output file without any extra work. I have been experimenting with a DOS-batch file so that the user should at least only click a symbol which does all the work creating the desired output file. However so far I have only gotten the batch file to open the DOS-promt and then start SQL*Plus. I can't get it to run the SQL script automatically. Is this possible?
I am brand new to batch file programming. When I click on my batch file I would like the following things to happen:
1. open the DOS prompt
2. start SQL*Plus in the DOS prompt
3. run the SQL script which spools to a file in SQL*Plus
4. exit and close the DOS prompt
Is this doable?
Will the batch file look something like this:
sqlplus
@testprogram
exit
?
Thanks!
|
|
|
|
| Re: Output to file [message #137660 is a reply to message #137424] |
Fri, 16 September 2005 00:40 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your batch (.bat) file should be all on one line, something like:
d:\oracle\ora92\bin\sqlplus.exe scott/tiger @testprogram.sql
Your testprogram.sql would then look something like:
spool c:\oracle\your_file.dot
select * from dept;
spool off
exit
If you create a shortcut to your .bat file, then click on it, it should open a dos window, start sqlplus, run your testprogram, then close the window. You should then have a your_file.dot file something like:
scott@ORA92> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 PERSONNEL DALLAS
scott@ORA92> spool off
The above is just an example. You will need to substitute the correct directory paths, file names, and table name for your system, and you may want to use some set commands in your .sql file to format the output.
|
|
|
|
|
|
|
|
| Re: Output to file [message #162041 is a reply to message #161729] |
Wed, 08 March 2006 06:18 |
Nagaika
Messages: 1
Registered: March 2006
|
Junior Member |
|
|
| rebeccaz wrote on Mon, 06 March 2006 22:18 |
Hi,
The above spool will only capture the output of the last sql file. however, I need to append all results into 1 log file.
|
SPOOL filename APPEND; should do it, I think
Lars\
|
|
|
|
| Re: Output to file [message #162199 is a reply to message #161756] |
Thu, 09 March 2006 03:00 |
rebeccaz
Messages: 18
Registered: August 2005
|
Junior Member |
|
|
Hi all,
Thanks for the suggestions.
One of the suggestions as follows:
"You can spool to a separate file from each sql file, then concatenate the files in your bat file."
How do I concatenate the files in my bat file? Kindly advise as I have limited knowledge in batch files.
Rgds
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
