ksh select value into variable from v$ view

  • From: Barbara Baker <barb.baker@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 22 Jul 2005 11:35:43 -0600

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

Other related posts:

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