Re: ksh select value into variable from v$ view

  • From: Ram K <lambu999@xxxxxxxxx>
  • Date: Sun, 24 Jul 2005 02:29:45 -0700

Hi all,
  
   Trying to learn.
   
   1) Is it possible to pass a variable (or more than one) to the SQL
from shell script.
  
   2)  Is it possible to return more than one value from the SQL to the script? 
   
Thanks,
Ram.
  
        ---------
  
On 7/22/05, Barbara Baker <barb.baker@xxxxxxxxx> wrote:
> 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
> 

-- 
Thanks,
Ram.
--
//www.freelists.org/webpage/oracle-l

Other related posts:

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