RE: ksh select value into variable from v$ view

  • From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
  • To: <barb.baker@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 22 Jul 2005 10:47:55 -0700

Try using 2 backslashes i.e.  v\\$process p, v\\$session


Thanks! 
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barbara Baker
Sent: Friday, July 22, 2005 10:36 AM
To: oracle-l@xxxxxxxxxxxxx
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&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


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

Other related posts: