topic Re: Output from the oracle database via shell scripts in Operating System - HP-UX

Output from the oracle database via shell scripts

Dayanand Naik_1 — Tue, 18 Jun 2002 05:47:49 GMT

Hi Folks,

On a daily basis i need some data from Oracle database, can i execute a shell script and get the same. As i don't know the format, if any1 can send me the same that will be fine.

eg:
customer table
sr_no, customer_ID, creation_date

Thanks in advance,

Regards,
!!! NAIK !!!

Re: Output from the oracle database via shell scripts

Duncan Edmonstone — Tue, 18 Jun 2002 06:44:50 GMT

You mean something like this?

export ORACLE_HOME=/.../...
export ORACLE_SID=XXX
su oracle -c $ORACLE_HOME/bin/sqlplus /nolog << EOF
connect / as sysdba ;
select sr_no, customer_ID, creation_date from customer_table ;
exit ;
EOF

Of course, you'll need to set ORACLE_HOME and ORACLE_SID correctly, and know the correct names for the tables and columns.

HTH

Duncan

Re: Output from the oracle database via shell scripts

Dayanand Naik_1 — Tue, 18 Jun 2002 06:57:44 GMT

Hi Again,

i need only to type the shell script at the command line. and it gets me the result.

so it means the sql stmt should be written into the shell script. correct me if 'm wrong

Re: Output from the oracle database via shell scripts

Deepak Extross — Tue, 18 Jun 2002 07:01:33 GMT

Yes, you can put Duncan's script into a file and just call this file from the command line.

Re: Output from the oracle database via shell scripts

Peter Kloetgen — Tue, 18 Jun 2002 07:04:10 GMT

Hi Naik,

Duncans script seems to be fine. Simply put all these command lines into a text file, chmod u+x script_name after that. Then you call your script and it gives you the desired result.

Normally standard output for the script is your terminal, if desired, redirect the output to a file:

script_name >> /path_to_output_file

Allways stay on the bright side of life!

Peter

Re: Output from the oracle database via shell scripts

Dayanand Naik_1 — Tue, 18 Jun 2002 08:46:41 GMT

Hi All,

The script works but needed some changes.. which i made.. thanks again and following are the changes :

file : cust.sh

ORACLE_HOME=/u05/oracle/product/8.1.7
ORACLE_SID=testdb
su - oracle -c "$ORACLE_HOME/bin/sqlplus username/password" /nolog << EOF
spool /tmp/test1.log
select sr_no, customer_ID, creation_date
from customer;
spool off
exit;
EOF

Thanks again....

Regards,
!!! NAIK !!!