Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to automate data export using toad for oracle

Posted on 2011-09-21
11
Medium Priority
?
3,015 Views
Last Modified: 2012-05-12
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
Comment
Question by:avoorheis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 13

Assisted Solution

by:George K.
George K. earned 600 total points
ID: 36579206
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
 
LVL 13

Assisted Solution

by:George K.
George K. earned 600 total points
ID: 36579217
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
 
LVL 13

Assisted Solution

by:George K.
George K. earned 600 total points
ID: 36579237
Regarding specifically Toad you should try Toad 's automation designer.
Here is a tutorial: http://www.youtube.com/watch?v=wH0T6HYXID0
0
Independent Software Vendors: 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!

 

Author Comment

by:avoorheis
ID: 36580545
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
 
LVL 7

Accepted Solution

by:
Jacobfw earned 1400 total points
ID: 36581579
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
 

Author Comment

by:avoorheis
ID: 36583947
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
 
LVL 13

Expert Comment

by:George K.
ID: 36585243
Set the pagesize to a bigger nuimber the maximum number of columns (Max is 50000)
0
 
LVL 13

Expert Comment

by:George K.
ID: 36585246
sorry, correction:
Set the pagesize to a bigger number than the maximum number of rows (Max is 50000)
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36586421
set pagesize 0
set linesize 30000
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36586449
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
 

Author Closing Comment

by:avoorheis
ID: 36587238
thanks guys, really appreciate your feed back
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question