Re: ksh select value into variable from v$ view

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_at_gmail.com> 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
> --
> http://www.freelists.org/webpage/oracle-l
>

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