Re: Running SQLPlus from command line

> 2. After Line 4 executes SQLPlus the batch file hangs. I do not see
> data, don't know if the query is finished, etc.

In your SQL script, code the following line:

EXIT
> 3. Is there a statement that I can use that will return whether SQLPlus
> successfully connected to the database? I think it connects, but am
> unsure actually.

See the next answer.

> 4. I need to save the data output. When the query is completed, how do
> I tell SQLPlus (via another line in the batch file) to save the
> contents to either a .csv file or .xls file, and exit, thus passing
> control back to the final line of the batch file to display the
> 'process completed' message?

The SPOOL command can save the results to a file. So the first line in your SQL script should be something like:

SPOOL my_sql_script.txt

After you have run your SQL statements in your script, add the following line:

SPOOL OFF
> I need to save this output file (be it .csv, .txt, or .xls) to the
> local drive so an Access program can pick it up and do more processing
> with it.
>
> When using the SQLPlus Command Line in a batch file in silent mode, is
> there a limitation of filetypes the data retreival to be exported to?
> I think choosing between .txt, .csv, and .xls woud be fine.

The SPOOL command will output straight text. However, you can also choose to have SQL*Plus generate HTML output for you with the SET MARKUP command. If you want XML output, then you'll need one of the Oracle XML toolkits.

> (line numbering is for reference here only)
> Here is my Batch File:
> <begin code for TEST12.BAT>
> 1. @echo off
> 2. echo Running TEST12.SQL
> 3. echo SQLPlus running from a command line.....please wait....
> 4. SQLPLUS -s myLogOn/myPass_at_MyDBName@TEST12.SQL

There is no space after MyDBName. The format should be:

sqlplus -s user/pass_at_tns_alias @script_name

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown