Re: ksh select value into variable from v$ view

  • From: "Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>
  • To: <lambu999@xxxxxxxxx>
  • Date: Sun, 24 Jul 2005 16:55:46 +0200

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@xxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l


--
Thanks,
Ram.
--
//www.freelists.org/webpage/oracle-l 

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2005