RE: ksh select value into variable from v$ view

From: Khemmanivanh, Somckit <somckit.khemmanivanh_at_weyerhaeuser.com>
Date: Fri, 22 Jul 2005 10:47:55 -0700
Message-ID: <65C0D8935651CB4D96E97CEFAC5A12B9962D2D@wafedixm10.corp.weyer.pri>

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

Thanks!
-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barbara Baker Sent: Friday, July 22, 2005 10:36 AM
To: oracle-l_at_freelists.org
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
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 22 2005 - 12:49:58 CDT