Re: UNIX script problem with sqlplus

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 Received on Thu Aug 20 1998 - 04:13:49 CDT