Re: ksh select value into variable from v$ view

Yes.
here is an example for both of them:

$ func () {
sqlplus -s '/ as sysdba' << !
set echo off feed off head off time off timing off $stmt
exit
 !
}
export stmt="select sysdate from dual;"
$ func | tail -1| while read var; do export VAR=$var;echo $VAR; done 24-JUL-05 Regards
Dimitre Radoulov

• Original Message ----- From: "Ram K" <lambu999_at_gmail.com> Cc: "oracle-l" <oracle-l_at_freelists.org> Sent: Sunday, July 24, 2005 11:29 AM Subject: Re: ksh select value into variable from v$ view

> Hi all,
>
> Trying to learn.
>
> 1) Is it possible to pass a variable (or more than one) to the SQL
> from shell script.
>
> 2) Is it possible to return more than one value from the SQL to the
> script?
>
> Thanks,
> Ram.
>
> ---------
>
> On 7/22/05, Barbara Baker <barb.baker_at_gmail.com> wrote:
>> Solaris 9 Oracle 9.2.0.4
>> Hi! I'm trying to grab a process id from the database and store it in
>> a variable. This syntax works if I'm not using a v$ view. If I use a
>> v$ view, it ignores what's after the $. I've tried v\$ and v_\$ , but
>> neither work.
>>
>> Any ideas?
>> Thanks!
>>
>> SCRIPT:
>>
>> #!/bin/ksh
>> set -xv
>> VALUE=`sqlplus -s scott/tiger <<END
>> set pagesize 0 feedback off verify off heading off echo off
>> trimspool on
>> col timecol new_value timestamp noprint
>> select to_char(sysdate,'.MMDDYY') timecol from dual;
>> spool persist&timestamp
>> select process from v\$process p, v\$session s
>> where p.addr=s.paddr
>> and s.username='PERSIST';
>> spool off;
>> exit;
>> END`
>> if [ -z "$VALUE" ]; then
>> echo "No rows returned from database"
>> exit 0
>> else
>> echo $VALUE
>> fi
>>
>>
>> WHEN I RUN IT:
>>
>> ENDVALUE=
>> select process from v p, v s
>> *
>> ERROR at line 1:
>> ORA-00942: table or view does not exist
>> if [ -z "$VALUE" ]; then
>>
>>
>> THIS WORKS:
>>
>> select max(adno) from ad;
>> spool off;
>> exit;
>> END`
>> + + sqlplus -s scott/tiger
>> + 0< /tmp/sh3845.2
>> VALUE=
>> 6687530
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>
> --
> Thanks,
> Ram.
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l