ksh select value into variable from v$ view

From: Barbara Baker <barb.baker_at_gmail.com>
Date: Fri, 22 Jul 2005 11:35:43 -0600
Message-ID: <47a6f72b050722103566c1b18e@mail.gmail.com>

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

Received on Fri Jul 22 2005 - 12:39:28 CDT