Operating System - HP-UX
1748003 Members
4378 Online
108757 Solutions
New Discussion юеВ

Output from the oracle database via shell scripts

 
SOLVED
Go to solution
Dayanand Naik_1
Occasional Advisor

Output from the oracle database via shell scripts

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 !!!
5 REPLIES 5
Solution

Re: Output from the oracle database via shell scripts

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

I am an HPE Employee
Accept or Kudo
Dayanand Naik_1
Occasional Advisor

Re: Output from the oracle database via shell scripts

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
Deepak Extross
Honored Contributor

Re: Output from the oracle database via shell scripts

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

Re: Output from the oracle database via shell scripts

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
I'm learning here as well as helping
Dayanand Naik_1
Occasional Advisor

Re: Output from the oracle database via shell scripts

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 !!!