$ func () { sqlplus -s '/ as sysdba' << ! set echo off feed off head off time off timing off $stmt exit ! } export stmt="select sysdate from dual;" $ func | tail -1| while read var; do export VAR=$var;echo $VAR; done 24-JUL-05
Regards Dimitre Radoulov
-- -- Original Message -- -- From: "Ram K" <lambu999@(protected)> Cc: "oracle-l" <oracle-l@(protected)> Sent: Sunday, July 24, 2005 11:29 AM Subject: 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@(protected)> 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×tamp >> 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 (See ORA-00942.ora-code.com): 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
