Connect to database from different unix box

Shell Programming and Scripting
1

Hi Friends,

I have a one unix box "x.y.z" on SunOS , where oracle is not installed. And another machine "a.b.c" on AIX , where oracle is installed. Now i want to make a script on x.y.z , that can connect to database on a.b.c, using sqlplus command. and then generate a report based on the database query output.

Thanks
Gaurav
Unix is in the blood !! :mad:

2

The things you need is oracle client on xyz and an entry for SID for the database of abc which you want to connect in the tnsnames.ora of the xyz.

Are you sure with them?

3

Hi Gaurav
Try this only if you cannot establish what anchal suggested before.

  1. Keep the sql part of your script in a separate file.
  2. Just before the sql part comes, do automatic ftp or rcp and place this sql script in the box in which oracle connection is present.
  3. Followed by step 2 in your shell script, do an "remsh" and connect to the box2 and run the sql script

Thanks
Guru.

4

My xyz is a production machine and I cant get the oracle client installed on it. However I think what guru suggested is workable in my case.

First I need to establish the connection to the machine abc and then execute the sql statements therein.

Does the code will look alike below ?

#!bin/ksh

telnet username/password@abc
sqlplus -s username/password@schema << report.txt

sql statements
exit
!

sftp username/password@xyz

mput report.txt /path on xyz
bye
exit
5

Hi Gaurav

I was thinking like this:

#!bin/ksh

sqlplus -s username/password@schema << report.txt

sql statements
exit
!

sftp username/password@xyz

mput report.txt /path on xyz
bye
exit

remsh <connect to destination and run the script placed there>

Could think of one more better way if your entire script is to be run in the "abc" box. Write your script as if you are going to run directly on the "abc" box.

From the "xyz" box, you can run the script like this:

ssh <user>\@<box>  <your script in xyz>

Thanks
Guru.

6

Guru,

Thanks for the post , but I was willing to shoot the script from XYZ box and not from ABC.Thats why I established the connection to ABC first in my code and then ran sql query.However once the query will run on abc box through xyz script, where the spool file will get created by default on xyz or abc ??? Because if it is getting generated on ABC box then again i need to SFTP on XYZ and get the file on it.