Re: selecting into unix variable

Ellen Storz <ellen_at_.pacific.net> wrote:

>From a unix script I want to use SQLPLUS to select
>a sequence number into a variable, then run a script to
>select other data into other a work table, run several
>reports, and then delete data from the work tables for
>the sequence number. I've found a workaround, but is
>there a way to select directly into a unix variable?
 

>i.e. (this is simplified)
 

>#!/bin/ksh
 

>sqlplus -s ${user}/${pw} <<SQLTEXT > /dev/null
 

> select seq.nextval
> into <unix var>
> from dual
>/
>exit
>SQLTEXT
>
>run another sql script
 

>runrep report_name ${user}/${pw} sequence=$sequence

>sqlplus -s ${user}/${pw} <<SQLTEXT > /dev/null
 

> delete from table_name
> where sequence = ${sequence};
>/
>exit
>SQLTEXT
 
>I'm not clear on what the syntax for the unix "sequence"
>variable would be.

There is a very simple solution to your problem. It is right in the shell script which you have already written.

Write this into a script

file get_sequence.sql

set verify off
set feedback off

    select seq.nextval
     from dual
/
exit sql.sqlcode

Just write

UNIX_VAR=`sqlplus -s <usercode>/<password> _at_get_sequence.sql`

in the shell script.

 Hope this explanation is sufficient.

Ananta Narayanan

Views expressed here are my own and
not those of my employer.
Other Standard disclaimers apply.

• What we know is very little, what we do not know is immense..