• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3139
  • Last Modified:

How to automate data export using toad for oracle

I'm using toad for oracle 9.5 and I have a small query and export the data as an excel file. I'd like to automate it and have tried using an action set, which works ok, but, will not be allowed in our production environment.
Is there another way I can do this?
Will a script work (I don't know what a script is; I've just switched departments and have used sql server, but, very little oracle/toad).

thanks
0
avoorheis
Asked:
avoorheis
  • 5
  • 3
  • 3
4 Solutions
 
George K.Commented:
You could also try connecting via odbc from Ms-Excel and then you will always have updated data when you open the excel file.
0
 
George K.Commented:
Oracle imp exp utilities might be also useful. Here is a tutorial ink:http://www-it.desy.de/systems/services/databases/oracle/impexp/impexp.html.en
0
 
George K.Commented:
Regarding specifically Toad you should try Toad 's automation designer.
Here is a tutorial: http://www.youtube.com/watch?v=wH0T6HYXID0
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
avoorheisAuthor Commented:
thanks George,
We can't use odbc for our production database. We have toad 9.5, so, not sure the automation designer is available, but, I'll check it out along with your other suggestion.
I see Toad can support scripts, but, I don't know what those are and am not finding any useful info in the help files. Do you know if a script could do what I need?

thanks
0
 
JacobfwCommented:
set feed off markup html on spool on
spool c:\mytable.xls
select * from mytable;
spool off
set markup html off spool off
exit

 Write the above script with your favourite editor..and save it as filename.sql
Run “sqlplus” prompt “sqlplus /nolog”
after logging in :
type @filename ..
and it will export the spreadsheet to the location
0
 
avoorheisAuthor Commented:
Hi Jacob,
that worked almost perfect. However, the header row appears every 15th row. I think I saw a command to turn off the headers somewhere, I can live without them for this project, but, would be cool to just have on row 1, if there is a way.
0
 
George K.Commented:
Set the pagesize to a bigger nuimber the maximum number of columns (Max is 50000)
0
 
George K.Commented:
sorry, correction:
Set the pagesize to a bigger number than the maximum number of rows (Max is 50000)
0
 
JacobfwCommented:
set pagesize 0
set linesize 30000
0
 
JacobfwCommented:
Other Commands to help format the spool file:
set colsep*
set heading off
set feedback off
set trimspool off
col text_field format a1000

For explaination see this:
http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/ch13.htm
0
 
avoorheisAuthor Commented:
thanks guys, really appreciate your feed back
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now