Shell Programming and Scripting

hi experts,

very need help about script how to login into oracle database and select a table and append the query into a file.

i have oracle database and have a table named omc_ip with fields like host_id(varchar2(20)),ip(varchar2(10)) and update_date(date).

i want to build a script that can connect to oracle database and query this table and put the output into a file.

nb:using oracle8i database and solaris 8 sparc

please help

Thank you so much for your attention


Best Regards,

bucci

Code:

sqlplus username/password@connection_string <<EOF
spool /dir1/dir2/file
select * from omc_ip;
spool off
EOF

/dir1/dir2/file contain the result of the query

thank you so much anbu

Regards,

bucci

hi anbu,

need some pretty advices for my output query.


my script database is :

#!/bin/sh

DAY=`/export/home/oracle/bin/lastdate | nawk '{print $1}'`

/export/home/orahome/bin/sqlplus -s user/pass<<eof
set heading off;
set linesize 100;

spool /export/home/oracle//log/$DAY.log
select ip,host_id from omc_ip ;
spool off;
EXIT;
eof


below is my output :

10.1.0.0 LDDS-IVR-2
10.1.0.0 LDDS-CS-A
10.1.0.0 USSDGsem1
10.1.0.0 USSDGsem2
10.1.0.0 USSDGsby1
10.1.0.0 USSDGsby2
10.1.0.0 USSDGmdn1
10.1.0.0 USSDGmdn2
10.1.0.0 USSDGtbs2
10.1.0.0 LDDS-IVR-1
10.1.0.0 USSDGtbs1
10.1.0.0 LDDS-MAP-B
10.1.0.0 LDDS-CS-B

10.1.0.0 LDDS-MAP-A
10.1.0.0 USSDGtbs3
10.1.0.0 USSDGmdn3
10.1.0.0 USSDGmdn4
10.1.0.0 USSDGsby3
10.1.0.0 USSDGsby4
10.1.0.0 USSDGtbs4

20 rows selected.

i want to throw away the blank line and the "20 rows selected."

any idea?


Thank you

Best Regards,

bucci

If you use

Code:

set feedback off

you wont get "20 rows selected".

Use this to remove blank lines in the output file

Code:

sed "/^[[:space:]]*$/d" file

hi anbu,

how to i bundle the sed command into my script ?

please advice

thank you

Regards,

bucci

Code:

#!/bin/sh

DAY=`/export/home/oracle/bin/lastdate | nawk '{print $1}'`

/export/home/orahome/bin/sqlplus -s user/pass<<eof
set heading off;
set linesize 100;
set feedback off;

spool /export/home/oracle//log/$DAY.log
select ip,host_id from omc_ip ;
spool off;
EXIT;
eof
sed "/^[[:space:]]*$/d" /export/home/oracle/log/$DAY.log > tmp
mv tmp /export/home/oracle/log/$DAY.log