ksh select value into variable from v$ view 2005-07-24 - By Radoulov, Dimitre
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@(protected)>
Cc: "oracle-l" <oracle-l@(protected)>
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@(protected)> 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×tamp
>> 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 (See ORA-00942.ora-code.com): 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
|
|
