Re: Sample Script?

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 22 Dec 2007 15:04:02 -0600
Message-ID: <u1w9ei7h8.fsf@rcn.com>

On Thu, 20 Dec 2007, megacrosstab_at_greenixsolutions.com wrote:
> On Dec 20, 10:48 am, "Terry Dykstra" <tddyks..._at_forestoil.ca> wrote:

>> "AP" <megacross..._at_greenixsolutions.com> wrote in message
>>
>> news:804a246f-3fca-4b54-a2bc-aeecac1841eb_at_j20g2000hsi.googlegroups.com...
>>
>> > Hello I am new to Oracle. What I am trying to do is basic, I
>> > think. I typically use toad to run select statements. I want to be
>> > able to create a script that simply when executed, runs the select
>> > statement and exports it to a file. Once I get this figured out, I
>> > would use a command line method to run it on a scheduled basis. Any
>> > samples ?
>>
>> > Thanks
>>
>> In its most basic form:
>>
>> sqlplus userid/passwd_at_dbname @yourscript.sql
>>
>> with a spool statement in the yourscript.sql file.
>>
>> Hardcoding passwords obviously is not very good.  There are ways of
>> redirecting input from another file into your script.
>> --
>> Terry Dykstra

>
> Thanks for the reply, but I know the command line part, I do not know
> how to do the following:
>
>
> Select * from mytable;
>
> Save the output as txt to C:\output files.
>
>
> Not sure how to build the script.
> Thanks for your response.

Seems that you are struggling with a very simple concept, actually, but I could see how it might be confusing, especially if all you have worked with is GUI tools, like TOAD.

sqlplus is an executable. It can be run as an interactive command-line where you submit sql statements directly. ie,

   CMD> sqlplus login/password_at_instance RETURN

       ... Now, you see a command-line interface to Oracle sql commands.    SQLPLUS> select blah ...;
   SQLPLUS> select bleh ...;

But sqlplus can, ALSO, be run as an executable that is passed a file containing sql statements.

   sqlplus login/password_at_instance @ScriptWithSqlStatements.sql

Inside ScriptWithSqlStatements.sql, put a spool statement.

All of sqlplus and how to use it is documented quite nicely.

-- 
Galen Boyer

Received on Sat Dec 22 2007 - 15:04:02 CST