Re: Oracle 9i / Toad 8.6

I have a situation where I need to launch Toad within a Batch
environment to run 5 separate SQL scripts and export the data from
these five scripts to five separate Excel Files.
Example:
"Toad.exe","MySqlScript_1.SQL","MySql_1_Exported Data.XLS"
"Toad.exe","MySqlScript_2.SQL","MySql_2_Exported Data.XLS"
"Toad.exe","MySqlScript_3.SQL","MySql_3_Exported Data.XLS"
"Toad.exe","MySqlScript_4.SQL","MySql_4_Exported Data.XLS"
"Toad.exe","MySqlScript_5.SQL","MySql_5_Exported Data.XLS"

Does anyone have an example where they launched Toad in a 'batch mode'
to run multiple scripts?
I am hitting brick walls in trying to do this.

I would welcome your examples/suggestions.

Thanks.

Why don't you ask in the MS scripting, or in the
Quest newsgroups?

Those seem better equipped for this question, I'd say
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

>>Those seem better equipped for this question<<
Agreed.
I was just posting here to verify whether there was a certain syntax
that Toad would be expecting.

I will post there also per your counsel.

Thanks Frank.

"RN" <rlntemp-newsgroup@yahoo.com> schreef in bericht
news:1160150383.502260.109520@m73g2000cwd.googlegr oups.com...


RN,

I think Toad is not the right tool to do this. Never heard of 'running Toad
in batch mode'. There must be better ways to generate Excel files from
within the database, or at least generate output in a format that can be
read by Excel.
If you use google and search on words like 'Excel', 'SQLplus', 'Oracle' I'm
sure you'll find better solutions.
I'll give you one sneak preview:

A very simple way of doing this is to create a tab delimited report (chr(9)
between each column) and spool a .xls data file within SqlPlus. This
creates and excel-like file that can be opened directly by Excel.
http://www.freelists.org/archives/o...5/msg00284.html

If you still want to use Toad, be my guest... but I suggest you stop hitting
brick walls! Keep it simple!

Robbert van der Hoorn
OSA it BV
The Netherlands

On 6 Oct 2006 08:59:43 -0700, "RN" <rlntemp-newsgroup@yahoo.com> wrote:


Why not run sqlplus in batch mode and simply create a csv file format and spool to file. It would be easier, and much more
efficient. Right tool for the right job, but find the simple and most direct solution. Excel can launch csv files.

If you don't want to go through the trouble of creating a flat file, I have some code that will produce a csv by simply passing the
SQL to it and some filename parameters. It will generate the file to your database server. Let me know if you are interested.

~Barry

IMHO I would think that using a PLSQL procedure & outputing to a file
(.CSV) would be a simpler solution.

Alternately you can ODBC from Excel into an oracle database easily.

I have however found that for the purpose of batch jobs a procedure
works best.

declare
hFile utl_file.file_type;
sOutPut varchar2(2000);
begin
hFile := utl_file.fopen('directoryName','fileName.csv','W') ;
for r in myView
loop
sOutPut := to_char(r.col1_dt,'dd-Mon-yyyy') || ',' || r.col2_num
||',"'|| r.col3_txt ||'"';
utl_file.put_line(hFile,sOutPut);
end loop;
utl_file.fclose(hFile);
end;

would create a file something like:
01-Jan-2006,123,"Fred"
01-Jan-2006,221,"Lou"
....


this would be saved in the file fileName.csv inside the directory
directoryName.
Depending on the version of your database you may need to set the
UTL_FILE_DIR or use the CREATE DIRECTORY feature. There are some o/s
differences as well so consult your manual.

On 6 Oct 2006 08:59:43 -0700, "RN" <rlntemp-newsgroup@yahoo.com>
wrote: