Home » Infrastructure » Unix » How to Store a Table Filed Value to a UNIX Shell Variable
How to Store a Table Filed Value to a UNIX Shell Variable [message #98382] Thu, 02 September 2004 09:54 Go to next message
Kumarr
Messages: 1
Registered: September 2004
Junior Member
Hello all,

I need to select a value from a Oracle Database Table and store it in a UNIX Shell script Variable, How do i do it?

Basically i will be running a shell script which executes the select statement.

Please help me on this.

Thanks,

 

 
Re: How to Store a Table Filed Value to a UNIX Shell Variable [message #98383 is a reply to message #98382] Thu, 02 September 2004 16:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.orafaq.com/faqunix.htm#UNIXVAR

#!/bin/ksh
#======================================================
# anonomous pl/sql
#======================================================
RETVAL=`sqlplus -s scott/tiger@dev <b><<</b>EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x varchar2(8);
begin
x := to_char(trunc(sysdate)-1, 'yyyymmdd');
dbms_output.put_line('the_result_is '||x);
end;
/
exit;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $2}'`
echo $X

#======================================================
# sql query
#======================================================
sqlplus -s scott/tiger@dev <b><<</b>EOF | read RETVAL
set echo off heading off feedback off pagesize 0
WHENEVER SQLERROR EXIT 1
select to_char(trunc(sysdate)-1, 'yyyymmdd') from dual;
/
exit;
EOF

echo retval is $RETVAL

Re: How to Store a Table Filed Value to a UNIX Shell Variable [message #98395 is a reply to message #98383] Fri, 10 September 2004 02:04 Go to previous messageGo to next message
pulkit
Messages: 18
Registered: April 2004
Junior Member
HI,

If i want to take the output in logfile and to shell symultaniously then how can i do this. As per the above mentioned approch i can get either of 2.

Please suggest

Pulkit
Re: How to Store a Table Filed Value to a UNIX Shell Variable [message #98396 is a reply to message #98395] Fri, 10 September 2004 07:59 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
sqlplus -s scott/tiger@dev <b><<</b>EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
SELECT table_name from user_tables where rownum < 6;
exit;
EOF

while read reslt_line
do
  echo "==>"$reslt_line
done < tmp.txt

output:
---------
==>A
==>ABC
==>ABC_CHILD
==>T1
==>T1_BAK
Previous Topic: oracle error with date and timestamp
Next Topic: Urgent Help
Goto Forum:
  


Current Time: Thu Mar 28 14:27:54 CDT 2024