Re: UNIX script problem with sqlplus (SOLUTION)

In article <35dbe6f2.865232728_at_news.telecom.pt>, nuno-v-guerreiro_at_telecom.pt (Nuno Guerreiro) wrote:

> On Thu, 20 Aug 1998 08:31:12 +0100, tics28@"spam-off"email.sps.mot.com
> (Alan Long) wrote:
>
> >I have a unix file temp1.sql containing:
> >
> > spool temp1
> >
> > select part_id
> > from product
> > ;
> >
> > spool off
> >
> >I want to run this automatically using crontab, so I have a unix file
temp1.run
> >containing:
> >
> > . ~/.profile
> > sqlplus userid/password < temp1.sql
> > mail tics28_at_email.sps.mot.com < temp1.lst
> >
> >This essentially works but temp1.lst contains:
> >
> > SQL>
> > SQL> select part_id
> > 2 from product
> > 4 ;
> >
> > PART_ID
> > -----------------------------------
> > A/32
> > SQL>
> > SQL> spool off
> >
> >
> >How can I suppress the SQL prompts and the sqlplus code from the output,
> >so that I only get the data? When I run temp1.sql from within sqlplus, the
> >output file temp1.lst just contains:
> >
> > PART_ID
> > -----------------------------------
> > A/32
> >
> >As is probably quite obvious, I don't have a lot of UNIX ability!
> >
> >--
>
> The sqlplus command has support for running a SQL script on startup.
> Thus you don't need to have the shell redirect input to sqlplus from a
> file.
> Simply invoke sqlplus in the following way:
>
> sqlplus userid/password @temp1
>
>
> In case you need to pass any parameters to your SQL script, you can
> also put them on the command-line, e.g.:
>
> sqlplus userid/password @temp1 abc
>
> Then in your SQL script, your SELECT statement might look like the
> following:
>
> select part_id from product
> where product_name='&1';
>
>
>
> Hope this helps,
>
>
> Nuno Guerreiro

Many thanks to everyone for the various suggestions. The solution I settled on is:

     . ~/.profile
     exit | sqlplus userid/password @temp1
     mail tics28_at_email.sps.mot.com < temp1.lst

The "exit" was required to prevent the script being left hanging at the SQL prompt.

--
Alan Long

email tics28@"spam-off"email.sps.mot.com (remove the "spam-off") Received on Fri Aug 21 1998 - 02:12:23 CDT