ksh select value into variable from v$ view 2005-07-22 - By Khemmanivanh, Somckit
Try using 2 backslashes i.e. v\\$process p, v\\$session
Thanks!
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Barbara Baker
Sent: Friday, July 22, 2005 10:36 AM
To: oracle-l@(protected)
Subject: ksh select value into variable from v$ view
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
--
http://www.freelists.org/webpage/oracle-l
|
|
